查询语句求指点~
本帖最后由 cyh027 于 2013-08-01 11:08:05 编辑 各位大大，小弟想设计一个查询语句，但不知道要怎么写了，请求指点。
表结构如下
分类 数值 ID
A 1 1
A 1 1
B 2 1
B 2 1
C 3 1
C 3 1
A 10 2
B 20 2
C 30 2
A 100 3
B 200 3
C 300 3
我想做一个分组统计查询，最后得到的结果如下
统计1= ID小于3的分类为A的数值的总合
统计2= ID小于3的分类B+ 分类C的总合
ID 统计1 统计2
1 2 10
2 10 50
[解决办法]
select ID, Sum(select 数值 from 表 where 分类=A)，Sum（select 数值 from 表 where 分类=B or 分类 =C）from 表
[解决办法]
select a.id,b.score1,c.score2 from (
select distinct id from tab1)a --表1
left join
(
select id,sum(score) as score1 from tab1 where type = 'A' and id <3 group by id
)b on b.id= a.id --表2
left join
(
select id,sum(score) as score2 from tab1 where (type = 'B' or type = 'C') and id <3 group by id
)c on c.id = a.id --表3
[解决办法]
WITH tt(Types,Num,ID)
AS
(SELECT 'A',1,1
UNION ALL
SELECT 'A',1,1
UNION ALL
SELECT 'B',2,1
UNION ALL
SELECT 'B',2,1
UNION ALL
SELECT 'C',3,1
UNION ALL
SELECT 'C',3,1
UNION ALL
SELECT 'A',10,2
UNION ALL
SELECT 'B',20,2
UNION ALL
SELECT 'C',30,2
UNION ALL
SELECT 'A',100,3
UNION ALL
SELECT 'B',200,3
UNION ALL
SELECT 'C',300,3)
SELECT A.ID,A.Tot1,B.Tot2
FROM (
SELECT ID,SUM(Num) AS Tot1 FROM tt WHERE ID<3 AND Types='A'
GROUP BY ID ) AS A
JOIN (
SELECT ID,SUM(Num) AS Tot2 FROM tt WHERE ID<3 AND Types IN ('B','C')
GROUP BY ID ) AS B ON A.ID=B.ID
WITH tt(Types,Num,ID)
AS
(SELECT 'A',1,1
UNION ALL
SELECT 'A',1,1
UNION ALL
SELECT 'B',2,1
UNION ALL
SELECT 'B',2,1
UNION ALL
SELECT 'C',3,1
UNION ALL
SELECT 'C',3,1
UNION ALL
SELECT 'A',10,2
UNION ALL
SELECT 'B',20,2
UNION ALL
SELECT 'C',30,2
UNION ALL
SELECT 'A',100,3
UNION ALL
SELECT 'B',200,3
UNION ALL
SELECT 'C',300,3)
select id
,sum(case when ID<3 AND Types='A' then Num else 0 end) as Tot1
,sum(case when ID<3 AND Types IN ('B','C') then Num else 0 end) as Tot1
from tt
where id<3
group by id
