mysql无效列名_sql-运行查询时无效的列名

想象下面的简单表(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';