CREATE table ts
(
id int(11) not null UNIQUE,
pcode int(11) default NULL,
cno varchar(20) default null,
count1 int(11) default null
)
INSERT INTO `ts` VALUES ('1', '1', ' 001', '10000');
INSERT INTO `ts` VALUES ('2', '1', ' 002', '5000');
INSERT INTO `ts` VALUES ('3', '1', ' 003', '20000');
INSERT INTO `ts` VALUES ('4', '2', ' 001', '40000');
INSERT INTO `ts` VALUES ('5', '2', ' 003', '30000');
INSERT INTO `ts` VALUES ('6', '3', ' 002', '90000');
INSERT INTO `ts` VALUES ('7', '3', ' 002', '90000');
INSERT INTO `ts` VALUES ('8', '3', ' 002', '90000');
查询pcode字段相同2条以上的记录:
select *
from ts
group by pcode
having count(*)>2
查询pcode字段相同的记录:
select *
from ts
where pcode in
(select pcode
from ts
group by pcode
having count(*)>1)
查询pcode,cno,count1三个字段均相同的记录:
select *
from ts
where concat(pcode,cno,count1) in
(
select concat(pcode,cno,count1)
from ts
group by pcode,cno,count1
having count(1) >= 2
)
删除多余的pcode字段相同的记录
SELECT *
from ts
where pcode in
(
select pcode
from ts
group by pcode
having count(*)>1
) and id not in #查出所有相同的记录排除第一条,将全部删除掉
(
select min(id)
from ts
group by pcode
having count(*) >= 2
);
删除多余的pcode,cno,count1三个字段均相同的记录:
SELECT *
from ts
where concat(pcode,cno,count1) in
(
select concat(pcode,cno,count1)
from ts
group by pcode,cno,count1
having count(1) >= 2
) and id not in
(
select min(id)
from ts
group by pcode,cno,count1
having count(1) >= 2
);
在此注意:多字段的时候该语句仅适用于字段值均不为空的情况,concat函数中的参数有一个为空便会返回空值。
1.任何情况下Select COUNT(*) FROM xxx 是最优选择;
2.尽量减少Select COUNT(*) FROM xxx Where COL = ‘xxx’ 这种查询;
3.杜绝Select COUNT(COL) FROM tablename Where COL = ‘xxx’ 的出现。(其中COL非主键)
(1)count(*)是对行数目进行计数
(2)count(column_name)是对列中不为空的行进行计数,