一个简单的重复数据统计问题,大家看看为什么我这样无法统计出来?
表a
ID date XX
1 2008062972 5,8,9,7,2
2 2008062971 0,9,6,1,4
3 2008062970 5,7,4,9,6
4 2008062969 5,1,1,1,7
5 2008062968 1,7,9,7,2我需要统计XX列最后3个数是否有重复的出现,我用了如下代码,但是统计有重复数字出现的ID,到底是哪里错了呢?
select *
from 表a
group by ID
having count(substring(XX,5,5))>1
order by ID
表a
ID date XX
1 2008062972 5,8,9,7,2
2 2008062971 0,9,6,1,4
3 2008062970 5,7,4,9,6
4 2008062969 5,1,1,1,7
5 2008062968 1,7,9,7,2我需要统计XX列最后3个数是否有重复的出现,我用了如下代码,但是统计有重复数字出现的ID,到底是哪里错了呢?
select *
from 表a
group by ID
having count(substring(XX,5,5))>1
order by ID
解决方案 »
- 不显示删除回复显示所有回复显示星级回复显示得分回复 SQL2008出错(判断表是否存在,然后删除再创建)
- 事务里执行update的疑问
- 请问如何在xp SP2 同时安装sql2000和sql2005?
- 求SQL语句
- 前面问了好多问题,再问一个!
- 专业地学习数据库编程--学习方式,步骤,以及相关书籍的选择,恳求高见!
- 谁知道在java中用DAO怎么连接数据库
- 恢复备份时,提示一个datafile找不到,我用nt backup manager做的备份 帮忙看看,谢谢各位!
- 删除数据库中重复记录,但需要保留一条记录,用DELECT语句;
- 求大神指教用Compression压缩表进行备份,数据是否完整?
- 工作无聊 没事可做 来逛逛 散分
- 给重复的记录做标志!
你这样只是表示字段XX:最后三位数重复的记录比如1 2008062972 5,8,9,7,2
1 2008021343 4,1,9,7,2
select 1 from tb where id=t.id group by substring(xx,5,5) having count(1)>1
)
group by ID 就根本不会有重复的啊
insert into @tb select 1,'2008062972','5,8,9,7,2'
insert into @tb select 2,'2008062971','0,9,6,1,4'
insert into @tb select 3,'2008062970','5,7,4,9,6'
insert into @tb select 4,'2008062969','5,1,1,1,7'
insert into @tb select 5,'2008062968','1,7,9,7,2'select * from @tb t where exists(
select 1 from @tb where id<>t.id and right(xx,5)=right(t.xx,5)
)select * from @tb where right(xx,5) in(
select right(xx,5) from @tb group by right(xx,5) having count(1)>1 )id date xx
1 2008062972 5,8,9,7,2
5 2008062968 1,7,9,7,2
借用4楼的表
select x.* from @tb x
join (select right(xx,5) as xx,count(1) as c from @tb group by right(xx,5) having count(1)>1) y on right(x.xx,5)=y.xx
但是我就用
select XX
from 表a
group by XX
having count(substring(XX,5,5))>1
--order by substring(XX,5,5)还是查不出最后3位数重复的情况啊,请问是不是(逗号)的问题?比如说“5,8,9,7,2”,我用count(substring(XX,5,5))>1,是不是列出从9开始的最后三位重复的记录?
from a
where substring(XX,5,5)
in
(select substring(XX,5,5) from a group by substring(XX,5,5)
having count(substring(XX,5,5))>1 )
from 表a
roup by substring(XX,5,5)
having count(substring(XX,5,5))>1
--order by substring(XX,5,5)