求SQL:两表的对照,要求有点复杂.高人请帮忙.

Question

有两表:
tab1 
id  product  code (条码) 
1   主机     31134422
2   显示器   33211900
3   显示器   33211900
4   显示器   33211900
5   电视机   90011233
6   电风扇   10221388
7   电风扇   10221388
8   电钮丝   89909990
9   电钮丝   89909990tab2 
id2  code2
1   31134422
2   00001111
3   33211900
4   89909990
5   22220000
6   33211900
7   10221388
8   10221388我想写一SQL,将tab2与tab1对比,以tab1为主表,想实现:
如果tab1,tab2都有的,且数量够的，结果为ok
如果tab1有,tab2数量不够,结果为losted,意思是此件已丢件，看下面结果第2，3，4行就应该明白我的要求了。
如果tab1没有,tab2有,结果为more,意思是此条码是多余的,而多余的,要在最后显示。
显示如下:
id  product  code     id2  code2      result
1   主机     31134422  1   31134422   ok
2   显示器   33211900  3   33211900   ok           
3   显示器   33211900  6   33211900   ok  
4   显示器   33211900                 losted  注意：因为tab2中只有两个33211900,而tab1有三个,所以结果为losted 
5   电视机   90011233                 losted
6   电风扇   10221388  7   10221388   ok
7   电风扇   10221388  8   10221388   ok
8   电钮丝   89909990  4   89909990   ok 
9   电钮丝   89909990                 losted
                      2   00001111   more
                      5   22220000   more
要实现这样的要求，查询的SQL语句怎么写呢？

Answer 1

--> 测试数据: [tab1]
if object_id('[tab1]') is not null drop table [tab1]
create table [tab1] (id int,product varchar(6),code int)
insert into [tab1]
select 1,'主机',31134422 union all
select 2,'显示器',33211900 union all
select 3,'显示器',33211900 union all
select 4,'显示器',33211900 union all
select 5,'电视机',90011233 union all
select 6,'电风扇',10221388 union all
select 7,'电风扇',10221388 union all
select 8,'电钮丝',89909990 union all
select 9,'电钮丝',89909990
--> 测试数据: [tab2]
if object_id('[tab2]') is not null drop table [tab2]
create table [tab2] (id2 int,code2 varchar(8))
insert into [tab2]
select 1,'31134422' union all
select 2,'00001111' union all
select 3,'33211900' union all
select 4,'89909990' union all
select 5,'22220000' union all
select 6,'33211900' union all
select 7,'10221388' union all
select 8,'10221388';with cte1 as (select *,rn=ROW_NUMBER()over(PARTITION  by code order by id) from [tab1]),
cte2 as (select *,rn=ROW_NUMBER()over(PARTITION  by code2 order by id2) from [tab2])select *,result=(case when id2 is null then 'losted' when id is null then 'more' else 'ok' end)  from(
select t1.id,t1.product,t1.code,t2.id2,t2.code2
from cte1 t1
full join cte2 t2 on t1.code=t2.code2 and t1.rn=t2.rn)t order by isnull(id,1000)/*
id product code id2 code2 result
1 主机 31134422 1 31134422 ok
2 显示器 33211900 3 33211900 ok
3 显示器 33211900 6 33211900 ok
4 显示器 33211900 NULL NULL losted
5 电视机 90011233 NULL NULL losted
6 电风扇 10221388 7 10221388 ok
7 电风扇 10221388 8 10221388 ok
8 电钮丝 89909990 4 89909990 ok
9 电钮丝 89909990 NULL NULL losted
NULL NULL NULL 2 00001111 more
NULL NULL NULL 5 22220000 more
*/

Answer 2

create table tab1 (id int, product varchar(20), code varchar(20))
insert tab1 select 1,   '主机',     '31134422'
insert tab1 select 2,   '显示器',   '33211900'
insert tab1 select 3,   '显示器',   '33211900'
insert tab1 select 4,   '显示器',   '33211900'
insert tab1 select 5,   '电视机',   '90011233'
insert tab1 select 6,   '电风扇',   '10221388'
insert tab1 select 7,   '电风扇',   '10221388'
insert tab1 select 8,   '电钮丝',   '89909990'
insert tab1 select 9,   '电钮丝',   '89909990'create table tab2 (id2 int, code2 varchar(20))
insert tab2 select 1,   '31134422'
insert tab2 select 2,   '00001111'
insert tab2 select 3,   '33211900'
insert tab2 select 4,   '89909990'
insert tab2 select 5,   '22220000'
insert tab2 select 6,   '33211900'
insert tab2 select 7,   '10221388'
insert tab2 select 8,   '10221388'select 
id=isnull(cast(a.id as varchar(10)),''),product = isnull(a.product,''),code = isnull(a.code,''),
id2=isnull(cast(b.id2 as varchar(10)),''),code2 = isnull(b.code2,''),
result = case when a.id is null then 'more' when b.id2 is null then 'losted' else 'ok' end
from 
(
select *,rowid = row_number() over(partition by code order by id) from tab1
) a 
full join 
(
select *,rowid = row_number() over(partition by code2 order by id2) from tab2
) b on a.code = b.code2 and a.rowid = b.rowid
order by isnull(a.id,99999),b.id2/*
id         product              code                 id2        code2                result
---------- -------------------- -------------------- ---------- -------------------- ------
1          主机                   31134422             1          31134422             ok
2          显示器                  33211900             3          33211900             ok
3          显示器                  33211900             6          33211900             ok
4          显示器                  33211900                                             losted
5          电视机                  90011233                                             losted
6          电风扇                  10221388             7          10221388             ok
7          电风扇                  10221388             8          10221388             ok
8          电钮丝                  89909990             4          89909990             ok
9          电钮丝                  89909990                                             losted
                                                     2          00001111             more
                                                     5          22220000             more(11 行受影响)
*/
记得结贴哦

Answer 3

SQL server 2000的查询分析器中,运行不了呀.
;with cte1 as (select *,rn=ROW_NUMBER()over(PARTITION  by code order by id) from [tab1]),
cte2 as (select *,rn=ROW_NUMBER()over(PARTITION  by code2 order by id2) from [tab2])
这个地方有错.

Answer 4

ROW_NUMBER()over(PARTITION by code order by id) 是sql 2005的.你需要是用子查询来完成.

Answer 5

有关上述row_number在sql 2000的对应写法参考如下:表jh03有下列数据：
name　score
aa　　99
bb　　56
cc　　56
dd　　77
ee　　78
ff　　76
gg　　78
ff　　501. 名次生成方式1,Score重复时合并名次
SELECT *　,　Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8

Answer 6

--2000,修改#3
select 
    id=isnull(cast(a.id as varchar(10)),''),product = isnull(a.product,''),code = isnull(a.code,''),
    id2=isnull(cast(b.id2 as varchar(10)),''),code2 = isnull(b.code2,''),
    result = case when a.id is null then 'more' when b.id2 is null then 'losted' else 'ok' end
from 
(
    select *,rowid = (select count(1) from tab1 b where a.code = b.code and a.id >= b.id ) from tab1 a
) a 
full join 
(
    select *,rowid = (select count(1) from tab2 b where a.code2 = b.code2 and a.id2 >= b.id2 ) from tab2 a
) b on a.code = b.code2 and a.rowid = b.rowid
order by isnull(a.id,99999),b.id2

Answer 7

2000
--> 测试数据: [tab1]
if object_id('[tab1]') is not null drop table [tab1]
create table [tab1] (id int,product varchar(6),code int)
insert into [tab1]
select 1,'主机',31134422 union all
select 2,'显示器',33211900 union all
select 3,'显示器',33211900 union all
select 4,'显示器',33211900 union all
select 5,'电视机',90011233 union all
select 6,'电风扇',10221388 union all
select 7,'电风扇',10221388 union all
select 8,'电钮丝',89909990 union all
select 9,'电钮丝',89909990
--> 测试数据: [tab2]
if object_id('[tab2]') is not null drop table [tab2]
create table [tab2] (id2 int,code2 varchar(8))
insert into [tab2]
select 1,'31134422' union all
select 2,'00001111' union all
select 3,'33211900' union all
select 4,'89909990' union all
select 5,'22220000' union all
select 6,'33211900' union all
select 7,'10221388' union all
select 8,'10221388';with cte1 as (select *,rn=(select count(1) from [tab1] where code=t.code and id<=t.id) from [tab1] t),
cte2 as (select *,rn=(select count(1) from [tab2] where code2=tt.code2 and id2<=tt.id2)  from [tab2] tt)select *,result=(case when id2 is null then 'losted' when id is null then 'more' else 'ok' end)  from(
select t1.id,t1.product,t1.code,t2.id2,t2.code2
from cte1 t1
full join cte2 t2 on t1.code=t2.code2 and t1.rn=t2.rn)t order by isnull(id,1000)/*
id product code id2 code2 result
1 主机 31134422 1 31134422 ok
2 显示器 33211900 3 33211900 ok
3 显示器 33211900 6 33211900 ok
4 显示器 33211900 NULL NULL losted
5 电视机 90011233 NULL NULL losted
6 电风扇 10221388 7 10221388 ok
7 电风扇 10221388 8 10221388 ok
8 电钮丝 89909990 4 89909990 ok
9 电钮丝 89909990 NULL NULL losted
NULL NULL NULL 2 00001111 more
NULL NULL NULL 5 22220000 more
*/

Answer 8

DECLARE @code CHAR(20)
DECLARE @code2 CHAR(20)
DECLARE @id int
DECLARE @id2 INT
DECLARE @id3 INT 
DECLARE @i INT
DECLARE @j INT
SELECT id2,code2,'dddddd' AS result,0 AS id3 INTO tab2bak FROM tab2
SELECT  @j=MAX(id2) FROM tab2bak
DECLARE xie CURSOR FOR SELECT code,id FROM tab1
OPEN xie
FETCH NEXT FROM xie INTO @code,@id
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @code2=''
SET @id2=0
SET @i=1
WHILE(@i<=@j) 
BEGIN
SELECT @code2=code2,@id2=id2,@id3=id3 FROM tab2bak WHERE id2=@i AND result='dddddd'
IF(@code=@code2)
BEGIN
UPDATE tab2bak SET result='OK' WHERE id2=@id2
UPDATE tab2bak SET id3=@id WHERE id2=@id2
SET @i=@j+1
END
ELSE
BEGIN
SET @i=@i+1
END
END
FETCH NEXT FROM xie INTO @code,@id
END
INSERT INTO tab2bak(id2,code2,result,id3)
SELECT 0 AS id2,a.code,'losted',a.id FROM tab1 a LEFT JOIN tab2bak b
ON a.id=b.id3 AND a.code=b.CODE2
WHERE b.code2 IS NULL
UPDATE tab2bak SET result='more' FROM
(SELECT a.id2 FROM tab2bak a LEFT JOIN tab1 b ON a.id3=b.id AND a.code2=b.code  WHERE b.id IS null)
 b WHERE tab2bak.id2=b.id2
 SELECT * FROM tab2bak
 DEALLOCATE xie  SELECT isnull(b.product,''),a.* 
  FROM tab2bak a LEFT JOIN tab1 b ON a.id3=b.id ORDER BY id3
 

Answer 9

                     2 00001111             more 0
                     5 22220000             more 0
主机                 1 31134422             OK 1
显示器               3 33211900             OK 2
显示器               6 33211900             OK 3
显示器               0 33211900             losted 4
电视机               0 90011233             losted 5
电风扇               7 10221388             OK 6
电风扇               8 10221388             OK 7
电钮丝               4 89909990             OK 8
电钮丝               0 89909990             losted 9    这是结果 代码如上