带分组的SQL查询 [英] SQL Query with Group By

问题描述

嗨朋友们，



我对sql server 2005有疑问。我有一些记录，比如



无名称

1 aaa

1 bbb

2 ccc

1 ddd

3 eee

2 fff



我想要的结果如下：



没有名字

1 aaa，bbb，ddd

2 ccc，fff

3 eee



我试过：选择否，来自table1组的名称为no，name。



它会给出像我上面的表格格式的结果......但我想要以下格式。如何写查询？



无名称

1 aaa，bbb，ddd

2 ccc，fff

3 eee

Hi Friends,

I have a doubt in sql server 2005. I have a some records like

No Name
1 aaa
1 bbb
2 ccc
1 ddd
3 eee
2 fff

I want the result like :

No Name
1 aaa,bbb,ddd
2 ccc,fff
3 eee

I tried : select no,name from table1 group by no,name.

It will give result like my above table format... but i want the below format.How can write query?

No Name
1 aaa,bbb,ddd
2 ccc,fff
3 eee

推荐答案

试试这个：

Try this:

SELECT No,
       SUBSTRING(
       (Select (',' + Name)
       from Temp_Table t2
       where t1.No=t2.No
       order by
       No,
       Name
       FOR xml path( '' ) ),3,1000)
       FROM Temp_Table t1
       group by No

MySQL中没有像GROUP_CONCAT这样的等效函数，但是我找到了一个有趣的技巧，请尝试：< br $> b $ b

Hi, there is no equivalent function such a GROUP_CONCAT in MySQL but I found an interesting trick, please try:

SELECT
[No],
STUFF((
SELECT ', ' + CAST([Name] AS VARCHAR(MAX)) FROM [table1] WHERE [No] = [table1_copy].[No]
FOR XML PATH ('')),
1,2,'') AS [Name_Values]
FROM [table1] [table1_copy]
GROUP BY [No]

它对我有用，试试这个：

It works for me, try this:

Select distinct ST2.NO, 
           (Select ST1.NAME + ',' AS [text()]
            From dbo.AA ST1
            Where ST1.NO = ST2.NO
            ORDER BY ST1.NO
            For XML PATH ('')) [Name]
     From dbo.AA ST2

这篇关于带分组的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！