a b
2010052301, JA100519002
2010052301, JA100519003
2010052301, JA100519004
2010052302, JA100519002
2010052302, KA100519003
2010052302, KA100519004
table 有这样的资料..
如何用一语句比较出a= 2010052301 和 a= 2010052302 ,具有不同b 列出来..
包括 a= 2010052301 有b列 但在a= 2010052302 没有b列,或 a= 2010052301 没有b列 但在a= 2010052302 b列
想要的结果 如
2010052301, JA100519003
2010052301, JA100519004
2010052302, KA100519003
2010052302, KA100519004
-----a= 2010052301 和 a= 2010052302 在b 列均有JA100519002 ..所以结果不产生
盼高手教我!!感激
2010052301, JA100519002
2010052301, JA100519003
2010052301, JA100519004
2010052302, JA100519002
2010052302, KA100519003
2010052302, KA100519004
table 有这样的资料..
如何用一语句比较出a= 2010052301 和 a= 2010052302 ,具有不同b 列出来..
包括 a= 2010052301 有b列 但在a= 2010052302 没有b列,或 a= 2010052301 没有b列 但在a= 2010052302 b列
想要的结果 如
2010052301, JA100519003
2010052301, JA100519004
2010052302, KA100519003
2010052302, KA100519004
-----a= 2010052301 和 a= 2010052302 在b 列均有JA100519002 ..所以结果不产生
盼高手教我!!感激
INSERT @a SELECT '2010052301', 'JA100519002'
union all select '2010052301', 'JA100519003'
union all select '2010052301', 'JA100519004'
union all select '2010052302', 'JA100519002'
union all select '2010052302', 'KA100519003'
union all select '2010052302', 'KA100519004' SELECT COALESCE(aa.a,bb.a) a,COALESCE(aa.b,bb.b) b FROM
(SELECT * FROM @a WHERE a='2010052301') aa
FULL JOIN
(SELECT * FROM @a WHERE a='2010052302') bb
ON aa.b=bb.b
WHERE aa.a+bb.a IS NULL --result
/*a b
-------------------- --------------------
2010052302 KA100519003
2010052302 KA100519004
2010052301 JA100519003
2010052301 JA100519004
*/
SELECT * FROM @a a WHERE NOT EXISTS(SELECT 1 FROM @a WHERE a<>a.a AND b=a.b)
a,
b
from
test
join (select
b,
Count(*) cous
from
test
group by
b
having
Count(*) =1)s
where
test.b = s.b
where a.b not in (select aa.b from tbl aa group by aa.b having count(aa.a)>1)