自己调整要的显示格式 分别显示T1/T2SELECT *,'T1' AS Type FROM ( SELECT * FROM T1 EXCEPT SELECT * FROM T2 ) AS T1 UNION ALL SELECT *,'T2' AS Type FROM ( SELECT * FROM T2 EXCEPT SELECT * FROM T1 ) AS T2;
( (SELECT b.cCode,a.cInvCode,c.cInvName,c.cInvStd,a.iQuantity,b.cDefine1 FROM (SELECT * FROM UFDATA_001_2012.dbo.rdrecords11) AS a LEFT JOIN (SELECT * FROM UFDATA_001_2012.dbo.rdrecord11) AS b ON a.id=b.id LEFT JOIN (SELECT * FROM UFDATA_001_2012.dbo.Inventory) AS c ON a.cInvCode=c.cInvCode WHERE b.cCode LIKE 'd%') UNION (SELECT b.dno, a.MatNo,a.MaterialName,a.model,a.outQTY,c.cItemCode FROM (SELECT * FROM HY_CRM.dbo.TKN_OutStorage) AS a LEFT JOIN (SELECT * FROM HY_CRM.dbo.TKN_OutStorMain ) AS b ON a.OutID =b.ID LEFT JOIN (SELECT * FROM HY_CRM.dbo.TKN_MO) AS c ON b.OrderNo = c.OrderNo ))可能我没有说清楚,是我的问题 这两个语句,我想得出来不一样的地方。
试试INTERSECT操作 ;WITH a(col1,col2,col3)AS ( SELECT 1,1,2 UNION ALL SELECT 2,3,4 UNION ALL SELECT 3,5,1 ),b(col1,col2,col3)AS ( SELECT 1,1,2 UNION ALL SELECT 2,4,4 UNION ALL SELECT 3,5,1 UNION ALL SELECT 4,6,2 ) SELECT * FROM a INTERSECT SELECT * FROM b /* col1 col2 col3 ----------- ----------- ----------- 1 1 2 3 5 1 */
分别显示T1/T2SELECT *,'T1' AS Type
FROM ( SELECT *
FROM T1
EXCEPT
SELECT *
FROM T2 ) AS T1
UNION ALL
SELECT *,'T2' AS Type
FROM ( SELECT *
FROM T2
EXCEPT
SELECT *
FROM T1 ) AS T2;
(SELECT b.cCode,a.cInvCode,c.cInvName,c.cInvStd,a.iQuantity,b.cDefine1
FROM
(SELECT * FROM UFDATA_001_2012.dbo.rdrecords11) AS a
LEFT JOIN
(SELECT * FROM UFDATA_001_2012.dbo.rdrecord11) AS b
ON a.id=b.id
LEFT JOIN
(SELECT * FROM UFDATA_001_2012.dbo.Inventory) AS c
ON a.cInvCode=c.cInvCode
WHERE b.cCode LIKE 'd%')
UNION
(SELECT b.dno, a.MatNo,a.MaterialName,a.model,a.outQTY,c.cItemCode
FROM
(SELECT * FROM HY_CRM.dbo.TKN_OutStorage) AS a
LEFT JOIN
(SELECT * FROM HY_CRM.dbo.TKN_OutStorMain ) AS b
ON a.OutID =b.ID
LEFT JOIN
(SELECT * FROM HY_CRM.dbo.TKN_MO) AS c
ON b.OrderNo = c.OrderNo
))可能我没有说清楚,是我的问题
这两个语句,我想得出来不一样的地方。
;WITH a(col1,col2,col3)AS
(
SELECT 1,1,2 UNION ALL
SELECT 2,3,4 UNION ALL
SELECT 3,5,1
),b(col1,col2,col3)AS
(
SELECT 1,1,2 UNION ALL
SELECT 2,4,4 UNION ALL
SELECT 3,5,1 UNION ALL
SELECT 4,6,2
)
SELECT * FROM a
INTERSECT
SELECT * FROM b
/*
col1 col2 col3
----------- ----------- -----------
1 1 2
3 5 1
*/
如果两个表都要看,用INTERSECT取得交集,那么连个表不存在这个交集的数据就都是它特有的
except
SELECT * FROM b
/*
col1 col2 col3
----------- ----------- -----------
2 3 4
*/SELECT * FROM b
except
SELECT * FROM a
/*
col1 col2 col3
----------- ----------- -----------
2 4 4
4 6 2
*/