SELECT * FROM tb2 AS b WHERE EXISTS(SELECT * FROM tb1 WHERE pid=b.pid AND pnum!=b.pnum)
--> liangCK小梁 于2008-10-07 --> 生成测试数据: @tb1 DECLARE @tb1 TABLE (pid INT,pnum INT) INSERT INTO @tb1 SELECT '1001','12' UNION ALL SELECT '1002','13' --> liangCK小梁 于2008-10-07 --> 生成测试数据: @tb2 DECLARE @tb2 TABLE (pid INT,pnum INT) INSERT INTO @tb2 SELECT '1001','13' UNION ALL SELECT '1002','13' UNION ALL SELECT '1003','25'--SQL查询如下:SELECT * FROM @tb2 AS b WHERE EXISTS(SELECT * FROM @tb1 WHERE pid=b.pid AND pnum!=b.pnum) /* pid pnum ----------- ----------- 1001 13(1 行受影响)*/
SELECT * FROM tb b WHERE EXISTS(SELECT 1 FROM ta WHERE pid=b.pid and pnum<>b.pnum)
select b.pid ,b.pnum from b where exists(select 1 from a where a.id=b.id and a.pum <> b.pum)
现在需要在原有基础上做一些改动: 原来查询的结果是: b pid pnum 1001 13现在要查询出: b pid pnum apnum(a表中对应的pnum,相当于在b表中加了一个字段,把a表中对应的pnum也显示出来) 1001 13 12
SELECT b.*,tb1.pnum as apnum FROM tb1,tb2 AS b WHERE EXISTS(SELECT * FROM tb1 WHERE pid=b.pid AND pnum!=b.pnum)
SELECT b.*,tb1.pnum as apnum FROM tb1,tb2 AS b WHERE EXISTS(SELECT * FROM tb1 WHERE pid=b.pid AND pnum!=b.pnum) AND b.pnum!=tb1.pnum
--如下: DECLARE @tb1 TABLE (pid INT,pnum INT) INSERT INTO @tb1 SELECT '1001','12' UNION ALL SELECT '1002','13'DECLARE @tb2 TABLE (pid INT,pnum INT) INSERT INTO @tb2 SELECT '1001','13' UNION ALL SELECT '1002','13' UNION ALL SELECT '1003','25' select a.*,b.pnum from @tb2 a,@tb1 b where a.pid=b.pid and a.pnum<>b.pnum
select b.*,a.pnum from @tb1 a join @tb2 b on a.pid=b.pid where a.pnum <>b.pnum
FROM tb2 AS b
WHERE EXISTS(SELECT *
FROM tb1
WHERE pid=b.pid
AND pnum!=b.pnum)
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (pid INT,pnum INT)
INSERT INTO @tb1
SELECT '1001','12' UNION ALL
SELECT '1002','13'
--> liangCK小梁 于2008-10-07
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (pid INT,pnum INT)
INSERT INTO @tb2
SELECT '1001','13' UNION ALL
SELECT '1002','13' UNION ALL
SELECT '1003','25'--SQL查询如下:SELECT *
FROM @tb2 AS b
WHERE EXISTS(SELECT *
FROM @tb1
WHERE pid=b.pid
AND pnum!=b.pnum)
/*
pid pnum
----------- -----------
1001 13(1 行受影响)*/
WHERE EXISTS(SELECT 1 FROM ta WHERE pid=b.pid and pnum<>b.pnum)
现在需要在原有基础上做一些改动:
原来查询的结果是:
b
pid pnum
1001 13现在要查询出:
b
pid pnum apnum(a表中对应的pnum,相当于在b表中加了一个字段,把a表中对应的pnum也显示出来)
1001 13 12
FROM tb1,tb2 AS b
WHERE EXISTS(SELECT *
FROM tb1
WHERE pid=b.pid
AND pnum!=b.pnum)
FROM tb1,tb2 AS b
WHERE EXISTS(SELECT *
FROM tb1
WHERE pid=b.pid
AND pnum!=b.pnum)
AND b.pnum!=tb1.pnum
pid pnum apnum
1001 13 12
1001 13 13多了一行数据
DECLARE @tb1 TABLE (pid INT,pnum INT)
INSERT INTO @tb1
SELECT '1001','12' UNION ALL
SELECT '1002','13'DECLARE @tb2 TABLE (pid INT,pnum INT)
INSERT INTO @tb2
SELECT '1001','13' UNION ALL
SELECT '1002','13' UNION ALL
SELECT '1003','25'
select a.*,b.pnum from @tb2 a,@tb1 b
where a.pid=b.pid and a.pnum<>b.pnum