SQL问题,三天都没有答案,加到100分。请帮帮忙忙,在线等待。

[h_huajun]

有三个表(a1,a2,a3)
a1: 单号 产品型号 序列号
111 M111 1234
111 M111 2311
222 M222 5589
222 M111 5596
222 M111 7596
222 M222 4485
333 M333 8856

a2: 单号 产品编号 数量
111 b111 2
222 b222 3
444 b444 3

a3: 编号 产品型号
b111 M111
b222 M222
b333 M333
b444 M444

现我想把a1和a2两个表进行筛选与统计。形成另外两个表
one:(a1里存在，而a2里不存在，并且单号相同)

单号 产品型号 数量
222 M111 2

two:(a2里存在，而a1里不存在，并且单号相同)

单号 产品型号 数量
444 M444 3

说明:
1、a2的[产品编号]是从a3的[编号]里得到
2、two的[产品型号]是从a3的[产品型号]里得到
3、two表里不用统计它的数量

one和two各用一个SQL语句该如何实现?

[BES]

One :
select a2.單號, a2.產品型號 ,Sum(IsNull(a2.數量,0)) as 數量
from a1 , a2
where a1.單號=a2.單號 and
a1.單號+','+a1.產品型號 not in(select 單號+','+產品編號 from a2)
Group by a2.單號, a2.產品型號
-- a1 a2 單號相等 但是a1產品型號不存在a2表中

Two:
select a2.單號,a2.產品型號,a2.數量
from a1 , a2
where a1.單號=a2.單號 and
a2.單號+','+a2.產品編號 not in (select 單號+','+產品型號 from a1)
-- a1 a2 單號相等 但是a2產品型號不存在a1表中

不知道我這樣理解對不對?

[dejoy]

第一个
SELECT 单号,产品型号,count(*) AS 数量 INTO one FROM(SELECT a1.单号 AS 单号, a1.产品型号 AS 产品型号
FROM a1 LEFT JOIN (SELECT a2.单号, a3.产品型号, a2.数量
FROM a2 INNER JOIN a3 ON a2.产品编号 = a3.编号) AS a2 ON a1.单号 = a2.单号
WHERE (a1.产品型号<>a2.产品型号)) AS tmp GROUP BY 单号,产品型号;

第二个
SELECT a2.单号, a2.产品型号, a2.数量 INTO two
FROM a1 RIGHT JOIN (SELECT a2.单号, a3.产品型号, a2.数量
FROM a2 INNER JOIN a3 ON a2.产品编号 = a3.编号) AS a2 ON a1.单号 = a2.单号
WHERE (a1.产品型号 Is Null);

[dejoy]

用我的,我的已经搞电了

[coolicelee]

如果:在a3中编号与产品型号是一对一的关系,在a2中单号是主键或不可重复索引,a2中的产品编号依存于a3的编号,那么可以用以下方法试试
为了调试方便我把一些汉字变成了字母,除了数量定义为int外,其他定义为char(10)
产品编号=bh
序列号=sn
单号=dh
产品型号=xh
数量=sl

declare @a1sl int,@a2dh char(10),@a2bh char(10),@a2sl int,@a3xh char(10)
declare tmpc CURSOR
for
select dh,bh,sl from a2
open tmpc
fetch next from tmpc into @a2dh,@a2bh,@a2sl
while (@@fetch_status=0)
begin
select @a3xh=xh from a3 where bh=@a2bh
select @a1sl=count(*) from a1 where dh=@a2dh and xh<>@a3xh
select @a1sl,@a2sl
if @a1sl>0
insert into one (dh,xh,sl) values (@a2dh,@a3xh,@a1sl)
select @a1sl=count(*) from a1 where dh=@a2dh and xh=@a3xh
if @a1sl=0 and @a2sl>0
insert into two (dh,xh,sl) values (@a2dh,@a3xh,@a2sl)
fetch next from tmpc into @a2dh,@a2bh,@a2sl
end
close tmpc
deallocate tmpc
Windows 98 se/Windows 2000 Server + MS SQL server 7.0 调试通过

[dejoy]

第一个
SELECT 单号,产品型号,count(*) AS 数量 INTO one FROM
(SELECT a1.单号 AS 单号, a1.产品型号 AS 产品型号
FROM a1 LEFT JOIN (SELECT a2.单号, a3.产品型号, a2.数量
FROM a2 INNER JOIN a3 ON a2.产品编号 = a3.编号) AS a2
ON a1.单号 = a2.单号
WHERE (a1.产品型号<>a2.产品型号)) AS tmp
GROUP BY 单号,产品型号;

第二个
SELECT a2.单号, a2.产品型号, a2.数量 INTO two
FROM a1 RIGHT JOIN (SELECT a2.单号, a3.产品型号, a2.数量
FROM a2 INNER JOIN a3 ON a2.产品编号 = a3.编号) AS a2 ON a1.单号 = a2.单号
WHERE (a1.产品型号 Is Null);

[ihihonline]

好说好说，嘻嘻

[swayi21]

给我的感觉你的表有点冗余。
你只要对第一个表加一个Lookup（编号）字段，就可以综合三个表的数据。
这样做了以后，再把它存成新表，
然后再从这个新表中查询不就全出来了。
（个人偏见，见笑。）

[h_huajun]

但我只想用一个SQL语句实现

[h_huajun]

注:one 和 two 只是两查询结果集,并不是两存在的数据表

[artman]

one:a2里不存在，如何得到数量？？？
two:
select a2.单号,a3.产品型号,a2.数量 from a2,a3 where
a2.产品编号 = a3.编号 and a3.产品型号 not in (
select 产品型号 from a1 where a1.单号 = a2.单号 )

[ihihonline]

Select a1.单号,a1.产品型号,数量 form a1,a2 where Not (a1.产品型号 IN(Select Distinct 产品型号 form a2))
and a1.单号 = a2.单号

[h_huajun]

one: 数量是统计a1得出

[ihihonline]

上边的统计不出数量，应该用这个；
Select a1.单号,a1.产品型号,b.sum1 as 数量1 form a1,a2 Cross Join(Select Sum(数量) as sum1 From a2) b,a2 where Not (a1.产品型号 IN(Select Distinct 产品型号 form a2))
and a1.单号 = a2.单号

[ihihonline]

没有调试，没有数据库:(
呵呵，麻烦你调一调

[BES]

坚决帮你搞定 我下午一点上班。
QQ：112925366

[h_huajun]

to Bes96261(秋水孤鹜):
下午可能我不在

[h_huajun]

to ihihonline(小小->不要再流浪！！):
我测试过了，但查询结果不正确。

可能我说的不够明白，其实我的要求是这样的
查询结果集：
one: 按照a1的[单号]，跟a2关联起来,显示该[单号]的a1里存在，但a2里不存在的[产品型号](注:在这里假设a3里的[产品型号]是唯一的),并分别统计每种型号的数量。
two: 按照a1的[单号]，跟a2关联起来,显示该[单号]的a2里存在，但a1里不存在的[产品型号]。[数量]不用统计，可直接取数据表里的值。

注：a2里每个[单号]是唯一的；a1里第个[单号]里的每种[产品型号]可以有多条记录。