如
123 a 一一
123 b 二二
124 a 三三
124 a 四四
124 c 五五
322 a 六六
432 b 七七
这样的话就当蓝色的两列是重复的，那么如何用SQL语句找出这些重复的记录？
ACCESS或MSSQL都可以
8 个解决方案
#1
select *
from ta a
left join (select col1,col2 from ta group by col1,col2 having count(1) > 1) b
on a.col1 = b.col1 and a.col2 = b.col2
#2
select *
from tb t
where (select count(1) from tb where col1=t.col1 and col2=t.col2)>1
#3
请问为什么有两个表？
#4
没有两个表.那个是子查询.
#5
select column1,column2
from tb
group by column1,column2
having count(*)>1
#6
看来主要问题是ACCESS不支持
count(1),count(*)
主知道就早一点转用MSSQL
count(1),count(*)
主知道就早一点转用MSSQL
#7
select a.*
from yourTable a inner join (
select col1,col2,count(*)
from yourTable
group by col1,col2
having count(*)>1) b on a.col1=b.col1 and a.col2=b.col2
试一下你就知道ACCESS也支持。
#8
呃，刚才一直不成功……谢谢楼上提醒
#1
select *
from ta a
left join (select col1,col2 from ta group by col1,col2 having count(1) > 1) b
on a.col1 = b.col1 and a.col2 = b.col2
#2
select *
from tb t
where (select count(1) from tb where col1=t.col1 and col2=t.col2)>1
#3
请问为什么有两个表？
#4
没有两个表.那个是子查询.
#5
select column1,column2
from tb
group by column1,column2
having count(*)>1
#6
看来主要问题是ACCESS不支持
count(1),count(*)
主知道就早一点转用MSSQL
count(1),count(*)
主知道就早一点转用MSSQL
#7
select a.*
from yourTable a inner join (
select col1,col2,count(*)
from yourTable
group by col1,col2
having count(*)>1) b on a.col1=b.col1 and a.col2=b.col2
试一下你就知道ACCESS也支持。
#8
呃，刚才一直不成功……谢谢楼上提醒