如何使用SQL Server中的连接记录对相同数据进行分组 [英] How to group by same data with connected records in SQL Server
本文介绍了如何使用SQL Server中的连接记录对相同数据进行分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个像......一样的表输出。
srno billno特殊数量费率增值额支付余额
1 25 aaa 5 20 5 105 400 135
2 25 qqq 5 50 5 225 400 135
3 25 fff 10 20 5 205 400 135
4 < span class =code-digit> 26 aaa 10 20 5 205 300 245
5 26 fff 10 20 5 205 300 245
但是我希望输出如下:
srno billno特殊数量费率大桶金额支付余额
1 25 aaa 5 20 5 105 400 135
qqq 5 50 5 225
fff 10 20 5 205
2 26 aaa 10 20 5 205 300 245
fff 10 20 5 205
如何使用T-SQL select命令获取此输出.. ..
解决方案
这是一个示例方法
DECLARE @ T TABLE
(
srno INT ,
billno INT ,
特别是 VARCHAR ( 100 ),
数量 INT ,
rate INT ,
vat INT ,
金额 INT ,
已支付 INT ,
余额 INT
)
INSERT INTO @ T
SELECT 1 , 25 ,' aaa', 5 , 20 , 5 , 105 , 400 , 135 UNION 全部
SELECT 2 , 25 ,' qqq', 5 , 50 , 5 , 225 , 400 , 135 UNION ALL
SELECT 3 , 25 ,' fff', 10 , 20 , 5 , 205 , 400 , 135 UNION ALL
SELECT 4 , 26 ,' aaa', 10 , 20 , 5 , 205 , 300 , 245 UNION ALL
< span class =code-keyword> SELECT 5 , 26 ,' fff', 10 , 20 , 5 , 205 , 300 , 245
SELECT
CASE WHEN T2.srno = T1.srno 那么 RowNo ELSE NULL END AS srno,
CASE WHEN T2.srno = T1.srno 那么 T1.billno ELSE NULL END AS billno,
特别是,数量, rate,vat,amount,
CASE WHEN T2.srno = T1.srno 那么 T1.paid ELSE NULL END 已付款,
CASE WHEN T2.srno = T1.srno THEN T1.balance ELSE NULL END 余额
FROM @ T T1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (订单 by billNo) AS RowNo,Min(srno) AS srno,billno FROM @ T
GROUP BY billno
)T2 ON T1.billno = T2.billno
ORDER BY T1.srNo,BillNo
i have a table output like....
srno billno particular Qty rate vat amount paid balance
1 25 aaa 5 20 5 105 400 135
2 25 qqq 5 50 5 225 400 135
3 25 fff 10 20 5 205 400 135
4 26 aaa 10 20 5 205 300 245
5 26 fff 10 20 5 205 300 245
but I want output like:
srno billno particular Qty rate vat amount paid balance
1 25 aaa 5 20 5 105 400 135
qqq 5 50 5 225
fff 10 20 5 205
2 26 aaa 10 20 5 205 300 245
fff 10 20 5 205
How to get this output with a T-SQL select command....
解决方案
Here is a sample approach
DECLARE @T TABLE ( srno INT, billno INT, particular VARCHAR(100), Qty INT, rate INT, vat INT, amount INT, paid INT, balance INT ) INSERT INTO @T SELECT 1,25,'aaa',5,20,5,105,400,135 UNION ALL SELECT 2,25,'qqq',5,50,5,225,400,135 UNION ALL SELECT 3,25,'fff',10,20,5,205,400,135 UNION ALL SELECT 4,26,'aaa',10,20,5,205,300,245 UNION ALL SELECT 5,26,'fff',10,20,5,205,300,245 SELECT CASE WHEN T2.srno = T1.srno THEN RowNo ELSE NULL END AS srno, CASE WHEN T2.srno = T1.srno THEN T1.billno ELSE NULL END AS billno, particular, Qty, rate, vat, amount, CASE WHEN T2.srno = T1.srno THEN T1.paid ELSE NULL END paid, CASE WHEN T2.srno = T1.srno THEN T1.balance ELSE NULL END balance FROM @T T1 INNER JOIN ( SELECT ROW_NUMBER() OVER (Order by billNo) AS RowNo, Min(srno) AS srno, billno FROM @T GROUP BY billno ) T2 ON T1.billno = T2.billno ORDER BY T1.srNo, BillNo
这篇关于如何使用SQL Server中的连接记录对相同数据进行分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文