mysql-常用sql

記錄下工作中常用的sql

刪除重復數據

delete from student where id not in (select min(id) from student group by name); 
-- 該語句在mysql下會報錯，
-- 執行報錯：1093 - You can't specify target table 'student' for update in FROM clause
-- 原因是：更新數據時使用了查詢，而查詢的數據又做了更新的條件，mysql不支持這種方式。外邊在包一層即可delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);

查找全部重復記錄

SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1)  -- 重復標題

每組的前N條數據

-- 對上面數據按照日期分組，每組取qty_ordered最大的前5條SELECT  * FROM 表名 a  WHERE (SELECT count(*) FROM 表名 b WHERE b.日期=a.日期 AND b.id>a.id  )<5;

分組的，某個字段最大

select a.* from table2 as a where age = (select max(age) from table2 where a.table1_id=table1_id);

條件總數

select 
COUNT(1),
COUNT(IF(source = 2 OR source =3 ,TRUE,NULL)) AS zq,
COUNT(IF(source =1,TRUE,NULL))AS jdcount
from table

其他

SELECT SEC_TO_TIME (89    53);select md5();select day(now());DATE_FORMAT( deteline, "%Y-%m-%d %H" );CONVERT(FROM_BASE64(content),CHAR);weight + 120 - FLOOR(TIMESTAMPDIFF(DAY,create_time,NOW())/30)*10