sql选取重复的项解决方法

sql选取重复的项
table1 id, bianhao,col,yj1, yj2, yj3, yj4
  1, 1, a1,NULL,NULL,意见,意见
  2, 1, a5,NULL,NULL,意见,意见
  3, 2, a3,NULL,NULL,意见,意见
  4, 2, a3,意见,意见,意见,意见
  5, 1, a1,意见,NULL,NULL,NULL
 现在就是bianhao,col 有重复的内容，想把它选出来怎么写，谢谢~！

[解决办法]

SQL code

select * from table1 a where exists(select 1 from table1 where bianhao=a.bianhao and col=a.col and id<>a.id)
[解决办法]
SQL code
create table table1(id int,bianhao int,col varchar(10),yj1 varchar(10),yj2 varchar(10),yj3 varchar(10),yj4 varchar(10))insert into table1 select 1, 1,'a1',NULL,NULL,'意见','意见'insert into table1 select 2, 1,'a5',NULL,NULL,'意见','意见'insert into table1 select 3, 2,'a3',NULL,NULL,'意见','意见'insert into table1 select 4, 2,'a3','意见','意见','意见','意见'insert into table1 select 5, 1,'a1','意见',NULL,NULL,NULLgoselect * from table1 where id in(select a.id from table1 a inner join table1 b on a.id<>b.id and a.bianhao=b.bianhao and a.col=b.col)/*id          bianhao     col        yj1        yj2        yj3        yj4----------- ----------- ---------- ---------- ---------- ---------- ----------1           1           a1         NULL       NULL       意见         意见3           2           a3         NULL       NULL       意见         意见4           2           a3         意见         意见         意见         意见5           1           a1         意见         NULL       NULL       NULL(4 行受影响)*/godrop table table1
[解决办法]
SQL code
----借晴天大大的数据create table table1(id int,bianhao int,col varchar(10),yj1 varchar(10),yj2 varchar(10),yj3 varchar(10),yj4 varchar(10))insert into table1 select 1, 1,'a1',NULL,NULL,'意见','意见'insert into table1 select 2, 1,'a5',NULL,NULL,'意见','意见'insert into table1 select 3, 2,'a3',NULL,NULL,'意见','意见'insert into table1 select 4, 2,'a3','意见','意见','意见','意见'insert into table1 select 5, 1,'a1','意见',NULL,NULL,NULLgoselect * from table1 where CHECKSUM(bianhao,col) in(select CHECKSUM(bianhao,col) from table1 group by bianhao,col having count(1)>1)drop table table1/*id          bianhao     col        yj1        yj2        yj3        yj4----------- ----------- ---------- ---------- ---------- ---------- ----------1           1           a1         NULL       NULL       意见         意见3           2           a3         NULL       NULL       意见         意见4           2           a3         意见         意见         意见         意见5           1           a1         意见         NULL       NULL       NULL(4 行受影响)*/
[解决办法]
SQL code
if object_id('tb') is not null   drop table tbgocreate table tb( id int, bianhao int, col varchar(10), yj1 varchar(10), yj2 varchar(10), yj3 varchar(10), yj4 varchar(10))goinsert into tb (id,bianhao,col,yj1,yj2,yj3,yj4)select 1,1,'a1',null,null,'意见','意见' union allselect 2,1,'a5',null,null,'意见','意见' union allselect 3,2,'a3',null,null,'意见','意见' union allselect 4,2,'a3','意见','意见','意见','意见' union allselect 5,1,'a1','意见',null,null,nullgodelete a from tb a where exists(select 1 from tb where bianhao=a.bianhao and id<a.id)select * from tbgo/*id          bianhao     col        yj1        yj2        yj3        yj4----------- ----------- ---------- ---------- ---------- ---------- ----------1           1           a1         NULL       NULL       意见         意见3           2           a3         NULL       NULL       意见         意见(2 行受影响)*/
[解决办法]
delete a from tb a where exists(select 1 from tb where bianhao=a.bianhao or col=a.col and id<a.id)