数据库表的记录如下:
id(自增长) name riqi type(类型) num1 num2
1 A 2010-09-13 1 5 4
2 B 2010-09-13 1 3 1
3 B 2010-09-13 2 6 0
4 C 2010-09-13 1 4 7
5 A 2010-09-13 2 5 2
6 C 2010-09-13 2 3 3
查询A后得:
1 2
2010-09-13 5 4 5 2
查询B后得:
1 2
2010-09-13 3 1 6 0还有就是一次查出ABC的格式如下:
A B C
1 2 1 2 1 2
2010-09-13 5 4 5 2 3 1 6 0 4 7 3 3
id(自增长) name riqi type(类型) num1 num2
1 A 2010-09-13 1 5 4
2 B 2010-09-13 1 3 1
3 B 2010-09-13 2 6 0
4 C 2010-09-13 1 4 7
5 A 2010-09-13 2 5 2
6 C 2010-09-13 2 3 3
查询A后得:
1 2
2010-09-13 5 4 5 2
查询B后得:
1 2
2010-09-13 3 1 6 0还有就是一次查出ABC的格式如下:
A B C
1 2 1 2 1 2
2010-09-13 5 4 5 2 3 1 6 0 4 7 3 3
--其他自己写
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1] (id int,name varchar(1),riqi datetime,type int,num1 int,num2 int)
insert into [tb1]
select 1,'A','2010-09-13',1,5,4 union all
select 2,'B','2010-09-13',1,3,1 union all
select 3,'B','2010-09-13',2,6,0 union all
select 4,'C','2010-09-13',1,4,7 union all
select 5,'A','2010-09-13',2,5,2 union all
select 6,'C','2010-09-13',2,3,3select riqi,
name,
n1 =max(case when type=1 then num1 end),
n2 =max(case when type=1 then num2 end),
n3 =max(case when type=2 then num1 end),
n4 =max(case when type=2 then num2 end)
from [tb1]
where name='A'
group by riqi,name
riqi name n1 n2 n3 n4
----------------------- ---- ----------- ----------- ----------- -----------
2010-09-13 00:00:00.000 A 5 4 5 2
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)
name,
n1 =max(case when type=1 then num1 end),
n2 =max(case when type=1 then num2 end),
n3 =max(case when type=2 then num1 end),
n4 =max(case when type=2 then num2 end)
from [tb1]
where name='B'
group by riqi,nameriqi name n1 n2 n3 n4
----------------------- ---- ----------- ----------- ----------- -----------
2010-09-13 00:00:00.000 B 3 1 6 0
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)
n1 =max(case when type=1 AND NAME='A' then num1 end),
n2 =max(case when type=1 AND NAME='A' then num2 end),
n3 =max(case when type=2 AND NAME='A' then num1 end),
n4 =max(case when type=2 AND NAME='A' then num2 end),
n5 =max(case when type=1 AND NAME='B' then num1 end),
n6 =max(case when type=1 AND NAME='B' then num2 end),
n7 =max(case when type=2 AND NAME='B' then num1 end),
n8 =max(case when type=2 AND NAME='B' then num2 end),
n9 =max(case when type=1 AND NAME='C' then num1 end),
n10 =max(case when type=1 AND NAME='C' then num2 end),
n11=max(case when type=2 AND NAME='C' then num1 end),
n12 =max(case when type=2 AND NAME='C' then num2 end)
from [tb1]
--where name='B'
group by riqi riqi n1 n2 n3 n4 n5 n6 n7 n8 n9 n10 n11 n12
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2010-09-13 00:00:00.000 5 4 5 2 3 1 6 0 4 7 3 3
警告: 聚合或其他 SET 操作消除了空值。(1 行受影响)