【MySql】举几个栗子

之前问到的一个面试题，做个记录

屏幕快照 2019-03-26 下午1.01.59.png

create table test1(
  id int unsigned primary key auto_increment,
  flag varchar(10) DEFAULT NULL,
  status varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (1, 'A', 'EBL');
INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (2, 'A', 'EBL');
INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (3, 'A', 'DEL');
INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (4, 'B', 'DEL');
INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (5, 'B', 'DEL');
INSERT INTO `test1`(`id`, `flag`, `status`) VALUES (6, 'B', 'EBL');

查找表中多余的重复记录，重复记录是根据单个字段（flag）来判断

SELECT * FROM test1 WHERE flag  IN (
    SELECT flag FROM test1 GROUP BY flag HAVING count(flag) > 1
)

查找表中多余的重复记录（多个字段,flag、status）

SELECT * FROM test1 WHERE (flag, status) IN (
    SELECT flag, status FROM test1 GROUP BY flag, status HAVING COUNT(*) >1
)

删除表中多余的重复记录，重复记录是根据单个字段（flag）来判断，只留有id最小的记录

DELETE FROM test1 WHERE flag IN (
    SELECT flag FROM test1 GROUP BY flag HAVING count(flag) > 1
) AND id NOT IN (
    SELECT min(id) FROM test1 GROUP BY flag HAVING count(flag) > 1
)

删除表中多余的重复记录（多个字段,flag、status），只留有id最小的记录

DELETE FROM test1 WHERE (flag, status) IN (
    SELECT flag, status FROM test1 GROUP BY flag, status HAVING count(*) > 1
) AND id NOT IN (
    SELECT min(id) FROM test1 GROUP BY flag, status HAVING count(*) > 1
)

统计A和B各自status出现的次数

屏幕快照 2019-03-26 下午1.13.35.png

SELECT 
flag,
SUM(CASE status WHEN 'EBL' THEN 1 ELSE 0 END) AS EBL,
SUM(CASE status WHEN 'DEL' THEN 1 ELSE 0 END) AS DEL
FROM test1 GROUP BY flag

关于COUNT函数

create table tb_test3(
  id int unsigned primary key auto_increment,
  one varchar(10) NOT NULL,
  two varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入数据：
insert into tb_test3(one,two) values (1,NULL);
insert into tb_test3(one,two) values ('',2);
insert into tb_test3(one,two) values (3,3);

#使用COUNT函数统计one字段:
select count(one) from tb_test3;   #结果为: 3 条， 说明 空字符串('') 会被count()函数统计!
#使用COUNT函数统计two字段:
select count(two) from tb_test3;   #结果为: 2条,  原因是NULL 不会被count()函数统计到!

#注意: 使用 * 号来统计会把NULL算进去!
SELECT count(*) FROM tb_test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+