★★★★★ 高分求解SQL查询统计语句 ★★★★★

Question

————————————————————————
开发环境：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

Answer 1

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.销售额),IIF(ISNULL(表2.数量),0,表2.数量) AS 退货数量,IIF(ISNULL(表2.退货额),0,表2.退货额) FROM 表1，表2) a

Answer 2

简单, LEFT JOIN 一下就可以了

Answer 3

谢谢老大！
我测试看看另外在表3下面加个总计栏，代码如何写。如：
表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
总计                             74              21460   
                                                           

Answer 4

不好意思，忘了连接了
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的列和上面一致就可以了

Answer 5

谢谢大哥！
测试了一下：当表1和表2里出现重复商品时，达不到效果。因这2个表经常会遇到重复的商品。
如：
表1：
商品、数量、价格、销售额 
sp1   10    100   1000 
sp2   20    200   4000 
sp3   30    300   9000 
sp4   40    150   6000 
sp1   10    100   1000 
sp2   20    200   4000 
sp5   30    250   7500 
sp1   10    100   1000 
sp2   20    200   4000 表2：
商品、数量、价格、退货额 
sp3   12     220    2640   
sp4   14     100    1400 
sp5   20     200    2000
sp3   12     220    2640   
sp4   14     100    1400 
sp1   12     220    2640   
sp2   14     100    1400 可否再帮我一下啊。

Answer 6

declare @a table(商品 char(10),数量 int,价格 int,销售额 int) 
insert into @a select 'sp1' ,  10 ,   100,   1000 
union all select 'sp2' ,  20 ,   200 ,  4000 
union all select 'sp3' ,  30 ,   300 ,  9000 
union all select 'sp4' ,  40 ,   150 ,  6000 
union all select 'sp5' ,  30 ,   250 ,  7500 declare @b table(
商品 char(10),数量 int,价格 int,退货额 int)
insert into @b select 'sp3' ,  12 ,    220 ,   2640   
union all select 'sp4' ,  14  ,   100  ,  1400 
union all select 'sp5' ,  20  ,   200 ,   2000 
select a.商品,a.数量 as 销售数量,a.销售额,isnull(b.数量,0)as 退货数量,isnull(b.退货额,0)退货额,
(a.数量-isnull(b.数量,0))实销量,(a.销售额-isnull(b.退货额,0))实销额
from @a a left join @b b on a.商品=b.商品
union all select 商品='总计',sum(销售数量)销售数量,sum(销售额)销售额,sum(退货数量)退货数量,sum(退货额)退货额,
sum(实销量)实销量,sum(实销额)实销额 from 
(select a.商品,a.数量 as 销售数量,a.销售额,isnull(b.数量,0)as 退货数量,isnull(b.退货额,0)退货额,
(a.数量-isnull(b.数量,0))实销量,(a.销售额-isnull(b.退货额,0))实销额
from @a a left join @b b on a.商品=b.商品)a
result:
商品         销售数量        销售额         退货数量        退货额         实销量         实销额         
---------- ----------- ----------- ----------- ----------- ----------- ----------- 
sp1        10          1000        0           0           10          1000
sp2        20          4000        0           0           20          4000
sp3        30          9000        12          2640        18          6360
sp4        40          6000        14          1400        26          4600
sp5        30          7500        20          2000        10          5500
总计         130         27500       46          6040        84          21460（所影响的行数为 6 行）

Answer 7

caixia615你好
可否将SQL语句写得明白些啊?
a,b,@a,@b是代表什么?
谢谢.

Answer 8

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 商品

Answer 9

以下即可：
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 表1 group by 商品) a 
left join (select 商品,sum(数量) as 数量,sum(退货额) as 销售额 from 表2 group by 商品) b 
on a.商品 = b.商品

Answer 10

有重复的就Group 一下

Answer 11

大家好！谢谢帮助。因我是自学DELPHI，刚刚熟悉。8，9，10，11楼的大哥们，可否把你们的代码做个小小改动：把对应的3个表写清楚啊。题目里只提供了表1，表2，表3，其他没提供的表，尽量不要出现在代码里，因为我看不明白。表1：销售
表2：退货
表3：汇总等着解答，谢谢！或者发到我的信箱里[email protected]

Answer 12

1、
nz()是access的函数。
全部显示Nz 函数
参阅应用于示例特性当一个 Variant 为 Null 时，使用 Nz 函数可以返回 0、零长度字符串 (" ") 或其他指定值。例如，可以使用该函数将 Null 值转换为其他值并防止它通过表达式扩散。Nz(variant, [valueifnull])
Nz 函数具有以下参数：参数 说明 
variant 数据类型为 Variant 的变量。 
Valueifnull 可选参数（除非在查询中使用）。一个 Variant，提供当 variant 参数为 Null 时的返回值。该参数使您能够返回一个除 0 或零长度字符串以外的其他值。 
注释  如果在查询的表达式中使用不带 ValueIfNull 参数的 Nz 函数，则对包含空值的字段，其返回结果为零长度字符串。
 
如果 variant 参数的值为 Null，Nz 函数将返回零或零长度字符串（当用在查询表达式中时，总是返回零长度字符串），这取决于上下文表明该值是数值还是字符串。如果包含了可选 valueifnull 参数，则当 variant 参数为 Null 时，Nz 函数将返回由该参数指定的值。当在查询表达式中使用时，NZ 函数总是包含 valueifnull 参数。如果 variant 的值不为 Null，则 Nz 函数返回 variant 的值。说明
Nz 函数对可能包含 Null 值的表达式是很有用的。要使表达式即使在包含 Null 值时也能计算得到一个非 Null 值，可使用 Nz 函数来返回 0、零长度字符串或一个自定义的返回值。例如，表达式 2 + varX 在 Variant varX 为 Null 时总是返回 Null 值。而 2 + Nz(varX) 却返回 2。可以经常使用 Nz 函数作为 IIf 函数的替代函数。例如，在下面的代码中，需要用两个包含 IIf 函数的表达式来返回所需的结果。第一个包含 IIf 函数的表达式用于检查变量的值，并且如果该值为 Null，则将其转换为零。varTemp = IIf(IsNull(varFreight), 0, varFreight)
varResult = IIf(varTemp > 50, "High", "Low")

在下一个示例中，Nz 函数提供与第一个表达式相同的功能，并且一步即可获得所需的值而不需要分两步进行。varResult = IIf(Nz(varFreight) > 50, "High", "Low")

如果为可选参数 valueifnul 提供了一个值，则当 variant 为 Null 时，将返回该值。通过包含该可选参数，可以避免使用含有 IIf 函数的表达式。例如，下面的表达式使用 IIf 函数，在 varFreight 的值为 Null 时返回一个字符串。varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

在下一个示例中，为 Nz 函数提供的可选参数提供在 varFreight 为 Null 时返回的字符串。varResult = Nz(varFreight, "No Freight Charge")

示例
下面的示例对窗体上的一个控件进行计算，并根据控件值返回两个字符串之一。如果控件值为 Null，过程就会使用 Nz 函数将 Null 值转换为零长度字符串。Public Sub CheckValue()    Dim frm As Form
    Dim ctl As Control
    Dim varResult As Variant    ' Return Form object variable pointing to Orders form.
    Set frm = Forms!Orders    ' Return Control object variable pointing to ShipRegion.
    Set ctl = frm!ShipRegion    ' Choose result based on value of control.
    varResult = IIf(Nz(ctl.Value) = vbNullString, _
        "No value.", "Value is " & ctl.Value & ".")    ' Display result.
    MsgBox varResult, vbExclamationEnd Sub
1、nz是哪个表啊？是我题目里的表3么？可否帮我改改啊。nz()是access的函数。
不是你的表3.
2、T是总计的意思么？可以随便写么？T是查询的别名，可以写成其他的。

Answer 13

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下，你试下吧

Answer 14

就是因为你的帖子，害得我在Access版的回复被删除了，扣了4分信誉分。唉!

Answer 15

jing12你的原代码在DELPHI运行不了,我改了一下开头的一句:
select a.商品,a.数量 as 销售数量,a.销售额,b.数量 as 退货数量,b.退货额,  
a.数量-b.数量 as 实销量,a.销售额-b.退货额 as 实销额   
 
这样可以运行,也有结果.但就是涉及不到的数量和金额为都空,可否让他默认为0啊是否是isnull的问题啊其他朋友,有答案的,也可以帮助.只要跟答案差不多的,都给分.

Answer 16

把IsNull(Sql)函数换成Nz(Access)函数，用法相同!

Answer 17

谢谢大家的帮助，终于测试通过！
代码参考了：4楼，8楼，15楼
当然DELPHI里不支持ISNULL和NZ，得要转换成DELPHI支持的，才能测试通过。另外询问个小问题：
如要查询：表1里没有的数据，而表2里有的数据，是否需要用：union all ？如：8楼。
以后有问题，还得麻烦大家，再次感谢！结帖完毕。