★ 高分求解SQL查询统计语句 ★该如何处理
★★★★★ 高分求解SQL查询统计语句 ★★★★★
————————————————————————
开发环境：DELPHI 7 + ACCESS 2000 +ADO
要求：从表1和表2中查询统计，得到表3的样式。
————————————————————————
表1：销售表
商品、数量、价格、销售额
sp1 10 100 1000
sp2 20 200 4000
sp3 30 300 9000
sp4 40 150 6000
sp5 30 250 7500
表2：销售退货表
商品、数量、价格、退货额
sp3 12 220 2640
sp4 14 100 1400
sp5 20 200 2000
表3：销售分析表
商品、销售数量、销售额、退货数量、退货额、实销量、实销额
sp1 10 1000 0 0 10 1000
sp2 20 4000 0 0 20 4000
sp3 30 9000 12 2640 8 6360
sp4 40 6000 14 1400 26 4600
sp5 30 7500 20 2000 10 5500
------解决方案--------------------
不好意思，忘了连接了
SELECT a.商品,a.销售数量,a.销售额,a.退货数量,a.退货额,a.销售数量-a.退货数量 AS 实销量,a.销售额-a.退货额
AS 实销额 FROM (select 表1.商品,IIF(ISNULL(表1.数量),0,表1.数量) AS 销售数量,IIF(ISNULL(表1.销售额),0,表1.销售额) AS 销售额,IIF(ISNULL(表2.数量),0,表2.数量) AS 退货数量,IIF(ISNULL(表2.退货额),0,表2.退货额) AS 退货额 FROM 表1 LEFT JOIN 表2 ON 表1.商品=表2.商品) a
如果显示总计
可以用GridEh或者直接用SQL语句
UNION ALL SELECT '总计',SUM(销售额)..... FROM (select 表1.商品,IIF(ISNULL(表1.数量),0,表1.数量) AS 销售数量,IIF(ISNULL(表1.销售额),0,表1.销售额) AS 销售额,IIF(ISNULL(表2.数量),0,表2.数量) AS 退货数量,IIF(ISNULL(表2.退货额),0,表2.退货额) AS 退货额 FROM 表1 LEFT JOIN 表2 ON 表1.商品=表2.商品) a
注意union all的列和上面一致就可以了
------解决方案--------------------
select
商品,
nz(sum(num1),0) as 销售数量,
nz(sum(je1),0) as 销售金额,
nz(sum(num2),0) as 退货数量,
nz(sum(je2),0) as 退货金额,
nz(sum(num1),0)-nz(sum(num2),0) as 实销量,
nz(sum(je1),0)-nz(sum(je2),0) as 实销额
from
(
SELECT 商品, 数量 as num1, 销售额 as je1,null as num2,null as je2
FROM 销售表
union all
SELECT 商品, null as num1, null as je1, 数量 as num2 , 退货额 as je2
FROM 销售退货表
) as T
group by 商品
------解决方案--------------------
select a.商品,a.数量 as 销售数量,a.销售额,isnull(b.数量,0) as 退货数量,isnull(b.退货额,0),
a.数量-isnull(b.数量,0) as 实销量,a.销售额-isnull(b.退货额,0) as 实销额
from (select 商品,sum(数量) as 数量,sum(销售额) as 销售额 from 销售 group by 商品) a
left join (select 商品,sum(数量) as 数量,sum(退货额) as 销售额 from 退货 group by 商品) b
on a.商品 = b.商品
其中：a 为各种商品的销售总数量、销售总额
b 为各种商品的退货总数量、退货总额
执行这段语句得出来的就是表3（汇总）啦！
不过我没试过在Excel下，你试下吧
————————————————————————
开发环境：DELPHI 7 + ACCESS 2000 +ADO
要求：从表1和表2中查询统计，得到表3的样式。
————————————————————————
表1：销售表
商品、数量、价格、销售额
sp1 10 100 1000
sp2 20 200 4000
sp3 30 300 9000
sp4 40 150 6000
sp5 30 250 7500
表2：销售退货表
商品、数量、价格、退货额
sp3 12 220 2640
sp4 14 100 1400
sp5 20 200 2000
表3：销售分析表
商品、销售数量、销售额、退货数量、退货额、实销量、实销额
sp1 10 1000 0 0 10 1000
sp2 20 4000 0 0 20 4000
sp3 30 9000 12 2640 8 6360
sp4 40 6000 14 1400 26 4600
sp5 30 7500 20 2000 10 5500
------解决方案--------------------
不好意思，忘了连接了
SELECT a.商品,a.销售数量,a.销售额,a.退货数量,a.退货额,a.销售数量-a.退货数量 AS 实销量,a.销售额-a.退货额
AS 实销额 FROM (select 表1.商品,IIF(ISNULL(表1.数量),0,表1.数量) AS 销售数量,IIF(ISNULL(表1.销售额),0,表1.销售额) AS 销售额,IIF(ISNULL(表2.数量),0,表2.数量) AS 退货数量,IIF(ISNULL(表2.退货额),0,表2.退货额) AS 退货额 FROM 表1 LEFT JOIN 表2 ON 表1.商品=表2.商品) a
如果显示总计
可以用GridEh或者直接用SQL语句
UNION ALL SELECT '总计',SUM(销售额)..... FROM (select 表1.商品,IIF(ISNULL(表1.数量),0,表1.数量) AS 销售数量,IIF(ISNULL(表1.销售额),0,表1.销售额) AS 销售额,IIF(ISNULL(表2.数量),0,表2.数量) AS 退货数量,IIF(ISNULL(表2.退货额),0,表2.退货额) AS 退货额 FROM 表1 LEFT JOIN 表2 ON 表1.商品=表2.商品) a
注意union all的列和上面一致就可以了
------解决方案--------------------
select
商品,
nz(sum(num1),0) as 销售数量,
nz(sum(je1),0) as 销售金额,
nz(sum(num2),0) as 退货数量,
nz(sum(je2),0) as 退货金额,
nz(sum(num1),0)-nz(sum(num2),0) as 实销量,
nz(sum(je1),0)-nz(sum(je2),0) as 实销额
from
(
SELECT 商品, 数量 as num1, 销售额 as je1,null as num2,null as je2
FROM 销售表
union all
SELECT 商品, null as num1, null as je1, 数量 as num2 , 退货额 as je2
FROM 销售退货表
) as T
group by 商品
------解决方案--------------------
select a.商品,a.数量 as 销售数量,a.销售额,isnull(b.数量,0) as 退货数量,isnull(b.退货额,0),
a.数量-isnull(b.数量,0) as 实销量,a.销售额-isnull(b.退货额,0) as 实销额
from (select 商品,sum(数量) as 数量,sum(销售额) as 销售额 from 销售 group by 商品) a
left join (select 商品,sum(数量) as 数量,sum(退货额) as 销售额 from 退货 group by 商品) b
on a.商品 = b.商品
其中：a 为各种商品的销售总数量、销售总额
b 为各种商品的退货总数量、退货总额
执行这段语句得出来的就是表3（汇总）啦！
不过我没试过在Excel下，你试下吧