當業務邏輯越來越復雜時,這個查詢的需求會越來越多,今天寫成筆記記錄下來,防止再忘記
SELECT * FROM `notice` a where add_time > 1513008000 and admin_id >= 1 and admin_id <= 10 and status = 0 and
3 > (SELECT COUNT(1) FROM `notice` where add_time > 1513008000 and admin_id >= 1 and admin_id <= 10 and status = 0 and admin_id = a.admin_id and add_time > a.add_time )
ORDER BY admin_id
mysql分組取取每組第一。虛擬一個業務,查詢通知表,時間大于今天,管理員id在1到10之間,通知狀態為0,重點是根據管理員分組,取每個分組的時間最大的3條
?
注:主查詢和子查詢的其他條件需保持一致,子查詢的分組依據字段(admin_id)需相等,再根據排序依據字段(add_time)做大于判斷