SQL重复记录查询
SQL重复记录查询
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from
people
where peopleId in (select peopleId from people group by peopleId
having count
(peopleId) > 1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId
having count
(peopleId) > 1)
and rowid not in (select min(rowid) from people group
by peopleId having count(peopleId
)>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq)
in (select peopleId,seq from vitae group by peopleId,seq having
count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq
having
count(*) > 1)
and rowid not in (select min(rowid) from vitae group by
peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq
having
count(*) > 1)
and rowid not in (select min(rowid) from vitae group by
peopleId,seq having
count(*)>1)
(二)
比方说
在A表中存在一个字段“name”,
而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select
Name,Count(*) from A Group By Name Having Count(*) >
1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) from A Group By Name,sex Having
Count(*) > 1
(三)
方法一
declare @max integer,@id integer
declare
cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having
count(*) >; 1
open cur_rows
fetch cur_rows into @id,@max
while
@@fetch_status=0
begin
s
相关文档:
有关SQL中逻辑查询处理的各个阶段(自己搞的例子,不对的欢迎指正哦)
SQL不同于其他的编程语言的最大最大特征有3个吧,
一个是它是面向集合的编程思想,第二个是三值逻辑(这个后面会说到),还有一个就是今天主要要说的查询元素的逻辑处理次序。
请看一个基本查询的逻辑过程:
(8) SELECT (9) DISTINCT ( ......
交叉表语句的实现:
用于:交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文'
from test
group by name ......
http://cheneyph.javaeye.com/blog/477829
在Java中,获得ResultSet的总行数的方法有以下几种。
第一种:利用ResultSet的getRow方法来获得ResultSet的总行数
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABL ......
--SQL 截取字符串方法,中文算两个字符的方法。
--方法跟水哥学习的,支持一下水哥的博客
--http://hi.csdn.net/link.php?url=http://blog.csdn.net%2Fwufeng4552
--水哥最近谢了好多博客,有时间一定去好好学习
--LEFT() 和SUBSTRING()都是将汉字算一个字符来算的
--如果不计较的话,推荐使用这两个系统函数
IF OBJE ......
一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值,CHAR() 返回NULL 。
3、LOWER()和 ......