我想查询一个表中root相同并且order相同的字段的所有属性,表如下
id name root order
1 liu 3 56
2 li 6 3
...........
我要查出所有的属性,要怎么写SQL.
我一开始打算用group by root,orser having count(*)>1,但是只能得到root和order.
id name root order
1 liu 3 56
2 li 6 3
...........
我要查出所有的属性,要怎么写SQL.
我一开始打算用group by root,orser having count(*)>1,但是只能得到root和order.
id name root order
4 月 5 6
56 他 5 6
23 以 5 6
6 tt 9 7
12 gg 9 7
43 dd 9 7
where exists(select 1 from tb as tb2 where tb.root=tb2.root and tb.order=tb2.order and tb.id<>tb2.id)
order by root,order
(select root,orser from [表] group by root,orser having count(*)>1) b
on a.root = b.root and a.orser = b.orser注意这个方法很笨
EXCEPT
SELECT MIN(id),
MIN(name),
[root],
[order]
FROM tblTest GROUP BY [root], [order] HAVING COUNT(*) = 1
SELECT id,name,root,order FROM tblTest
EXCEPT
SELECT MIN(id),
MIN(name),
[root],
[order]
FROM tblTest GROUP BY [root], [order] HAVING COUNT(*) = 1
这个是在SQL 2005测试出来的,如果JavCof(咖啡的香浓,眼泪汇成的冰川。) 兄弟有什么补充下面请回帖,谢谢大家.