有个表
tmp
nbr charge name
1 2 x
1 3 y
1 3 z
2 3 x
2 5 y
其中name 的类型只有 x,y,z 三种类型
现在要查询出下列格式:
nbr x y z
1 2 3 3
1 3 5 0
其中x,y,z 列下的数字为相应的nbr 对应的charge
大家帮忙啊!!!
tmp
nbr charge name
1 2 x
1 3 y
1 3 z
2 3 x
2 5 y
其中name 的类型只有 x,y,z 三种类型
现在要查询出下列格式:
nbr x y z
1 2 3 3
1 3 5 0
其中x,y,z 列下的数字为相应的nbr 对应的charge
大家帮忙啊!!!
(select charge from tmp where nbr = a.nbr and name = x) as x,
(select charge from tmp where nbr = a.nbr and name = y) as y,
(select charge from tmp where nbr = a.nbr and name = z) as z
from tmp as a
[dz1] [int] NULL ,
[dz2] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into t1
select 1 ,'a'
union
select 1 ,'b'
union
select 1 ,'c'
union
select 2 ,'a'
union
select 2 ,'b'
union
select 2 ,'c'
union
select 3 ,'a'
union
select 3 ,'b'
union
select 3 ,'c'select sum(countx) sumall,
min(case when dz2='a' then countx end) countx1,
min(case when dz2='b' then countx end) countx2,
min(case when dz2='c' then countx end) countx3
from
(select dz2,count(dz1) as countx from t1 group by dz2) a
给人家分吧~还有你如果用的是MYSQL的话,这句SQL可能不能执行
(select charge from tmp where nbr = a.nbr and name = x) as x,
(select charge from tmp where nbr = a.nbr and name = y) as y,
(select charge from tmp where nbr = a.nbr and name = z) as z
from tmp as a
http://community.csdn.net/Expert/TopicView.asp?id=3324969
select distinct a.nbr,
(select charge from tmp where nbr = a.nbr and name = 'x') as x,
(select charge from tmp where nbr = a.nbr and name = 'y') as y,
(select charge from tmp where nbr = a.nbr and name = 'z') as z
from tmp as a
[nbr] [int] NULL ,
[charge] [int] NULL ,
[name] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into temp
select 1,2,'x'
union
select 1,3,'y'
union
select 1,3,'z'
union
select 2,3,'x'
union
select 2,5,'y'select nbr,
min(case when name='x' then charge end) x,
min(case when name='y' then charge end) y,
min(case when name='z' then charge end) z
from temp group by nbrdrop TABLE temp