如何在不使用聚合函数的情况下对SQL中不同行的列进行分组? [英] How do I group a column from different rows in SQL without using an aggregate function?
本文介绍了如何在不使用聚合函数的情况下对SQL中不同行的列进行分组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的sql数据库中有两个表1,称为事务,数据就像下面的表格一样
trans_ID Item_ID
100 1
100 3
100 4
200 1
200 2
300 3
和另一个名为items的表,它具有以下属性
ITem_ID项目
1 I1
2 I2
3 I3
4 I4
5 I5
我想编写一个输出查询
Trans_ID项目
100 I1,I3,I4
200 I1,I2
300 I3
这可能吗?
我有什么尝试过:
我试过像
选择 trans_id,concat(Items,' ,')
来自 交易 加入项上的code-keyword> transaction .Item_ID = items.Item_ID
group by trans_id
但它带来错误
解决方案
检查:
sql server - 多行到一个以逗号分隔的值 - Stack Overflow [ ^ ]
将表中的行值转换为单个连接字符串 - SQLMatters [ ^ ]
你可以尝试这个< br $>
DECLARE @ txnid int
DECLARE @ table table (Txnid INT ,Items VARCHAR ( 15 ))
DECLARE cur CURSOR FOR
SELECT DISTINCT trans.Txnid FROM trans
OPEN cur
FETCH NEXT FROM cur INTO @ txnid
WHILE @@ FETCH_STATUS = 0
< span class =code-keyword> BEGIN
INSERT INTO @ table
SELECT @ txnid Txnid,STUFF(( SELECT ' ,' + items FROM 项 WHERE itemid in ( SELECT itemid FROM trans WHERE txnid = @ txnid) FOR XML PATH(' ')), 1 , 1 ,' ')项
FETCH NEXT FROM cur INTO @ txnid
END
SELECT * FROM @ table
CLOSE cur
DEALLOCATE cur
I have two tables in my sql database 1 called transaction and data in it is like in the following form
trans_ID Item_ID
100 1
100 3
100 4
200 1
200 2
300 3
and another table called items which has the following attributes
ITem_ID Item
1 I1
2 I2
3 I3
4 I4
5 I5
I want to write a query that gives an output of
Trans_ID Items
100 I1,I3,I4
200 I1,I2
300 I3
Is this possible ?
What I have tried:
I tried a query like
select trans_id , concat(Items,',')
from transaction join items on transaction.Item_ID=items.Item_ID
group by trans_id
but it brings an error
解决方案
Check this:
sql server - Multiple rows to one comma-separated value - Stack Overflow[^]
Converting row values in a table to a single concatenated string - SQLMatters[^]
You can try this
DECLARE @txnid int DECLARE @table table (Txnid INT,Items VARCHAR(15)) DECLARE cur CURSOR FOR SELECT DISTINCT trans.Txnid FROM trans OPEN cur FETCH NEXT FROM cur INTO @txnid WHILE @@FETCH_STATUS =0 BEGIN INSERT INTO @table SELECT @txnid Txnid,STUFF((SELECT ','+items FROM Items WHERE itemid in(SELECT itemid FROM trans WHERE txnid=@txnid) FOR XML PATH('')),1,1,'')Items FETCH NEXT FROM cur INTO @txnid END SELECT * FROM @table CLOSE cur DEALLOCATE cur
这篇关于如何在不使用聚合函数的情况下对SQL中不同行的列进行分组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文