Sql group by的問題

ms sql

peterzn 2020-06-09 09:42:00 ‧ 1065 瀏覽

分享至

請問各位大神我想用以下sql導出出貨資料,可是出現的order欄位會反覆的加了好幾筆,不知道怎麼修改,可否請各位大神指點,如何修正..

select a.TA070 ship_date,a.TA046 car,
STUFF((select ','+k.TB004+'-'+k.TB005 from shipping2 k where k.TB001+k.TB002=c.TC001+c.TC002 FOR XML PATH('')),1,1,'') order,
case c.TC020
when 'order1' then e.TC004
when 'order2' then e.TC004
when 'order3' then f.TC004
else
d.TC004
end
Cust,
sum(c.TC007) Box,sum(c.TC011) Amount,sum(c.TC016*c.TC007) Wt1,sum(c.TC029) Wt2,sum(c.TC030) Volume,a.TA058 country,
case a.TA031
when '1' then 'aa'
when '2' then 'bb'
when '3' then 'cc'
else TA031
end ship from shipping1 a
left join picking c on a.TA001+a.TA002=c.TC001+c.TC002
left join db1..order1 d on c.TC020+c.TC021=d.TC001+d.TC002
left join db2..order2 e on c.TC020+c.TC021=e.TC001+e.TC002
left join db3..order3 f on c.TC020+c.TC021=f.TC001+f.TC002
where a.TA034='Y' and -datediff(d,getdate(),a.TA070)<15
group by a.TA070,a.TA046,d.TC004,e.TC004,f.TC004,a.TA058,a.TA031,c.TC001,c.TC002,c.TC001,c.TC002,c.TC020

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

做工仔人!

iT邦大師 1 級 ‧ 2020-06-09 11:12:58

最佳解答

select a.TA070 ship_date,a.TA046 car,
STUFF((select ','+k.TB004+'-'+k.TB005 from shipping2 k where k.TB001+k.TB002=c.TC001+c.TC002 FOR XML PATH('')),1,1,'') order,
case c.TC020
when 'order1' then e.TC004
when 'order2' then e.TC004
when 'order3' then f.TC004
else
d.TC004
end
Cust,
c.TC007 Box,c.TC011 Amount,c.Wt1,c.TC029 Wt2,c.TC030 Volume,a.TA058 country,
case a.TA031
when '1' then 'aa'
when '2' then 'bb'
when '3' then 'cc'
else TA031
end ship from shipping1 a
left join
(select TC001,TC002,sum(TC007) TC007 , SUM(TC011) TC011,SUM(TC016*TC007) Wt1,sum(TC029) TC029,sum(TC030) TC030
FROM picking
group by TC001,TC002) c on a.TA001+a.TA002=c.TC001+c.TC002
left join db1..order1 d on c.TC020+c.TC021=d.TC001+d.TC002
left join db2..order2 e on c.TC020+c.TC021=e.TC001+e.TC002
left join db3..order3 f on c.TC020+c.TC021=f.TC001+f.TC002
where a.TA034='Y' and -datediff(d,getdate(),a.TA070)<15

試試.
將GROUP BY 放在 PACKING 中

回應 1
分享
檢舉

peterzn iT邦新手 5 級 ‧ 2020-06-09 11:33:52 檢舉

做工仔人!感謝你的回覆,我在試試..

登入發表回應

REX

iT邦新手 4 級 ‧ 2020-06-09 19:51:02

select a.TA070 ship_date,a.TA046 car,
STUFF((select ','+k.TB004+'-'+k.TB005 from shipping2 k where k.TB001+k.TB002=c.TC001+c.TC002 FOR XML PATH('')),1,1,'') order,
case c.TC020
when 'order1' then e.TC004 ----根據你下面JOIN的表，相信這裡應該是打錯了，應該是when 'order1' then d.TC004，出來的資料有問題，也無法正確GROUPBY
when 'order2' then e.TC004
when 'order3' then f.TC004
else
d.TC004
end
Cust,