sql-server - SQL Server：如何从动态列的值中计数？

Question

SQL Server：如何从动态列的值中计数？
我有数据：

+ 主题
___________________
| 子ID | 子名 |
|-------|---------|
| 1 | 英语 |
|-------|---------|
| 2 | 西班牙语 |
|-------|---------|
| 3 | 韩语 |
|________|_________|

+ 学生
______________________________________
| 学生 | 学生姓名 | 性别 | 子ID |
|---------|---------|--------|--------|
| 1 | 大卫 | 中号 | 1,2 |
|---------|---------|--------|--------|
| 2 | 露西 | F | 2,3 |
|_________|_________|________|________|

我想查询结果为：

____________________________________
| 子ID | 子名 | 女 | 男 |
|--------|---------|--------|------|
| 1 | 英语 | 0 | 1 |
|--------|---------|--------|------|
| 2 | 西班牙语 | 1 | 1 |
|--------|---------|--------|------|
| 3 | 韩语 | 1 | 0 |
|________|_________|________|______|

这是我的查询：

选择
     子 ID、子名称、0 作为女性、0 作为男性
从主题

我不知道用实际计数替换 0。

标签： sql-server

Answer 1

因为您犯了将 CSV 数据存储在表中的错误，所以我们将不得不做一些 SQL Olympics 来获取您的结果集。我们可以尝试加入这两个表，条件是SubID主题表中的某个位置出现在学生表中 ID 的 CSV 列表中。然后，按主题汇总并计算男性和女性的数量。

SELECT
    s.SubID,
    s.SubName,
    COUNT(CASE WHEN st.Gender = 'F' THEN 1 END) Female,
    COUNT(CASE WHEN st.Gender = 'M' THEN 1 END) Male
FROM Subject s
LEFT JOIN Student st
    ON ',' + CONVERT(varchar(10), st.SubID) + ',' LIKE
       '%,' + CONVERT(varchar(10), s.SubID) + ',%'
GROUP BY
    s.SubID,
    s.SubName;

演示

但是，最好重构表设计以更好地规范数据。这是一个看起来更好的学生表示例：

+---------+---------+--------+--------+
| StuID   | StuName | Gender | SubID  |
+---------+---------+--------+--------+
| 1       | David   | M      | 1      |
+---------+---------+--------+--------+
| 1       | David   | M      | 2      |
+---------+---------+--------+--------+
| 2       | Lucy    | F      | 2      |
+---------+---------+--------+--------+
| 2       | Lucy    | F      | 3      |
+---------+---------+--------+--------+

我们可以更进一步，甚至将元数据与StuIDandSubID关系分开存储。但即使只使用上述方法也可以避免丑陋的连接条件。