我有2张桌子,客户和销售
我需要从Sales中提取尚未加载到“客户”表中的所有“客户”,并且在要求我添加2个MAX()
字段之前,我构建了以下运行良好的查询。
这是查询:
INSERT INTO Customers (.....)
SELECT distinct
d.UserName,
d.postalCode,
d.location,
d.country,
max(d.invoiceamount) invoiceamount,
max(d.itemscount) itemscount,
d.storeID
FROM S.dbo.[Sales] d
LEFT JOIN G.dbo.Customers s ON d.Username=s.UserName
WHERE s.UserName IS NULL
AND d.username IS NOT NULL
GROUP BY d.UserName, d.postalCode, d.location,
d.country, max(d.invoiceamount), max(d.itemscount), d.storeID
但查询在分组中不接受max():
在另一种情况下,我使用了,CROSS APPLY
但是从这里开始,我必须在一个表中提取Username为NULL的记录...我不知道如何构建查询。
能给我一些提示吗?
您不需要max()
中的group by
:
INSERT INTO Customers (.....)
SELECT d.UserName, d.postalCode, d.location, d.country,
max(d.invoiceamount) invoiceamount,
max(d.itemscount) itemscount,
d.storeID
FROM S.dbo.[Sales] d LEFT JOIN
G.dbo.Customers s
ON d.Username = s.UserName
WHERE s.UserName IS NULL AND d.username IS NOT NULL
GROUP BY d.UserName, d.postalCode, d.location, d.country, d.storeID;
所有未汇总的列均应位于中group by
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句