常用SQL之日期格式化和查询重复数据
本文列举一些工作中常用的SQL,以提升工作效率。
1 日期格式化
使用 DATE_FORMAT(get_date, '%Y-%m-%d') 函数进行格式化。其中:get_date 是需要被格式化的字段,'%Y-%m-%d' 是格式化后的日期格式。例如:
select date_format('1997-10-04 22:23:00','%y %M %b %D %W %a %Y-%m-%d %H:%i:%s %r %T');
结果:97 October Oct 4th Saturday Sat 1997-10-04 22:23:00 10:23:00 PM 22:23:00
SELECT count(*) FROM house Where get_date like '2006%-07%';
实例:
SELECT date_format('1997-10-01 22:23:00','%Y-%m-%d');
运行结果:1997-10-01。
2 查询指定字段是否有重复记录
重复记录有两种解释,一是完全重复的记录,也即所有字段均重复的记录,二是部分字段重复的记录,比如姓名name字段重复,而其它字段不一定重复。下面先在MySQL数据库中准备测试数据。
DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
`course` varchar(20) DEFAULT NULL COMMENT '课程',
`score` int(4) DEFAULT NULL COMMENT '分数',
create_time datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 测试数据
insert into test1(name,course,score,create_time)
values
('Tom','英语',98,NOW()),
('Tom','语文',96,NOW()),
('Tom','数学',69,NOW()),
('张三','语文',80,NOW()),
('李四','语文',90,NOW()),
('王五','语文',93,NOW()),
('张三','数学',77,NOW()),
('李四','数学',68,NOW()),
('王五','数学',99,NOW()),
('张三','英语',90,NOW()),
('李四','英语',50,NOW()),
('王五','英语',89,NOW());
2.1 完全重复
这一类的数据比较容易查询,使用如下SQL就可以得到无重复记录的结果集。
select
distinct * from
tableName;
如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除:
l 创建表结构一致的临时表tmp;
l 使用distinct关键字查询tableName中的所有数据,并复制到tmp;
l 清空tableName表;
l 从tmp把数据复制回来;
l 删除临时表tmp。
下面处理第二种场景的数据。
2.2 某个字段重复
查询重复字段重复次数信息
SELECT `name`,count(id) sumCount
FROM test1
GROUP BY `name` HAVING sumCount > 1;
结果集如下图所示:
说明:先用GROUP BY 对 name 进行分组,同时使用COUNT(id)进行统计,再用HAVING来过滤大于1的,这样查找出来的就是重复的记录了。查询包含重复字段的完整记录
SELECT T.* FROM test1 T WHERE `name` in (
SELECT `name` FROM test1
GROUP BY `name` HAVING count(id) > 1
)
结果集如下图所示:
查询包含重复字段的完整记录,并且,排除id最小的记录
SELECT * from test1 where
id not in (select id from (select min(id) as id from test1 group by `name`) b);
结果集如下图所示:
从结果集可知,已经排除姓名重复时,id最小的记录。删除表中多余的重复记录,重复记录是根据单个字段(name)来判断,只保留id最小的记录。
delete from test1 where
id not in (select id from (select min(id) as id from test1 group by `name`) b);
执行后,查询表中结果集如下,可以看到,name列已经不存在重复数据。结果集如下图所示:
如果删除脚本如下书写,则会抛异常,提示【[Err] 1093 - You can't specify target table 'test1' for update in FROM clause】。
delete from test1 where
id not in (select min(id) from test1 group by `name`);
在MySQL中,不允许先select一张表,再在同一个SQL语句中按此条件进行更新和删除同一张表的记录。解决办法就是将select得到的结果,再通过中间表select一遍,这样就规避了MySQL的限制。这个问题只出现于MySQL,ms sql和oracle不会出现此问题。
3 查找多个字段是否有重复记录
更新张三的数学成绩为80,使得和语文成绩一样。现在查询姓名和成绩都相同的记录,SQL如下:
select * from test1 a
where (a.`name`,a.score) in ( select `name`,score from test1
group by `name`,score having count(*) > 1 );
执行后,结果集如下图所示:
查找表中多个字段重复的记录,但不包含id最小的记录:
select * FROM test1 a
where (a.`name`,a.score) in ( select `name`,score FROM test1
group by `name`,score having count(*) > 1 )
AND a.id not in (
select min(id) FROM test1
group by `name`,score having count(*) > 1
);
删除表中多个字段重复的记录,并且包含id最小的记录
DELETE FROM test1
where (`name`,score) in ( select `name`,score FROM
(SELECT `name`,score FROM test1
group by `name`,score having count(*) > 1 ) b)
AND id not in ( SELECT id FROM
(select min(id) as id FROM test1
group by `name`,score having count(*) > 1
)c);
相关的主要查询SQL如下:
select * from test1 a where (a.`name`,a.score) in ( select `name`,score from test1 group by `name`,score having count(*) > 1 );
![](https://common.cnblogs.com/images/wechat.png)