HiSEN

MySQL一些简单的语句

发表于

emlog_ad字段
id
status
position
title
weight
content

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--找出重复
SELECT a.*
FROM emlog_ad a
WHERE a.id IN
    (SELECT b.id id
     FROM emlog_ad b
     GROUP BY b.title
     HAVING count(b.id)>1);

--删除重复留下id最小的
SELECT a.*
FROM emlog_ad a
WHERE a.id IN
    (SELECT b.id
     FROM emlog_ad b
     GROUP BY b.title
     HAVING count(b.id)>1)
  AND a.id NOT IN
    (SELECT min(c.id)
     FROM emlog_ad c
     GROUP BY c.title
     HAVING count(c.id)>1);

--一句sql把所有AA改为BB，CC改为DD
UPDATE emlog_ad a
SET a.`status`=(
CASE
	WHEN a.`status` = '1' THEN '11'
	WHEN a.`status`='2' THEN '22'
	END
);