select id1 , id2
from t1
group by id1 , id2
having count(*) > 1
from t1
group by id1 , id2
having count(*) > 1
解决方案 »
- 连接sql sever 2008 数据库有什么好用的软件
- 删除记录,为什么不成功啊?
- xp 如何打开1433端口
- 求助,win2003 装的sql2008用IP登陆不上去
- sqlserver 中如何把一个int列,转化为标示列
- 专门给wanyingsong(豌豆)发表用的!
- ERWin哪有下载?
- 如何 读取 sql server 数据库的日志 也就是(.ldf)文件?
- 字段中数据格式转换问题
- 请问如何在paradox7里写case语句??急急啊!!
- 聚集函数_分组:Select MAX(lorange),MIN(hirange) From roysched
- how to transfer the following sub select to a string in view
select t1.id1 , t1.id2 from t1 ,
(select id1 , id2
from t1
group by id1 , id2
having count(*) > 1) t2
where t1.id1 = t2.id1 and t1.id2 = t2.id2方法2:(投机取巧:-)
select id1 , id2
from t1
group by id1 , id2
having count(*) > 1
union all
select id1 , id2
from t1
group by id1 , id2
having count(*) > 1
select id1+','+id2 from table group by id1,id2 having count(*)>1
)
将 varchar 值 ',' 转换为数据类型为 int 的列时发生语法错误。
”
select * from table where cast(id1 as varchar)+','+cast(id2 as varchar) in (
select id1+','+id2 from table group by id1,id2 having count(*)>1
)
declare @t1 table (id1 int,id2 int)
insert into @t1 values(8,3)
insert into @t1 values(8,3)
insert into @t1 values(5,6)
insert into @t1 values(5,2)
insert into @t1 values(7,6)
insert into @t1 values(7,6)select id1,id2
from @t1
where str(id1)+','+str(id2) not in (select str(id1)+','+str(id2)
from @t1
group by id1 , id2
having count(*) = 1)
select * from table where cast(id1 as varchar)+','+cast(id2 as varchar) in (
select cast(id1 as varchar)+','+cast(id2 as varchar) from table group by id1,id2 having count(*)>1
)
select id1,id2 from table where id1+','+id2 in (
select id1+','+id2 from table group by id1,id2 having count(*)>1)
注意如果id1、id2两个字段为int或smallint类型,提示先转换为char或varchar即可!
insert into @t1 values(8,3)
insert into @t1 values(8,3)
insert into @t1 values(5,6)
insert into @t1 values(5,2)
insert into @t1 values(7,6)
insert into @t1 values(7,6)select * from @t1 a where exists (select 1 from (select * from @t1 group by id1,id2 having count(*)>1) tem where tem.id1=a.id1 and tem.id2=a.id2)