Tab1
No Type Operation_Name,Operation_DateTime
101 进 张三 2008-01-07
202 进 张三 2008-01-08
101 出 李四 2008-01-08要得到
No Type(进) Operation_Name(进),Operation_DateTime(进) Type(出) Operation_Name(出),Operation_DateTime(出)
101 进 张三 2008-01-07 出 李四 2008-01-08
202 进 张三 2008-01-08 NULL NULL NULL该怎么写代码
No Type Operation_Name,Operation_DateTime
101 进 张三 2008-01-07
202 进 张三 2008-01-08
101 出 李四 2008-01-08要得到
No Type(进) Operation_Name(进),Operation_DateTime(进) Type(出) Operation_Name(出),Operation_DateTime(出)
101 进 张三 2008-01-07 出 李四 2008-01-08
202 进 张三 2008-01-08 NULL NULL NULL该怎么写代码
Tab1
No Type Operation_Name,Operation_DateTime
101 进 张三 2008-01-06
202 进 张三 2008-01-08
101 出 李四 2008-01-08
101 进 张三 2008-01-07要得到
No Type(进) Operation_Name(进),Operation_DateTime(进) Type(出) Operation_Name(出),Operation_DateTime(出)
101 进 张三 2008-01-07 出 李四 2008-01-08
202 进 张三 2008-01-08 NULL NULL NULL 如果有重复的No+Type 就取Operation_DateTime最大的值,谢
max(case type then '进' then type else null end) Type_进,
max(case type then '进' then Operation_Name else null end) Operation_Name_进,
max(case type then '进' then Operation_DateTime else null end) Operation_DateTime_进,
max(case type then '出' then type else null end) Type_出,
max(case type then '出' then Operation_Name else null end) Operation_Name_出,
max(case type then '出' then Operation_DateTime else null end) Operation_DateTime_出
from tab1
group by no
insert into tb values('101', '进', '张三', '2008-01-07')
insert into tb values('202', '进', '张三', '2008-01-08')
insert into tb values('101', '出', '李四', '2008-01-08')
goselect no ,
max(case type when '进' then type else null end) Type_进,
max(case type when '进' then Operation_Name else null end) Operation_Name_进,
max(case type when '进' then Operation_DateTime else null end) Operation_DateTime_进,
max(case type when '出' then type else null end) Type_出,
max(case type when '出' then Operation_Name else null end) Operation_Name_出,
max(case type when '出' then Operation_DateTime else null end) Operation_DateTime_出
from tb
group by nodrop table tb/*
no Type_进 Operation_Name_进 Operation_DateTime_进 Type_出 Operation_Name_出 Operation_DateTime_出
---------- ---------- ---------------- -------------------- ---------- ---------------- --------------------
101 进 张三 2008-01-07 出 李四 2008-01-08
202 进 张三 2008-01-08 NULL NULL NULL
*/
insert into #tab values(101,'进','张三','2008-01-07')
insert into #tab values(202,'进','张三','2008-01-08')
insert into #tab values(101,'出','李四','2008-01-08')
select [no],[Type(进)]=max(case when [type]='进' then [type] else null end) ,
[Operation_Name(进)]=max(case when [type]='进' then Operation_Name else null end) ,
[Operation_DateTime(进)]=max(case when [type]='进' then Operation_DateTime else null end),
Type_出=max(case when [type]='出' then type else null end) ,
Operation_Name_出=max(case when [type]='出' then Operation_Name else null end) ,
Operation_DateTime_出=max(case when [type]='出' then Operation_DateTime else null end)
from #tab group by [no]
no Type(进) Operation_Name(进) Operation_DateTime(进) Type_出 Operation_Name_出 Operation_DateTime_出
----------- ---------- ----------------- ----------------------- ---------- ---------------- -----------------------
101 进 张三 2008-01-07 00:00:00.000 出 李四 2008-01-08 00:00:00.000
202 进 张三 2008-01-08 00:00:00.000 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了空值。
create table tb(No varchar(10) , Type varchar(10) , Operation_Name varchar(10),Operation_DateTime varchar(10))
insert into tb values('101', '进', '张三', '2008-01-06')
insert into tb values('202', '进', '张三', '2008-01-08')
insert into tb values('101', '出', '李四', '2008-01-08')
insert into tb values('101', '进', '张三', '2008-01-07')
goselect no ,
max(case type when '进' then type else null end) Type_进,
max(case type when '进' then Operation_Name else null end) Operation_Name_进,
max(case type when '进' then Operation_DateTime else null end) Operation_DateTime_进,
max(case type when '出' then type else null end) Type_出,
max(case type when '出' then Operation_Name else null end) Operation_Name_出,
max(case type when '出' then Operation_DateTime else null end) Operation_DateTime_出
from (select t.* from tb t where Operation_DateTime = (select max(Operation_DateTime) from tb where no = t.no and type = t.type)) m
group by nodrop table tb/*
no Type_进 Operation_Name_进 Operation_DateTime_进 Type_出 Operation_Name_出 Operation_DateTime_出
---------- ---------- ---------------- -------------------- ---------- ---------------- --------------------
101 进 张三 2008-01-07 出 李四 2008-01-08
202 进 张三 2008-01-08 NULL NULL NULL
*/
insert into @ta
select '101','進','張三','2008/01/07' union all
select '202','出','張三','2008/01/08' union all
select '101','進','李四','2008/01/08'select * from @taselect no ,
max(case type when '進' then type else null end) Type_進,
max(case type when '進' then Operation_Name else null end) Operation_Name_進,
max(case type when '進' then Operation_DateTime else null end) Operation_DateTime_進,
max(case type when '出' then type else null end) Type_出,
max(case type when '出' then Operation_Name else null end) Operation_Name_出,
max(case type when '出' then Operation_DateTime else null end) Operation_DateTime_出
from @ta
group by no101 進 張三 2008-01-07 00:00:00.000 出 李四 2008-01-08 00:00:00.000
202 進 張三 2008-01-08 00:00:00.000 NULL NULL NULL
SELECT distinct [NO] as [No(出)]
,(select top 1 [Type] from Tab1 where [NO] = a.[NO] and [Type]='進' order by Opration_DateTime desc) as [Type(進)]
,(select top 1 [Operation_Name] from Tab1 where [NO] = a.[NO] and [Type]='進' order by Opration_DateTime desc) as [Operation_Name(進)]
,(select top 1 [Opration_DateTime] from Tab1 where [NO] = a.[NO] and [Type]='進' order by Opration_DateTime desc) as [Opration_DateTime(進)]
,(select top 1 [Type] from Tab1 where [NO] = a.[NO] and [Type]='出' order by Opration_DateTime desc) as [Type(出)]
,(select top 1 [Operation_Name] from Tab1 where [NO] = a.[NO] and [Type]='出' order by Opration_DateTime desc) as [Operation_Name(出)]
,(select top 1 [Opration_DateTime] from Tab1 where [NO] = a.[NO] and [Type]='出' order by Opration_DateTime desc) as [Opration_DateTime(出)]
FROM Tab1 a 測試結果No Type(进) Operation_Name(进),Operation_DateTime(进) Type(出) Operation_Name(出),Operation_DateTime(出)
101 进 张三 2008-01-07 出 李四
2008-01-08
202 进 张三 2008-01-08 NULL NULL
NULL