mysql记录 - 快车库

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

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有一个记录

delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and min(id) not in (select id from people group by peopleId having count(peopleId )>1)

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

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

6.mysql delete删除关联多表数据

DELETE reviews,reviews_description FROM reviews LEFT JOIN reviews_description ON reviews.reviews_id=reviews_description.reviews_id WHERE reviews.status='0'

7.mysql delete in操作

delete from sns_hits where id not in （select id from another_table）
delete from sns_hits where id not in (select id from sns_hits)# error
CREATE TEMPORARY TABLE tmp_sns_hits ( `id` BIGINT(20) )
DELETE   FROM t_tag
WHERE id NOT IN (SELECT * FROM (SELECT MAX(t .id) AS id FROM t_tag t GROUP BY tagname)t)

8.一对多查询
select ID from 表2 where (select CONCAT(',', ksort , ',')AS ksort from 表1 ) LIKE CONCAT('%,', ID , ',%');
from Mysql删除重复记录
9.group by先排序后分组，group by默认的是第一条记录，相同的iID的记录不会先进行排序再group by
select from (select from t where 你的查询条件 order by 你的排序字段) group by 你的分组字段
10.mysql case when
SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND ( STATUS = 'Sold'

  OR STATUS = 'In Stock'
  OR STATUS = 'Ref')

AND CASE STATUS

     WHEN 'Sold' 
     THEN delivery_date >= '2012-08-01'
     ELSE 1=1
END

SELECT *
FROM logs
WHERE pw='correct'
AND CASE

      WHEN id<800 THEN success=1
      ELSE 1=1
  END

AND YEAR(TIMESTAMP)=2011
WHERE
pw='correct'
AND (id>=800 OR success=1)
AND YEAR(timestamp)=2011

WHERE
pw='correct'
AND CASE WHEN id<800 THEN success=1 ELSE TRUE END
AND YEAR(timestamp)=2011
11.mysql where case when

--简单Case函数 
CASE sex 
         WHEN '1' THEN '男' 
         WHEN '2' THEN '女' 
ELSE '其他' END 
--Case搜索函数 
CASE WHEN sex = '1' THEN '男' 
         WHEN sex = '2' THEN '女' 
ELSE '其他' END

mysql 排序去重 sql 写法

| data_id | user_id | data_name |hits 
| 1 | 2 | test1 | 140 
| 2 | 2 | test2 | 200 
| 3 | 3 | test2 | 110 
| 4 | 3 | test2 | 10 
| 5 | 1 | test2 | 130 
| 6 | 4 | test2 | 10 
| 7 | 4 | test2 | 100 

查询出来下面的 3 条结果，也就是前 3 条点击量最多的数据， user_id 不能重复
SELECT
    t.*
FROM
    (
        SELECT
            user_id,
            max(hits) AS max_hits
        FROM
            t
        GROUP BY
            user_id
    ) t2
LEFT JOIN t ON t.user_id = t2.user_id
AND t.hits = t2.max_hits
ORDER BY
    t2.max_hits DESC
LIMIT 3

| data_id | user_id | data_name |hits 
| 2 | 2 | test2 | 200 
| 5 | 1 | test2 | 130 
| 3 | 3 | test2 | 110

order by 出现数据错误问题
select stock.*, stock1.price_current as price_current1, stock1.rank as rank1, stock.price_current - stock1.price_current as percent1 from stock inner join stock as stock1 on stock.code = stock1.code and stock.date = '2016-10-10' and stock1.date = '2016-09-30' and stock.code = '600817' order by percent1 descG

price_current 这个字段设置了 UNSIGNED ，用 order by 会生成临时表，而临时表也就会同样是 UNSIGNED ，这就导致所有percent1为负值的都变成 0.00 了

MYsql 去重复语句

delete from xxx where id in (select * from (SELECT min(id) FROM xxx group by mail having count(mail)>1) as a);

create table tmp_xxx select min(id) as id,mail,password from xxx group by mail,password;

只保留一条不重复数据
DELETE T FROM MYTABLE T LEFT JOIN (SELECT MAX(A.ID) AS ID FROM MYTABLE A GROUP BY A.MAIL) TT ON T.ID = TT.ID WHERE TT.ID IS NULL
删除重复数据中的一条
DELETE T FROM MYTABLE T LEFT JOIN (SELECT MIN(A.ID) AS ID FROM MYTABLE A GROUP BY A.MAIL HAVING COUNT(A.MAIL) > 1) TT ON T.ID = TT.ID WHERE TT.ID IS NOT NULL
MySQL 唯一索引和插入重复自动更新

INSERT INTO table (id, user_id, token) VALUES (NULL, '2479031', '232') ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), token = VALUES(token), online = VALUES(online)
user_id 是唯一索引字段，如果 insert 的时候该 user_id 已经存在，那么就将触发更新而不是插入，此时相当于执行了
update table set user_id = 2479031 token = 232 where user_id = 2479031