如
id depid
1 49
2 49
3 49
4 50
5 50
我想得到
49 1,2,3
50 4,5
sql 语句如何写?
解决方案 »
- 一个PHP按照数组顺序查询MYSQL的问题,求助
- mysql已经独立出来了啊,恭喜恭喜啊!
- 复制表数据
- 一个关于mysql存储过程的问题
- 求一sql语句,高手请帮忙。
- 如何附带安装mysql server?
- sql-front中运行sql语句的时候怎么一次只能建立一个表?
- mysql中如何修改root口令?
- 求助!各位高手,用DELPHI7的ADOQUERY查询MYSQL数据厍时出现class eacessviocation whit message access violation at address 100072 in m
- 谁有MYSQL中文手册(是**.chm格式的)
- 字段赋一个随机数字值,求语句
- xpe操作系统 安装 mysql front 乱码
select depid,group_concat(id) from tbname group by depid;
其相反函数是find_in_set
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([name] varchar(4),[status] varchar(8))
insert [TB]
select '小张','普通员工' union all
select '小张','组长' union all
select '小二','经理' union all
select '小三','老板' union all
select '小三','员工'
GO
SELECT a.name,
status =stuff((
select ','+convert(varchar(20),status) FROM [TB] WHERE name = a.name for xml path(''))
,1,1,'')
FROM [TB] a
group by a.nameSELECT a.name,
status =STUFF(REPLACE(REPLACE(
(select status
FROM [TB]
where name = a.name
FOR XML AUTO
), '<TB status="', ','), '"/>', ''), 1, 1, '')
FROM [TB] a
group by a.name
--> 查询结果
SELECT * FROM [TB]
--> 删除表格
--DROP TABLE [TB]
+------+-------+
| id | depid |
+------+-------+
| 1 | 49 |
| 2 | 49 |
| 3 | 49 |
| 4 | 50 |
| 5 | 50 |
+------+-------+
5 rows in set (0.05 sec)mysql>
mysql> select depid,group_concat(id)
-> from t_a6633281
-> group by depid;
+-------+------------------+
| depid | group_concat(id) |
+-------+------------------+
| 49 | 1,2,3 |
| 50 | 4,5 |
+-------+------------------+
2 rows in set (0.09 sec)mysql>