sql - 运行查询时列名无效

Question

我是 SQL 的新手，我不知道这个查询有什么问题，

SELECT 
    wo.WORKORDERID "Request ID", (wo.CREATEDTIME) "Created on", 
    aau.FIRST_NAME "Requester", aac.EMAILID 'From', 
    [To] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2 
                  WHERE wor2.Workorderid = wor.Workorderid and wor2.To_cc_bcc='To' 
                  FOR XML PATH('')), 1, 2, ''), 
    [CC] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2
                  WHERE wor2.Workorderid = wor.Workorderid and wor2.To_cc_bcc='CC' 
                  FOR XML PATH('')), 1, 2, ''),
    cd.CATEGORYNAME "Category" 
FROM 
    workorder_recipients wor
LEFT JOIN 
    workorder wo ON wor.workorderid = wo.workorderid 
LEFT JOIN 
    ModeDefinition mdd ON wo.MODEID = mdd.MODEID 
LEFT JOIN 
    SDUser sdu ON wo.REQUESTERID = sdu.USERID 
LEFT JOIN 
    AaaUser aau ON sdu.USERID = aau.USER_ID 
LEFT JOIN 
    SDUser crd ON wo.CREATEDBYID = crd.USERID 
LEFT JOIN 
    AaaUser cri ON crd.USERID = cri.USER_ID 
LEFT JOIN 
    AaaUserContactInfo aauc ON aau.USER_ID = aauc.USER_ID 
LEFT JOIN 
    AaaContactInfo aac ON aauc.CONTACTINFO_ID = aac.CONTACTINFO_ID 
LEFT JOIN 
    WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID 
LEFT JOIN 
    CategoryDefinition cd ON wos.CATEGORYID = cd.CATEGORYID 
WHERE 
    mdd.MODENAME = 'E-Mail'
    AND cd.CATEGORYNAME in ('Agent Operational Technology (EMEA/UK/IE)','Client Technology')
    AND wo.IS_CATALOG_TEMPLATE='0'
    AND wo.CREATEDTIME >= 1416783600000 
    AND wo.CREATEDTIME <= 1417388399000
    AND wo.ISPARENT='1' 
GROUP BY 
    wo.workorderid 

但我一直收到这个错误:

Column 'workorder_recipients.WORKORDERID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

谢谢，阿图尔

Answer 1

设想以下简单表 (T)，其中 ID 是主键:

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |
2   |    A    |    Y     |

然后你写下面的查询

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1;

这违反了 SQL 标准，如果它运行时没有错误(在 MySQL 中会出现错误)，结果:

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |

不比

更正确或更不正确

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

所以你的意思是为 Column1 的每个不同值提供一行，两个结果集都满足，那么你怎么知道你会得到哪一个？好吧，你不知道。

为了简单起见(以及它在 SQL Server 中的实现方式)，我们声明了一个规则，如果一个列不包含在聚合函数中，它必须在 GROUP BY 子句中才能出现在选择列表中。这不是严格意义上的，SQL 标准确实允许选择列表中的列不包含在 GROUP BY 或聚合函数中，但是这些列必须在功能上依赖于 GROUP BY 中的列。来自 SQL-2003-Standard(5WD-02-Foundation-2003-09 - 第 346 页)- http://www.wiscorp.com/sql_2003_standard.zip

15) If T is a grouped table, then let G be the set of grouping columns of T. In each contained in , each column reference that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in an aggregated argument of a whose aggregation query is QS.

例如，示例表中的ID是PRIMARY KEY，所以我们知道它在表中是唯一的，所以下面的查询符合SQL标准，在MySQL中运行，目前在很多DBMS中都失败(当时编写 Postgresql 是我所知道的最接近正确实现标准的 DBMS - Example here):

SELECT  ID, Column1, Column2
FROM    T
GROUP BY ID;

由于每一行的 ID 是唯一的，因此每个 ID 只能有一个 Column1 值，一个 Column2 值，因此没有歧义每行返回什么。据我所知，Postgresql 是唯一一个无论如何都实现了这一点的 DBMS。

为了使您的查询正常工作，您需要向 GROUP BY 添加一些列:

GROUP BY wo.workorderid, wo.CREATEDTIME, aau.FIRST_NAME, aac.EMAILID, cd.CATEGORYNAME

但是，我认为您可以通过从 FROM 中删除 workorder_recipients 来消除重复问题，您似乎没有在任何地方使用它。删除此引用应该会删除对 GROUP BY

的需要

SELECT 
    [Request ID] = wo.WORKORDERID, 
    [Created on] = wo.CREATEDTIME, 
    [Requester] = aau.FIRST_NAME, 
    [From] = aac.EMAILID, 
    [To] = STUFF((SELECT ', ' + Recipient_email 
                  FROM workorder_recipients wor2 
                  WHERE wor2.Workorderid = wo.Workorderid 
                  AND wor2.To_cc_bcc='To' 
                  FOR XML PATH('')), 1, 2, ''), 
    [CC] = STUFF((SELECT ', ' + Recipient_email 
                  FROM  workorder_recipients wor2
                  WHERE wor2.Workorderid = wo.Workorderid 
                  AND   wor2.To_cc_bcc='CC' 
                  FOR XML PATH('')), 1, 2, ''),
    [Category] = cd.CATEGORYNAME 
FROM workorder wo
    LEFT JOIN ModeDefinition AS mdd 
        ON wo.MODEID = mdd.MODEID 
    LEFT JOIN SDUser AS sdu 
        ON wo.REQUESTERID = sdu.USERID 
    LEFT JOIN AaaUser AS aau 
        ON sdu.USERID = aau.USER_ID 
    LEFT JOIN SDUser AS crd 
        ON wo.CREATEDBYID = crd.USERID 
    LEFT JOIN AaaUser AS cri 
        ON crd.USERID = cri.USER_ID 
    LEFT JOIN AaaUserContactInfo AS aauc 
        ON aau.USER_ID = aauc.USER_ID 
    LEFT JOIN AaaContactInfo AS aac 
        ON aauc.CONTACTINFO_ID = aac.CONTACTINFO_ID 
    LEFT JOIN WorkOrderStates AS wos 
        ON wo.WORKORDERID = wos.WORKORDERID 
    LEFT JOIN CategoryDefinition AS cd 
        ON wos.CATEGORYID = cd.CATEGORYID 
WHERE 
    mdd.MODENAME = 'E-Mail'
    AND cd.CATEGORYNAME in ('Agent Operational Technology (EMEA/UK/IE)','Client Technology')
    AND wo.IS_CATALOG_TEMPLATE='0'
    AND wo.CREATEDTIME >= 1416783600000 
    AND wo.CREATEDTIME <= 1417388399000
    AND wo.ISPARENT='1';