请大家帮忙写一条汇总语句 - 编程论坛

#2

zhaoyang10082010-11-18 17:39

程序代码：

select 订单号，姓名，sum(生产良品) as 生产良品数，sum(生产不良品) as 生产不良品数,
sum(生产不良品)/(sum(生产良品)+sum(生产不良品)) as 模切报废率，
sum(生产不良品2)/(sum(生产良品2)+sum(生产不良品2)) as 模切报废率
from(select 订单号，姓名，作业类别，生产良品，生产不良品 from thistable
where 作业类别='模切') a

inner join
(select 订单号，作业类别 as 作业类别2，
生产良品 as 生产良品2，生产不良品 as 生产不良品2 from thistable
where 作业类别=‘整理’) b where a.订单号=b.订单号
group by 订单号，姓名

试试上面这个

#3

南川2010-11-18 17:49

程序代码：

select A.订单号,姓名,生产良品数,生产不良品数,模切报废率,整理报废率
from
(select 订单号,姓名,sum(生产良品) as 生产良品数,sum(生产不良品) as 生产不良品数,sum(生产不良品)/(sum(生产良品)+sum(生产不良品)) as 模切报废率
from orders
where 作业类别='模切'
group by 订单号,姓名) as A

left join

(select 订单号,sum(生产不良品)/(sum(生产良品)+sum(生产不良品)) as 整理报废率
from orders
where 作业类别='整理'
group by 订单号) as B

ON A.订单号=B.订单号

貌似你的ord002的模切报废率,整理报废率的汇总有问题吧？
如果你用int类型的话会得到比率计算结果0的情况，我这里用的是float类型来处理的
运算结果

只有本站会员才能查看附件，请登录

#4

aei1352010-11-19 09:37

SELECT 订单号,(SELECT distinct 姓名 FROM table a where 订单号=table.订单号 AND 作业类别='模切') 姓名,
SUM(CASE WHEN 作业类别='模切' THEN 生产良品 ELSE 0 END) 生产良品,
SUM(CASE WHEN 作业类别='模切' THEN 生产不良品 ELSE 0 END) 生产不良品,
SUM(CASE WHEN 作业类别='模切' THEN 生产不良品 ELSE 0 END)*1.0/(SUM(CASE WHEN 作业类别='模切' THEN 生产良品 ELSE 0 END)+SUM(CASE WHEN 作业类别='模切' THEN 生产不良品 ELSE 0 END)) 模切报废率,
SUM(CASE WHEN 作业类别='整理' THEN 生产不良品 ELSE 0 END)*1.0/(SUM(CASE WHEN 作业类别='整理' THEN 生产良品 ELSE 0 END)+SUM(CASE WHEN 作业类别='整理' THEN 生产不良品 ELSE 0 END)) 整理报废率
FROM table group by 订单号

#5

njy230002010-11-19 16:02

回复 4楼 aei135

向高手请教：

请教同组物品查询的方法-我只发难题
Create  TABLE MAIN.[object_group](
[object_title] varchar(50)
,[object_para] varchar(50)
,[group_title] varchar(50)
,[group_value] varchar(50)
, Primary Key(object_title,object_para,group_title,group_value)
);

Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","红木八仙桌","品种","家具");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","红木八仙桌","材质","红木");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","红木八仙桌","包装","一类包装-大");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("茶几","红木几","品种","家具");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("茶几","红木几","材质","红木");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("茶几","红木几","包装","二类包装-中");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","平头案","品种","家具");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","平头案","材质","梨花木");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("桌子","平头案","包装","一类包装-大");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("椅子","电脑转椅","品种","办公");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("椅子","电脑转椅","包装","二类包装-中");
Insert  Into [object_group] ([object_title],[object_para],[group_title],[group_value]) Values("椅子","电脑转椅","材质","革");

[object_title] 对象名称
[object_para] 对象参数
[group_title] 组名称
[group_value] 组取值

求查询语句写法：
输入：对象名称，对象参数
输出：和输入对象至少具有两个相同组名称和组取值的其他对象列表
通俗讲，就是：至少有两个分类定义相同的其他物品

也可以扩充为：有若干个分类定义相同

#6

aps20092010-11-21 10:08

select 订单号，姓名，sum(生产良品) as 生产良品数，sum(生产不良品) as 生产不良品数,
sum(生产不良品)/(sum(生产良品)+sum(生产不良品)) as 模切报废率，
sum(生产不良品2)/(sum(生产良品2)+sum(生产不良品2)) as 模切报废率
from(select 订单号，姓名，作业类别，生产良品，生产不良品 from thistable
where 作业类别='模切') a
inner join
(select 订单号，作业类别 as 作业类别2，
生产良品 as 生产良品2，生产不良品 as 生产不良品2 from thistable
where 作业类别=‘整理’) b where a.订单号=b.订单号
group by 订单号，姓名

#7

qq88011032010-11-23 11:40

个人同意3楼的

#8

BC1892010-11-28 14:44

求一师傅、教本人学习C语言的基础。来邮箱Johnson-189@。thanksgiving you！