表结构如下:
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| user_id | int(10) | NO | | | |
| oper_id | int(10) | NO | | 0 | |
+------------+--------------+------+-----+---------+----------------+数据如下:
+---------+---------+
| user_id | oper_id |
+---------+---------+
| 16 | 888 |
| 20 | 10 |
| 20 | 0 |
| 20 | 0 |
| 20 | 0 |
| 20 | 11 |
| 20 | 0 |
+---------+---------+怎样显示像下面这样的结果呢:
+---------+---------+
| user_id | oper_id |
+---------+---------+
| 16 | 888 |
| 20 | 10,0,11|
+---------+---------+
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| user_id | int(10) | NO | | | |
| oper_id | int(10) | NO | | 0 | |
+------------+--------------+------+-----+---------+----------------+数据如下:
+---------+---------+
| user_id | oper_id |
+---------+---------+
| 16 | 888 |
| 20 | 10 |
| 20 | 0 |
| 20 | 0 |
| 20 | 0 |
| 20 | 11 |
| 20 | 0 |
+---------+---------+怎样显示像下面这样的结果呢:
+---------+---------+
| user_id | oper_id |
+---------+---------+
| 16 | 888 |
| 20 | 10,0,11|
+---------+---------+
select user_id,group_concat(oper_id) as oper_id from tablename group by user_id;
Returns Nvarchar(1000)
As
Begin
Declare @S Nvarchar(1000)
Set @S=''
Select @S=@S+','+oper_id from tt Where user_id=@ID Order By PK
Return(Stuff(@S,1,1,''))
End--测试
Select user_id,oper_id,max(dbo.GetCourse(user_id)) As Course from tt group by user_id,oper_id思路如上述,自己修改一下,代码是SQL SERVER的
此招真妙
select user_id,group_concat(oper_id) as oper_id from(
select distinct user_id ,oper_id from tablename ) as temp group by temp.user_id;
这样就可以了哦