我无法过滤出重复的列值。
到目前为止,这是我的结果:
ProjectNumber, WorkOrder, Shipment, Start, End, Quantity, PartName
------------------------------------------------------------------
1234 | 1234-01 | S-01 | 2015-09-01 00:00:01 | 2015-09-01 00:00:01 | 200 | Part A
1234 | 1234-02 | S-01 | 2015-09-01 00:00:01 | 2015-09-01 00:00:01 | 200 | Part B
1234 | 1234-03 | S-01 | 2015-09-01 00:00:01 | 2015-09-01 00:00:01 | 200 | Part C
2345 | 2345-01 | S-01 | 2015-09-02 00:00:01 | 2015-09-02 00:00:01 | 12 | Part A
2345 | 2345-02 | S-01 | 2015-09-02 00:00:01 | 2015-09-02 00:00:01 | 24 | Part B
我知道我需要根据ProjectNumber与第一个WorkOrder的数据进行分组(如果可以的话)。因此,理想的结果将是:
ProjectNumber, WorkOrder, Shipment, Start, End, Quantity, PartName
------------------------------------------------------------------
1234 | 1234-01 | S-01 | 2015-09-01 00:00:01 | 2015-09-01 00:00:01 | 200 | Part A
2345 | 2345-01 | S-01 | 2015-09-02 00:00:01 | 2015-09-02 00:00:01 | 12 | Part A
到目前为止,我没有尝试过任何尝试。
SELECT
ProjectNumber, ProductionNotes AS Shipment, PromiseDate AS "End", Notes,
MIN(WorkOrderNumber) AS "Work Order", Name AS "Sales Order", PromiseDate,
Quantity, PartName
FROM Database.dbo.daWorkOrders
WHERE StatusTypeID = 3
GROUP BY ProjectNumber
ORDER BY PromiseDate ASC;
回复:
选择列表中的“ Database.dbo.daWorkOrders.ProductionNotes”列无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。
是否有可能做到这一点?
基本上,此错误表示的是,如果您要使用该GROUP BY
子句,那么结果将是每个组都有一行的关系/表,因此在您的SELECT
语句中,您只能“选择”您要使用的列在该列上进行分组并使用聚合函数,因为其他列将不会出现在结果表中。
请尝试使用此查询:
SELECT ProjectNumber
, ProductionNotes AS Shipment
, PromiseDate AS "End"
, Notes
, MIN(WorkOrderNumber) AS "Work Order"
, Name AS "Sales Order"
, PromiseDate
, Quantity
, PartName
FROM Database.dbo.daWorkOrders
WHERE StatusTypeID = 3
GROUP BY ProjectNumber, ProductionNotes, PromiseDate, Note, Name, PromiseDate, Quantity, PartName
ORDER BY PromiseDate ASC;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句