SQL语句写法，求助。。

[gukehanxue]

在数据库中有一个Paper表，字段包括：纸编号（barcode）,物料号（material_no）,重量（net_weight）,直径（roll_d）,
怎样根据直径的大小，统计所有物料号下面纸的重量及数量。

报表格式如下：

物料号 直径（0-999）的数量 直径（0-999）的总重量 直径（1000-1200）数量 直径（1000-1200）总重量
00001 -- 3 -- 599-- 4 -- 800
00004 -- 5 -- 1800 --0 -- 0

[deepmist]

直径是唯一的吗？

[yutian_31]

select count(*) as 数量,sum(net_weight) as 重量 from Paper where roll_d >=0 and roll_d <=999

[yutian_31]

select material_no,isnull(b.数量，0）as ‘0-999数量’，isnull(b.重量 ，0）as ‘0-999重量’，...//c表的字段 from Paper a
left join(
select material_no,count(*) as 数量,sum(net_weight) as 重量
from Paper where roll_d >=0 and roll_d <=999
group by material_no
) b
on a.material_no=b.material_no
left join
(
select material_no,count(*) as 数量,sum(net_weight) as 重量
from Paper where roll_d >=1000 and roll_d <=1200
group by material_no
) c
on a.material_no=c.material_no
c表的字段 你自己写吧
你用子查询也可以实现，这个方法比较笨 等大侠给你更好的吧 呵呵

[yuna@liu]

为什么有两个直径和总重量 算哪个，还是都计算呢

select sum([直径（0-999）])+sum([直径(1000-1200)]) as 总数量，sum([（0-999）的总重量])+sum([（1000-1200）的总重量])as 总重量
fron Paper group by [物料号] 

[蛋炒饭]

select material_no,count(case when roll_d>0 OR roll_d<=999 then material_no end) as count1,
sum(case when roll_d>0 OR roll_d<=999 then net_weight end) as weight1,
count(case when roll_d>=1000 OR roll_d<=1200 then material_no end) as count1,
sum(case when roll_d>=1000 OR roll_d<=1200 then net_weight end) as weight1
from Paper
group by material_no

已测！

[蛋炒饭]

[Quote=引用 5 楼 sdnzhqng 的回复:]
select material_no,count(case when roll_d>0 OR roll_d<=999 then material_no end) as count1,
sum(case when roll_d>0 OR roll_d<=999 then net_weight end) as weight1,
count(case when roll_d>=1000 OR……
[/Quote]

不好意思：
发错了，正确的如下
select material_no,count(case when roll_d>0 and roll_d<=999 then material_no end) as count1,
sum(case when roll_d>0 and roll_d<=999 then net_weight end) as weight1,
count(case when roll_d>=1000 and roll_d<=1200 then material_no end) as count1,
sum(case when roll_d>=1000 and roll_d<=1200 then net_weight end) as weight1
from Paper
group by material_no

[gukehanxue]

[Quote=引用 4 楼 liuxiulian 的回复:]
为什么有两个直径和总重量 算哪个，还是都计算呢

SQL code


select sum([直径（0-999）])+sum([直径(1000-1200)]) as 总数量，sum([（0-999）的总重量])+sum([（1000-1200）的总重量])as 总重量
fron Paper group by [物料号]
[/Quote]


数量指的是直径在（0--999）内的paper总数量，重量是指直径在（0--999）范围内paper的总重量

[IE11下面经常卡到爆]

select material_no,
count(case when roll_d>0 OR roll_d<=999 then material_no end) as count1,
sum(case when roll_d>0 OR roll_d<=999 then net_weight end) as weight1,
count(case when roll_d>=1000 OR roll_d<=1200 then material_no end) as count2,
sum(case when roll_d>=1000 OR roll_d<=1200 then net_weight end) as weight2
from Paper
group by material_no

[jeje]

楼上的那个不错.容易看懂.