解决方案 »
- 问个问题。麻烦大家
- SQL语句理解SELECT a.*, b.Key FROM SystemArtClass AS b INNER JOIN SystemArtClass AS a ON a.c_parent = b.id where b.key='NavMenu' orde
- 数据库中有能修改、添加、删除列名中的数据的语法吗??
- sql 2005 image字段问题
- 请帮我看这个SQL怎么写?
- 一个非常奇怪的问题!
- 求救!求救!关于sql数据库!
- '/' 特殊字符问题
- 查询结果中,如何取指定行的前10行和后10行的数据?
- in which state you can re-create control file
- tempdb中的表与与临时表有何区别?几处系统数据库的用处?
- 我是不是一定要安装sql server?
-- 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板块问问的哈。