SQL SERVER的怪问题，你能解决吗

[vt]

请看下面的两个SQL

1）
select sum(b.销售额) as total from
(
select a.货号,sum(a.数量) as 销售数量,sum(a.售价 * a.数量) as 销售额 from
(
select 货号,售价, 数量 from sale_detail where
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime)
union all
select 货号,售价, 数量 from old_sale_detail where
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime)
) a group by a.货号
) b

2)
select sum(b.销售额) as total from
(
select a.货号,sum(a.数量) as 销售数量,sum(a.售价 * a.数量) as 销售额 from
(
select 货号,售价, sum(数量) as 数量 from sale_detail where 货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
union all
select 货号,售价, sum(数量) as 数量 from old_sale_detail where 货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
) a group by a.货号
) b

可以保证sale_detail 和 old_sale_detail 里面的货号全都在product中,但两个查询的结果是不一样的，很奇怪的，
不知是什么原因，你能看出问题吗

我把这个数据库（test）里的表备份下来了，要的话，把EMAIL留下，你可以自己试试看，

我一直在线等待结果

[vt]

没人看看啊

[pengdali]

select sum(b.销售额) as total from
(
select a.货号,sum(a.数量) as 销售数量,sum(a.售价 * a.数量) as 销售额 from
(
select 货号,售价, sum(数量) as 数量 from sale_detail where --货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
union all
select 货号,售价, sum(数量) as 数量 from old_sale_detail --where 货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
) a group by a.货号
) b

这个呢？和哪个一样？

[pengdali]

是：

select sum(b.销售额) as total from
(
select a.货号,sum(a.数量) as 销售数量,sum(a.售价 * a.数量) as 销售额 from
(
select 货号,售价, sum(数量) as 数量 from sale_detail where --货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
union all
select 货号,售价, sum(数量) as 数量 from old_sale_detail where --货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and
时间 between cast('2003-10-7 00:00:00' as smalldatetime)
and cast('2003-10-7 23:00:00' as smalldatetime) group by 货号,售价
) a group by a.货号
) b

[vt]

to pengdali(大力 V3.0)

看起来一样，但执行的结果是不一样的，你要不要用实际数据试试看

[pengdali]

我是问你这个的结果和1 还是2一样？

[vt]

和1 一样，谢谢了，能不能解释一下呢，这是什么样的原因呢

[pengdali]

还是你的

货号 in (select product.货号 from product inner join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%') and

这句有问题！

[pengdali]

select product.货号 from product left join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%'

或：

select product.货号 from product full join provider on product.供应商编号=provider.供应商编号 where product.货号 like '%'

试试

[vt]

谢谢你了，pengdali(大力 V3.0)

我们找到了原因，有些货号被从product中删除了，而我用的是等值连接，所以有些记录就没有被连接上，故两个查询的结果不对