想象下面的简单表(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-Standard确实允许选择列表中的列不包含在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';
转播
