sql - 在sql中连接许多具有相同名称的行？

我似乎无法获得我需要的输出，谁能看到我做错了什么？

输出应该是

1 | Adam | Math, Science
2 | Tony | Math
3 | Erica | English, History

But I keep resulting with

1 | Adam | Math, Science, English, History
2 | Tony | Math, Science, English, History
3 | Erica | Math, Science, English, History

CREATE TABLE T
(
    ID INT,
    WM VARCHAR(50),
    Class VARCHAR(50),
    Prof VARCHAR(50),
)

INSERT INTO T (ID, WM, Class, Prof)
VALUES
    (NULL, 'Adam', 'Math', 'Sam'),
    (1, 'Adam', 'Science', 'Marc'),
    (2, 'Tony','Math', 'Sam'),
    (3, 'Erica','English', 'P'),
    (3, 'Erica','History', 'P')


SELECT *, STUFF((
            SELECT DISTINCT ', ' + Class
            FROM T
            WHERE T.WM = T.WM
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT WM
    FROM T
) T

如果留下负面结果，请留下原因

最佳答案

问题出在 where T.WM = T.WM 语句中

SELECT *, STUFF((
            SELECT DISTINCT ', ' + Class
            FROM T as T2
            WHERE T.WM = T2.WM
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT WM
    FROM T
) T

关于sql - 在sql中连接许多具有相同名称的行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50221045/