我有這張表，如果 total2 <=2，我需要聚合 code1。基本上將其向上/組合總計到下一個最小值，以便欄位 total2 將 >2 。我怎樣才能在 t-sql 中做到這一點？感謝您的任何幫助。我對此感到很困惑。
生的：
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ad7f47b37d4694411b8a38db07356a7e
| 代碼1 | 代碼2 | 總計1 | 總計2 |
|---|---|---|---|
| AAA1 | 123 | 9 | 3 |
| AAA1 | 120 | 3 | 5 |
| AAA1 | 124 | 4 | 2 |
| AAA2 | 125 | 2 | 1 |
| AAA2 | 126 | 3 | 2 |
| AAA3 | 121 | 4 | 4 |
| AAA5 | 119 | 1 | 1 |
| AAA6 | 118 | 4 | 2 |
| AAA6 | 117 | 2 | 5 |
匯總：
| 代碼1 | 代碼2 | 總計1 | 總計2 |
|---|---|---|---|
| AAA1 | 120 | 3 | 5 |
| AAA1 | 123 124 | 13 | 5 |
| AAA2 | 125 126 | 5 | 3 |
| AAA3 | 121 | 4 | 4 |
| AAA5 | 119 | 1 | 1 |
| AAA6 | 118 117 | 6 | 7 |
uj5u.com熱心網友回復：
首先，您需要根據 確定至少 2 行total2。您可以使用 row_number()
row_number() over(partition by code1 order by total2)
一旦你確定了grp，就對其執行聚合（僅針對至少 2 行grp 2）
for total1and total2, use SUM(), for code2, 因為它是字串，所以使用字串聚合string_agg()
樣本資料 ：
| 代碼1 | 代碼2 | 總計1 | 總計2 |
|---|---|---|---|
| AAA1 | 123 | 9 | 3 |
| AAA1 | 120 | 3 | 5 |
| AAA1 | 124 | 4 | 2 |
| AAA2 | 125 | 2 | 1 |
| AAA2 | 126 | 3 | 2 |
| AAA3 | 121 | 4 | 4 |
| AAA5 | 119 | 1 | 1 |
| AAA6 | 118 | 4 | 2 |
| AAA6 | 117 | 2 | 5 |
with cte as
(
select *,
grp = case when row_number() over(partition by code1
order by total2) <= 2
then 2
else 1
end
from Test1
)
select code1,
code2 = case when grp = 1 then max(code2)
else string_agg(code2, ' ') within group (order by code2)
end,
total1 = case when grp = 1 then max(total1) else sum(total1) end,
total2 = case when grp = 1 then max(total2) else sum(total2) end
from cte
group by code1, grp
order by code1, grp
結果 ：
| 代碼1 | 代碼2 | 總計1 | 總計2 |
|---|---|---|---|
| AAA1 | 120 | 3 | 5 |
| AAA1 | 123 124 | 13 | 5 |
| AAA2 | 125 126 | 5 | 3 |
| AAA3 | 121 | 4 | 4 |
| AAA5 | 119 | 1 | 1 |
| AAA6 | 117 118 | 6 | 7 |
小提琴<>演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/468702.html
標籤：tsql
下一篇：在T-SQL中回圈游標