很基础的问题!
我想查询一个表中root相同并且order相同的字段的所有属性,表如下
id name root order
1 liu 3 56
2 li 6 3
...........
我要查出所有的属性,要怎么写SQL.
我一开始打算用group by root,orser having count(*)> 1,但是只能得到root和order.
[解决办法]
select * from tb
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 a.* from [表] a inner join
(select root,orser from [表] group by root,orser having count(*)> 1) b
on a.root = b.root and a.orser = b.orser
注意这个方法很笨
[解决办法]
SELECT * FROM tblTest
EXCEPT
SELECT MIN(id),
MIN(name),
[root],
[order]
FROM tblTest GROUP BY [root], [order] HAVING COUNT(*) = 1
