1.Oracle数据库查询某一时间段内的数据
eg.查询T_TABLE_NAME
中过去一周的数据总和,时间字段为date
SELECT count(ID)
FROM T_TABLE_NAME
WHERE date
BETWEEN TRUNC(sysdate -7) and TRUNC(sysdate)
2.Oracle数据库查询/删除同一张表中的重复记录
(1)查找表中多余的重复记录,重复记录是根据单个字段来判断
eg.查询T_TABLE_NAME
中字段id
重复的记录
SELECT *
FROM
T_TABLE_NAME
GROUP BY
id
HAVING
count( id ) > 1
(2)查找表中多余的重复记录,重复记录是根据多个字段来判断
eg.查询T_TABLE_NAME
中字段id
和字段name
同时重复的记录,并根据id
进行降序排序
SELECT *
FROM
T_TABLE_NAME
WHERE
( T_TABLE_NAME.id, T_TABLE_NAME.name ) IN ( SELECT id, name T_TABLE_NAME GROUP BY id, name HAVING count( * ) > 1 )
ORDER BY id DESC
(3)查找表中多余的重复记录,重复记录是根据多个字段来判断,只留有rowid最小的记录
eg.查询T_TABLE_NAME
中字段id
和字段name
同时重复的记录,只留number
最小的记录
SELECT *
FROM
T_TABLE_NAME
WHERE
( T_TABLE_NAME.id, T_TABLE_NAME.name ) IN ( SELECT id, name FROM T_TABLE_NAME GROUP BY id, name HAVING count( * ) > 1 )
AND number NOT IN ( SELECT min( number ) FROM T_TABLE_NAME GROUP BY id, name HAVING count( * ) > 1 )
(4)删除表中多余的重复记录,重复记录是根据多个字段来判断,只留有rowid最小的记录
eg.删除T_TABLE_NAME
中字段id
和字段name
同时重复的记录,只留number
最小的记录
DELETE
FROM
T_TABLE_NAME
WHERE
( T_TABLE_NAME.id, T_TABLE_NAME.name ) IN ( SELECT id, name FROM T_TABLE_NAME GROUP BY id, name HAVING count( * ) > 1 )
AND number NOT IN ( SELECT min( number ) FROM T_TABLE_NAME GROUP BY id, name HAVING count( * ) > 1 )
3.Oracle数据库中查询不重复数据
eg.查询T_TABLE_NAME
中字段id
和字段name
同时均不重复的记录
SELECT DISTINCT id,name
FROM T_TABLE_NAME