数据库使用MS SQL 2000,
有两上表,我想找出两个表里两个工单号某一列的差异(类似 sql2008 的except功能),查询adatabase 工单号Work_order='131A-13070298'号的PCBSNSELECT DISTINCT a.PCBSN
FROM adatabase a
WHERE a.Work_order='131A-13070298'查询bdatabase 工单号Work_order='131a-13070243'号的PCBSNSELECT DISTINCT b.PCBSN
FROM bdatabase b
WHERE b.Work_order='131a-13070243'我需要找出这两个表不同工单号里的PCBSN差异, 如何使用sql2008 的except功能,很容易实现. 但在sql2000实现not exists, 不知道如何实现,求各位指点.谢谢.
有两上表,我想找出两个表里两个工单号某一列的差异(类似 sql2008 的except功能),查询adatabase 工单号Work_order='131A-13070298'号的PCBSNSELECT DISTINCT a.PCBSN
FROM adatabase a
WHERE a.Work_order='131A-13070298'查询bdatabase 工单号Work_order='131a-13070243'号的PCBSNSELECT DISTINCT b.PCBSN
FROM bdatabase b
WHERE b.Work_order='131a-13070243'我需要找出这两个表不同工单号里的PCBSN差异, 如何使用sql2008 的except功能,很容易实现. 但在sql2000实现not exists, 不知道如何实现,求各位指点.谢谢.
FROM adatabase a
WHERE a.Work_order='131A-13070298'
and NOT EXISTS
(
SELECT b.PCBSN
FROM bdatabase b
WHERE b.Work_order='131a-13070243'
AND a.PCBSN = b.PCBSN
)
--a表中工单号相同但版本号b表中不存在的记录
select *
from adatabase a
where not exsists(select 1 from bdatabase b where b.Work_order=a.Work_order and a.PCBSN=b.PCBSN)--b表中工单号相同但版本号a表中不存在的记录
select *
from bdatabase a
where not exsists(select 1 from adatabase b where b.Work_order=a.Work_order and a.PCBSN=b.PCBSN)
FROM
(
SELECT DISTINCT a.PCBSN
FROM adatabase a
WHERE a.Work_order='131A-13070298'
) a
FULL JOIN
(
SELECT DISTINCT b.PCBSN
FROM bdatabase b
WHERE b.Work_order='131a-13070243'
) b
ON a.PCBSN = b.PCBSN
WHERE a.PCBSN IS NULL OR b.PCBSN IS NULL
FROM adatabase a
WHERE a.Work_order='131A-13070298'
and not exists
(SELECT 1
FROM bdatabase b
WHERE b.Work_order='131a-13070243' and b.PCBSN=a.PCBSN)