有2张表user和record,两张通过userid外键关联。
userid username
1 n1
2 n2
3 n3
4 n4
5 n5starttime userid hours
2010-10-15 00:00:00.000 1 4
2010-10-15 00:00:00.000 1 4
2010-10-15 00:00:00.000 5 6
2010-10-15 00:00:00.000 2 6
2010-10-15 00:00:00.000 1 4
2010-10-16 00:00:00.000 3 3
2010-10-17 00:00:00.000 1 6
需要按下面的格式来显示数据
姓名 10/14/2010 10/15/2010 10/16/2010 10/17/2010 10/18/2010
N1 12 6
N2 6
N3 3
N4
N5 6 每行一个username,列是日期,但是这个日期并不是和record的日期是一样的,他是按用户的要求来显示某几天的日期,日期下面显示的是当天这个user的hours的总和。这个怎么做?
userid username
1 n1
2 n2
3 n3
4 n4
5 n5starttime userid hours
2010-10-15 00:00:00.000 1 4
2010-10-15 00:00:00.000 1 4
2010-10-15 00:00:00.000 5 6
2010-10-15 00:00:00.000 2 6
2010-10-15 00:00:00.000 1 4
2010-10-16 00:00:00.000 3 3
2010-10-17 00:00:00.000 1 6
需要按下面的格式来显示数据
姓名 10/14/2010 10/15/2010 10/16/2010 10/17/2010 10/18/2010
N1 12 6
N2 6
N3 3
N4
N5 6 每行一个username,列是日期,但是这个日期并不是和record的日期是一样的,他是按用户的要求来显示某几天的日期,日期下面显示的是当天这个user的hours的总和。这个怎么做?
insert into [user] values(1 ,'n1')
insert into [user] values(2 ,'n2')
insert into [user] values(3 ,'n3')
insert into [user] values(4 ,'n4')
insert into [user] values(5 ,'n5')
create table record(starttime datetime,userid int, hours int)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-15 00:00:00.000', 5 ,6)
insert into record values('2010-10-15 00:00:00.000', 2 ,6)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-16 00:00:00.000', 3 ,3)
insert into record values('2010-10-17 00:00:00.000', 1 ,6)
goselect m.username ,
sum(case convert(varchar(10),n.starttime,120) when '2010-10-14' then n.hours else 0 end) [2010-10-14],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-15' then n.hours else 0 end) [2010-10-15],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-16' then n.hours else 0 end) [2010-10-16],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-17' then n.hours else 0 end) [2010-10-17],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-18' then n.hours else 0 end) [2010-10-18]
from [user] m ,record n
where m.userid = n.userid
group by m.usernamedrop table [user],record/*
username 2010-10-14 2010-10-15 2010-10-16 2010-10-17 2010-10-18
---------- ----------- ----------- ----------- ----------- -----------
n1 0 12 0 6 0
n2 0 6 0 0 0
n3 0 0 3 0 0
n5 0 6 0 0 0(所影响的行数为 4 行)
*/
insert into [user] values(1 ,'n1')
insert into [user] values(2 ,'n2')
insert into [user] values(3 ,'n3')
insert into [user] values(4 ,'n4')
insert into [user] values(5 ,'n5')
create table record(starttime datetime,userid int, hours int)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-15 00:00:00.000', 5 ,6)
insert into record values('2010-10-15 00:00:00.000', 2 ,6)
insert into record values('2010-10-15 00:00:00.000', 1 ,4)
insert into record values('2010-10-16 00:00:00.000', 3 ,3)
insert into record values('2010-10-17 00:00:00.000', 1 ,6)
goselect m.username ,
sum(case convert(varchar(10),n.starttime,120) when '2010-10-14' then n.hours else 0 end) [2010-10-14],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-15' then n.hours else 0 end) [2010-10-15],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-16' then n.hours else 0 end) [2010-10-16],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-17' then n.hours else 0 end) [2010-10-17],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-18' then n.hours else 0 end) [2010-10-18]
from [user] m left join record n
on m.userid = n.userid
group by m.usernamedrop table [user],record/*
username 2010-10-14 2010-10-15 2010-10-16 2010-10-17 2010-10-18
---------- ----------- ----------- ----------- ----------- -----------
n1 0 12 0 6 0
n2 0 6 0 0 0
n3 0 0 3 0 0
n4 0 0 0 0 0
n5 0 6 0 0 0(所影响的行数为 5 行)*/
sum(case convert(varchar(10),n.starttime,120) when '2010-10-14' then n.hours else 0 end) [2010-10-14],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-15' then n.hours else 0 end) [2010-10-15],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-16' then n.hours else 0 end) [2010-10-16],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-17' then n.hours else 0 end) [2010-10-17],
sum(case convert(varchar(10),n.starttime,120) when '2010-10-18' then n.hours else 0 end) [2010-10-18]
from [user] m left join record n
on m.userid = n.userid
group by m.username
gocreate table [tb]([BH] int, [nameCH] char(20),[SL] char(20))
insert [tb]
select 1,'A','A1' union all
select 2,'B','B2' union all
select 3,'A','A3' union all
select 4,'B','B4' union all
select 5,'A','A5' union all
select 6,'B','B6'
goselect * from tb---查询---
declare @sql varchar(8000)
select
@sql=isnull(@sql+',','')
+'max(case when px='+ltrim(px)+' then sl else '''' end) as [C'+ltrim(px)+']'
from
(select distinct px=(select count(1)+1 from tb where namech=t.namech and bh<t.bh) from tb t) tt
print @sql
--max(case when px=1 then sl else '' end) as [C1],max(case when px=2 then sl else '' end) as [C2],max(case when px=3 then sl else '' end) as [C3]set @sql='select NameCh,'
+@sql
+' from (select *,px=(select count(1)+1 from tb where namech=t.namech and bh<t.bh) from tb t) tt group by NameCh'
print @sql
--select NameCh,max(case when px=1 then sl else '' end) as [C1],max(case when px=2 then sl else '' end) as [C2],max(case when px=3 then sl else '' end) as [C3] from (select *,px=(select count(1)+1 from tb where namech=t.namech and bh<t.bh) from tb t) tt group by NameCh
exec (@sql)