sql分组合并

[zwj8392008]

create table table01
(
id int,
kc_id int,
signedname varchar(10),
create_time varchar(10),
sign_img varchar(50)
)
insert into table01 values ('109','6','周六下午','1469066060','qrcode/14690660600001')
insert into table01 values ('110','6','周日上午','1469066060','qrcode/14690660600001')
insert into table01 values ('111','6','周日下午','1469066060','qrcode/14690660600001')
insert into table01 values ('108','6','周六上午','1468834015','qrcode/14688340150001')

create table table02
(
name varchar(10),
member_id int,
class_id int,
class_name varchar(50)
)
insert into table02 values ('苜倩','5118','17','国学10班')
insert into table02 values ('赵佳','5151','17','国学10班')
insert into table02 values ('王瑞林','5994','18','国学11班')
insert into table02 values ('吴军和','5121','18','国学11班')
insert into table02 values ('程程','5122','18','国学11班')
insert into table02 values ('李娜','5123','18','国学11班')

create table table03
(
id int,
kc_id int,
user_id int,
signedtime varchar(10),
typeid int,
name varchar(10)
)
insert into table03 values ('1321','6','5151','1469232597','108','智军')
insert into table03 values ('1322','6','5994','1469232722','108','王宝金')
insert into table03 values ('1390','6','5994','1469254644','109','王宝金')
insert into table03 values ('1403','6','5151','1469254964','109','智军')
insert into table03 values ('1441','6','5994','1469319308','110','王宝金')
insert into table03 values ('1495','6','5994','1469340709','111','王宝金')


select * from table01 
select * from table02
select * from table03 
select d.kc_id,d.[user_id],d.name,(case when d.signedname='周六上午' then d.signedtime end) 周六上午 , (case when d.signedname='周六下午' then d.signedtime end) 周六下午,(case when d.signedname='周日上午' then d.signedtime end) 周日上午 , (case when d.signedname='周日下午' then d.signedtime end) 周日下午,d.class_name,d.class_id from (select c.kc_id,c.[user_id],c.name,a.signedname,c.signedtime,b.class_name,b.class_id from table03 c left join (select ID,signedname,create_time from table01) a on c.typeid=a.id left join (select member_id,class_id,class_name from table02) b on c.[user_id]=b.member_id) d  
最后一句的结果为

但是下达到的效果是第二张图

[RINK_1]

select d.kc_id,d.[user_id],d.name, max((case when d.signedname='周六上午' then d.signedtime end)) 周六上午 , max((case when d.signedname='周六下午' then d.signedtime end)) 周六下午, max((case when d.signedname='周日上午' then d.signedtime end)) 周日上午 , max((case when d.signedname='周日下午' then d.signedtime end)) 周日下午, max(d.class_name) as class_name, max(d.class_id) as class_id from (select c.kc_id,c.[user_id],c.name,a.signedname,c.signedtime,b.class_name,b.class_id from table03 c left join (select ID,signedname,create_time from table01) a on c.typeid=a.id left join (select member_id,class_id,class_name from table02) b on c.[user_id]=b.member_id) d GROUP BY kc_id,user_id,name

[zwj8392008]

引用 1 楼 RINK_1 的回复:

select d.kc_id,d.[user_id],d.name, max((case when d.signedname='周六上午' then d.signedtime end)) 周六上午 , max((case when d.signedname='周六下午' then d.signedtime end)) 周六下午, max((case when d.signedname='周日上午' then d.signedtime end)) 周日上午 , max((case when d.signedname='周日下午' then d.signedtime end)) 周日下午, max(d.class_name) as class_name, max(d.class_id) as class_id from (select c.kc_id,c.[user_id],c.name,a.signedname,c.signedtime,b.class_name,b.class_id from table03 c left join (select ID,signedname,create_time from table01) a on c.typeid=a.id left join (select member_id,class_id,class_name from table02) b on c.[user_id]=b.member_id) d GROUP BY kc_id,user_id,name

多谢，居然忘了 max函数。。。。

[zwj8392008]

引用 1 楼 RINK_1 的回复:

select d.kc_id,d.[user_id],d.name, max((case when d.signedname='周六上午' then d.signedtime end)) 周六上午 , max((case when d.signedname='周六下午' then d.signedtime end)) 周六下午, max((case when d.signedname='周日上午' then d.signedtime end)) 周日上午 , max((case when d.signedname='周日下午' then d.signedtime end)) 周日下午, max(d.class_name) as class_name, max(d.class_id) as class_id from (select c.kc_id,c.[user_id],c.name,a.signedname,c.signedtime,b.class_name,b.class_id from table03 c left join (select ID,signedname,create_time from table01) a on c.typeid=a.id left join (select member_id,class_id,class_name from table02) b on c.[user_id]=b.member_id) d GROUP BY kc_id,user_id,name

我想咨询一下 我这个语句有没有简单一点的写法？

[道素]

和你的结果有点不一样，你看看是不是你要的

SELECT * FROM (
    SELECT  c.kc_id,c.[user_id],c.name,a.signedname,c.signedtime,b.class_name,b.class_id 
    from table03 c 
    LEFT JOIN table01 a on c.typeid=a.id 
    LEFT JOIN table02 b on c.[user_id]=b.member_id
) t
PIVOT(max(signedtime) FOR signedname IN ([周六上午],[周六下午],[周日上午],[周日下午])) d

/*
kc_id	user_id	name	class_name	class_id	周六上午	周六下午	周日上午	周日下午
6	5151	智军	国学10班	17	1469232597	1469254964	NULL	NULL
6	5994	王宝金	国学11班	18	1469232722	1469254644	1469319308	1469340709
*/