请教各位朋友： 表A的数据格式如下
name， stime， sdate ，scode
aaa, 08:20, 2007-10-10, 进入
aaa, 09:05, 2007-10-10, 外出
aaa, 10:10, 2007-10-10, 进入
bbb, 17:14, 2007-10-10, 外出 aaa, 08:21, 2007-10-11, 进入
aaa, 10:05, 2007-10-11, 外出
aaa, 10:10, 2007-10-11, 进入
aaa, 15:12, 2007-10-11, 外出 bbb, 08:25, 2007-10-11, 进入
bbb, 10:25, 2007-10-11, 外出
bbb, 12:21, 2007-10-11, 进入
bbb, 16:11, 2007-10-11, 外出 我想通过查询得到下面的表B
name,sdate,进入(min_time)，外出(max_time)
aaa, 2007-10-10, 08:20,17:14
aaa, 2007-10-11, 08:21,15:12
bbb, 2007-10-11, 08:25,16:11这个查询该如何写？
name， stime， sdate ，scode
aaa, 08:20, 2007-10-10, 进入
aaa, 09:05, 2007-10-10, 外出
aaa, 10:10, 2007-10-10, 进入
bbb, 17:14, 2007-10-10, 外出 aaa, 08:21, 2007-10-11, 进入
aaa, 10:05, 2007-10-11, 外出
aaa, 10:10, 2007-10-11, 进入
aaa, 15:12, 2007-10-11, 外出 bbb, 08:25, 2007-10-11, 进入
bbb, 10:25, 2007-10-11, 外出
bbb, 12:21, 2007-10-11, 进入
bbb, 16:11, 2007-10-11, 外出 我想通过查询得到下面的表B
name,sdate,进入(min_time)，外出(max_time)
aaa, 2007-10-10, 08:20,17:14
aaa, 2007-10-11, 08:21,15:12
bbb, 2007-10-11, 08:25,16:11这个查询该如何写？
普通行列转换(2007-11-18于海南三亚)假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
*/-------------------------------------------------------------------------
/*
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result int
)insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理
from tb
group by name
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql varchar(8000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name'
exec(@sql)
/*
姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74
*/-------------------------------------------------------------------
/*加个平均分，总分
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--静态SQL,指subject只有语文、数学、物理这三门课程。
select name 姓名,
max(case subject when '语文' then result else 0 end) 语文,
max(case subject when '数学' then result else 0 end) 数学,
max(case subject when '物理' then result else 0 end) 物理,
cast(avg(result*1.0) as decimal(18,2)) 平均分,
sum(result) 总分
from tb
group by name
/*
姓名 语文 数学 物理 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct Subject from tb) as a
set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1)
/*
姓名 数学 物理 语文 平均分 总分
---------- ----------- ----------- ----------- -------------------- -----------
李四 84 94 74 84.00 252
张三 83 93 74 83.33 250
*/drop table tb ---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94想变成
Name Subject Result
---------- ------- -----------
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
*/create table tb1
(
姓名 varchar(10) ,
语文 int ,
数学 int ,
物理 int
)insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end--------------------------------------------------------------------
/*加个平均分,总分
Name Subject Result
---------- ------- --------------------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
*/select * from
(
select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
union all
select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
union all
select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
union all
select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
union all
select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb1
max(case scode when '进入' then stime end) '进入',
max(case scode when '外出' then stime end) '外出'
from
(
select name,sdate,scode,stime = min(stime) from tb where scode '进入' group by name,sdate,scode
union all
select name,sdate,scode,stime = max(stime) from tb where scode '外出' group by name,sdate,scode
) t
group by name , sdate
insert into tb values('aaa','08:20','2007-10-10','进入')
insert into tb values('aaa','09:05','2007-10-10','外出')
insert into tb values('aaa','10:10','2007-10-10','进入')
insert into tb values('bbb','17:14','2007-10-10','外出')
insert into tb values('aaa','08:21','2007-10-11','进入')
insert into tb values('aaa','10:05','2007-10-11','外出')
insert into tb values('aaa','10:10','2007-10-11','进入')
insert into tb values('aaa','15:12','2007-10-11','外出')
insert into tb values('bbb','08:25','2007-10-11','进入')
insert into tb values('bbb','10:25','2007-10-11','外出')
insert into tb values('bbb','12:21','2007-10-11','进入')
insert into tb values('bbb','16:11','2007-10-11','外出')
go
select name , sdate,
max(case scode when '进入' then stime end) '进入',
max(case scode when '外出' then stime end) '外出'
from
(
select name,sdate,scode,stime = min(stime) from tb where scode = '进入' group by name,sdate,scode
union all
select name,sdate,scode,stime = max(stime) from tb where scode = '外出' group by name,sdate,scode
) t
group by name , sdate
order by name , sdatedrop table tb/*
name sdate 进入 外出
---------- ---------- ---------- ----------
aaa 2007-10-10 08:20 09:05
aaa 2007-10-11 08:21 15:12
bbb 2007-10-10 NULL 17:14
bbb 2007-10-11 08:25 16:11（所影响的行数为 4 行）*/
insert into jycnet values('aaa','08:20','2007-10-10','进入')
insert into jycnet values('aaa','09:05','2007-10-10','外出')
insert into jycnet values('aaa','10:10','2007-10-10','进入')
insert into jycnet values('aaa','17:14','2007-10-10','外出')
insert into jycnet values('aaa','08:21','2007-10-11','进入')
insert into jycnet values('aaa','10:05','2007-10-11','外出')
insert into jycnet values('aaa','10:10','2007-10-11','进入')
insert into jycnet values('aaa','15:12','2007-10-11','外出')
insert into jycnet values('bbb','08:25','2007-10-11','进入')
insert into jycnet values('bbb','10:25','2007-10-11','外出')
insert into jycnet values('bbb','12:21','2007-10-11','进入')
insert into jycnet values('bbb','16:11','2007-10-11','外出')select A.name,A.sdate,
max(case A.scode when '进入' then A.stime end) as '进入',
max(case A.scode when '外出' then A.stime end) as '外出'
from (
select name,sdate,scode,min(stime)as stime from jycnet where scode ='进入' group by name,sdate,scode
union all
select name,sdate,scode,max(stime) as stime from jycnet where scode ='外出' group by name,sdate,scode ) A
group by A.name, A.sdate==================================
name sdate 进入 外出
---------- ---------- ---------- ---------- aaa 2007-10-10 08:20 17:14
aaa 2007-10-11 08:21 15:12
bbb 2007-10-11 08:25 16:11
insert into @T values('aaa','08:20','2007-10-10','进入')
insert into @T values('aaa','09:05','2007-10-10','外出')
insert into @T values('aaa','10:10','2007-10-10','进入')
insert into @T values('aaa','17:14','2007-10-10','外出')
insert into @T values('aaa','08:21','2007-10-11','进入')
insert into @T values('aaa','10:05','2007-10-11','外出')
insert into @T values('aaa','10:10','2007-10-11','进入')
insert into @T values('aaa','15:12','2007-10-11','外出')
insert into @T values('bbb','08:25','2007-10-11','进入')
insert into @T values('bbb','10:25','2007-10-11','外出')
insert into @T values('bbb','12:21','2007-10-11','进入')
insert into @T values('bbb','16:11','2007-10-11','外出')
select
name,
sdate,
[进入]=stime,
[外出]=(select max(stime) from @T where name=t.name and scode='外出' and sdate=t.sdate)
from
@T t
where
scode='进入'
and not exists(select 1 from @T where name=t.name and sdate=t.sdate and scode=t.scode and stime<t.stime)name sdate 进入 外出
---------- ---------- ---------- ----------
aaa 2007-10-10 08:20 17:14
aaa 2007-10-11 08:21 15:12
bbb 2007-10-11 08:25 16:11（所影响的行数为 3 行）
max(case when scode = '外出' THEN stime else null end) as '外出'
FROM A
GROUP BY Name, sdate可以这样写吗？这样写有什么问题？