##
原始数据：
id name count
1 张三 1000
2 老赵 500
3 兵哥 500
4 乌鸦 200
5 李四 1000
6 火鸡 200
7 王五 1000
效果数据展示：
count(count) name
3 张三 李四 王五
2 老赵 兵哥
2 乌鸦 火鸡
任意数据库皆可。
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-24 14:33:23
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[name] varchar(4),[count] int)
insert [huang]
select 1,'张三',1000 union all
select 2,'老赵',500 union all
select 3,'兵哥',500 union all
select 4,'乌鸦',200 union all
select 5,'李四',1000 union all
select 6,'火鸡',200 union all
select 7,'王五',1000
--------------开始查询--------------------------
select COUNT(a.[count])[count],
stuff((select ','+name from [huang] b
where b.[count]=a.[count]
for xml path('')),1,1,'') 'name'
from [huang] a
group by a.[count]
ORDER BY [count] DESC
----------------结果----------------------------
/*
count name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 张三,李四,王五
2 乌鸦,火鸡
2 老赵,兵哥
*/
------解决方案--------------------
试试这个;
if object_id('tb') is not null drop table tb
go
create table tb([id] int,[name] varchar(4),[count] int)
insert tb
select 1,'张三',1000 union all
select 2,'老赵',500 union all
select 3,'兵哥',500 union all
select 4,'乌鸦',200 union all
select 5,'李四',1000 union all
select 6,'火鸡',200 union all
select 7,'王五',1000
go
select COUNT(a.[count]) [count],
replace(stuff((select ','+name from tb b
where b.[count]=a.[count]
for xml path('')),1,1,''),',',' ') 'name'
from tb a
group by a.[count]
order by [count] desc
/*
count name
3 张三 李四 王五
2 乌鸦 火鸡
2 老赵 兵哥
*/
------解决方案--------------------
哦，如果用mysql，可以在mysql板块问问的哈。
