--表的主要结构如下,要按照type分类,type为5,19的做为一组,type=17时作为另外一组,算其费用
create table testa(
tname varchar(16),--名字
infofee int,--费用
type int--费用类别
)insert into testa(tname,infofee,type) values('杨迎',10,5);
insert into testa(tname,infofee,type) values('杨迎',20,19);
insert into testa(tname,infofee,type) values('杨迎',30,19);
insert into testa(tname,infofee,type) values('杨迎',40,17);
insert into testa(tname,infofee,type) values('肖杉',50,17);select * from testa
--要求得到如下结果的查询语句
tname infofee1 infofee2
杨迎 60 40
肖杉 0 50
--表的结构,要求统计各个号码的和
create table testb(
number varchar(16),
suma int,
sumb int
)insert into testb(number,suma,sumb)values('aa',1,10);
insert into testb(number,suma,sumb)values('aa',2,11);
insert into testb(number,suma,sumb)values('aa',3,12);
insert into testb(number,suma,sumb)values('bb',4,13);
insert into testb(number,suma,sumb)values('bb',5,14);select * from testb--要求得到如下结果的查询语句
number suma sumb
aa 6 33
bb 9 27
有点急,谢谢大家的帮忙。
create table testa(
tname varchar(16),--名字
infofee int,--费用
type int--费用类别
)insert into testa(tname,infofee,type) values('杨迎',10,5);
insert into testa(tname,infofee,type) values('杨迎',20,19);
insert into testa(tname,infofee,type) values('杨迎',30,19);
insert into testa(tname,infofee,type) values('杨迎',40,17);
insert into testa(tname,infofee,type) values('肖杉',50,17);select * from testa
--要求得到如下结果的查询语句
tname infofee1 infofee2
杨迎 60 40
肖杉 0 50
--表的结构,要求统计各个号码的和
create table testb(
number varchar(16),
suma int,
sumb int
)insert into testb(number,suma,sumb)values('aa',1,10);
insert into testb(number,suma,sumb)values('aa',2,11);
insert into testb(number,suma,sumb)values('aa',3,12);
insert into testb(number,suma,sumb)values('bb',4,13);
insert into testb(number,suma,sumb)values('bb',5,14);select * from testb--要求得到如下结果的查询语句
number suma sumb
aa 6 33
bb 9 27
有点急,谢谢大家的帮忙。
SUM(CASE WHEN type=5 OR TYPE =19 THEN infofee ELSE 0 END )AS infofee1,
SUM(CASE WHEN type=17 THEN infofee ELSE 0 END )AS infofee2
FROM TB GROUP BY TNAME
tname,
sum(case type when 17 then 0 else infofee end) as infofee1,
sum(case type when 17 then infofee else 0 end) as infofee2
from
testa
group by
tname
select
[number],
sum(suma),
sum(sumb)
from
testb
group by
[number]
tname varchar(16),--名字
infofee int,--费用
type int--费用类别
) insert into testa(tname,infofee,type) values('杨迎',10,5);
insert into testa(tname,infofee,type) values('杨迎',20,19);
insert into testa(tname,infofee,type) values('杨迎',30,19);
insert into testa(tname,infofee,type) values('杨迎',40,17);
insert into testa(tname,infofee,type) values('肖杉',50,17); select tname,
infofee1=SUM(case when TYPE IN(5,19) then infofee else 0 end),
infofee2=SUM(case when TYPE =17 then infofee else 0 end)
from testa
group by tname
/*
(1 行受影响)
tname infofee1 infofee2
---------------- ----------- -----------
肖杉 0 50
杨迎 60 40 */
number varchar(16),
suma int,
sumb int
) insert into testb(number,suma,sumb)values('aa',1,10);
insert into testb(number,suma,sumb)values('aa',2,11);
insert into testb(number,suma,sumb)values('aa',3,12);
insert into testb(number,suma,sumb)values('bb',4,13);
insert into testb(number,suma,sumb)values('bb',5,14); select
[number],
sum(suma) as suma,
sum(sumb) as sumb
from
testb
group by
[number]
/*
number suma sumb
---------------- ----------- -----------
aa 6 33
bb 9 27*/
SELECT number ,
SUM(CASE number ='AA'THEN suma ELSE 0 END )AS suma,
SUM(CASE number='BB' THEN sumB ELSE 0 END )AS sumB
FROM TEST GROUP BY NUMBER
1、select tname,infofee1=isnull(sum(case when type in(5,19) then infofee end),0),
infofee2=isnull(sum(case when type=17 then infofee end),0)
from testa
group by tnametname infofee1 infofee2
肖杉 0 50
杨迎 60 40
SELECT tname ,
infofee1=SUM(CASE WHEN type=5 OR TYPE =19 THEN infofee ELSE 0 END ),
infofee2=SUM(CASE WHEN type=17 THEN infofee ELSE 0 END )
FROM testa GROUP BY TNAME
from testb
group by number