• 主页
• 基础类
• 应用实例
• 新技术前沿

# 50分求查询语句，分少了没人理啊？

zhangwei1437 2006-01-23 12:08:09

1000 精肉 5.00
1001 味精 3.20
1002 糖 0.5

10000 1000 0.7
10000 1001 0.01
10000 1002 0.05
20000 1000 1.0
20000 3000 2.0

10000 1 10 中厨 2006-1-1-0001
10000 2 10 中厨 2006-1-1-0001
20000 1 5 中厨 2006-1-1-0001
30000 1 6 面点 2006-1-1-0002

select
a.cpbh as 编号,
sum(a.sl) as 销售数量,
sum(a.sl*a.jg) as 销售金额,
sum(a.sl*c.dj*b.yl) as 物料成本,
sum(a.sl*a.jg)-sum(a.sl*c.dj*b.yl) as 利润
from XFJLB a inner join PF b on a.cpbh=b.cpbh
inner join WLDA c on b.wlbh=c.wlbh
group by a.cpbh

...全文
109 点赞 收藏 2

2 条回复

-- 分两次group
select
a.cpbh as 编号,

sum(销售数量*b.dj) as 物料成本,
sum(销售金额)-sum(销售数量*b.dj) as 利润
from(
select
a.cpbh,
b.wlbh,
sum(a.sl) as 销售数量,
sum(a.sl*a.jg) as 销售金额
from XFJLB a
inner join PF b on a.cpbh=b.cpbh
group by a.cpbh, b.wlbh
)a
inner join WLDA c on b.wlbh=c.wlbh
group by a.cpbh

select x.cpbh as 编号,sum(x.sl) as 销售数量,sum(x.sl*x.jg) as 销售金额,sum(x.sl*t.zj) as 成本,sum(x.sl*x.jg)-sum(x.sl*t.zj) as 利润
from XFJLB x inner join (select cpbh,sum(yl*dj) as zj from PF p inner join WLDA w on p.wlbh=w.wlbh group by cpbh) t on x.cpbh=t.cpbh
group by x.cpbh

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区