22,210
社区成员
发帖
与我相关
我的任务
分享
/*
数据格式
ClientNO OrderDrugstoreName OrderCon OrderTel OrderCell OrderAdd Qty OrdQty
HYF20150430000466 普宁市创美电器有限公司 王城 0663-2237558 0663-2237558 池尾镇科技工业园计生办后面创美电器 50 50
HYF20150505001094 普宁市创美电器有限公司 江坚 0663-2254140 0663-2254140 池尾镇科技工业园计生办后面创美电器 21 21
SHF20150513000803 普宁市创美电器有限公司 王城 0663-2237558 0663-2237558 池尾镇科技工业园计生办后面创美电器 22 3
*/
根据相同OrderDrugstoreName,OrderCon,OrderTel,OrderCell,OrderAdd,汇总Qty,OrdQty,ClientNO行转列并汇总到一起
得出以下结果
/*ClientNO OrderDrugstoreName OrderCon OrderTel OrderCell OrderAdd Qty OrdQty SClientNO
HYF20150430000466 普宁市创美电器有限公司 王城 0663-2237558 0663-2237558 池尾镇科技工业园计生办后面创美电器 72 53 HYF20150430000466-SHF20150513000803
HYF20150505001094 普宁市创美电器有限公司 江坚 0663-2254140 0663-2254140 池尾镇科技工业园计生办后面创美电器 21 21 HYF20150505001094
*/
SELECT a.ClientNO,a.OrderDrugstoreName,OrderCon,OrderTel,OrderCell,a.OrderAdd,SUM(a.OrderQty) AS Qty,SUM(a.OrderOrdQty) AS OrdQty,SUM(a.OrderSpare2) AS Premium
FROM dbo.TMS_Order a,dbo.TMS_OrderRowList b,dbo.TMS_OrderRow c,dbo.WMS_Base_Client h,dbo.TMS_Base_Carrier u
WHERE a.OrderNO=b.OrderNO AND b.OrderRowNO=c.OrderRowNO AND
c.CarrierGuid=u.CarrierGuid AND a.ClientGuid=h.ClientGuid AND u.CarrierName='速运有'
GROUP BY a.ClientNO,a.OrderDrugstoreName,OrderCon,OrderTel,OrderCell,a.OrderAdd
ORDER BY a.ClientNO,a.OrderDrugstoreName,OrderCon,OrderTel,OrderCell,a.OrderAdd
查询代码WITH
/* 测试数据
table1(ClientNO,OrderDrugstoreName,OrderCon,OrderTel,OrderCell,OrderAdd,Qty,OrdQty)AS(
SELECT 'HYF20150430000466','普宁市创美电器有限公司','王城','0663-2237558','0663-2237558','池尾镇科技工业园计生办后面创美电器',50,50 UNION ALL
SELECT 'HYF20150505001094','普宁市创美电器有限公司','江坚','0663-2254140','0663-2254140','池尾镇科技工业园计生办后面创美电器',21,21 UNION ALL
SELECT 'SHF20150513000803','普宁市创美电器有限公司','王城','0663-2237558','0663-2237558','池尾镇科技工业园计生办后面创美电器',22,3
), */
a AS (
SELECT MIN(ClientNO) ClientNO,
OrderDrugstoreName,OrderCon,OrderTel,OrderCell,OrderAdd,
SUM(Qty) Qty,
SUM(OrdQty) OrdQty
FROM table1
GROUP BY OrderDrugstoreName,OrderCon,OrderTel,OrderCell,OrderAdd
)
SELECT a.*,
Stuff((SELECT '-'+ClientNO
FROM table1 AS b
WHERE b.OrderDrugstoreName = a.OrderDrugstoreName
AND b.OrderCon = a.OrderCon
AND b.OrderTel = a.OrderTel
AND b.OrderCell = a.OrderCell
AND b.OrderAdd = a.OrderAdd
FOR XML PATH('')
),
1,1,'') SClientNO
FROM a
ClientNO OrderDrugstoreName OrderCon OrderTel OrderCell OrderAdd Qty OrdQty SClientNO
----------------- ---------------------- -------- ------------ ------------ ---------------------------------- ----------- ----------- -----------------------------------
HYF20150430000466 普宁市创美电器有限公司 王城 0663-2237558 0663-2237558 池尾镇科技工业园计生办后面创美电器 72 53 HYF20150430000466-SHF20150513000803
HYF20150505001094 普宁市创美电器有限公司 江坚 0663-2254140 0663-2254140 池尾镇科技工业园计生办后面创美电器 21 21 HYF20150505001094