1.隨機(jī)獲取表中100條數(shù)據(jù)
select * from table order by rand() limit 100;

2.統(tǒng)計(jì)表中每10分鐘的pv量
select floor(unix_timestamp(window_start)/600),sum(pv_num) from table
where window_start <= date_add(now(),interval -20 minute) and
window_start >= date_add(now(),interval -30 minute)
group by floor(unix_timestamp(window_start)/600)

select FROM_UNIXTIME(floor(unix_timestamp(record_time)/600)600,'%Y-%m-%d %H:%i:%s'),count(user_no) rk from table
group by FROM_UNIXTIME(floor(unix_timestamp(record_time)/600)600,'%Y-%m-%d %H:%i:%s')
having rk > 1

3.計(jì)算上下兩行相減
select *,LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE) Next_Date From table

SELECT *,CASE WHEN LAG(CALLBACK_DATE) OVER (PARTITION BY UID ORDER BY CALLBACK_DATE) IS NULL
OR
DATEDIFF(DAY,LAG(CALLBACK_DATE) OVER
(PARTITION BY UID ORDER BY CALLBACK_DATE),CALLBACK_DATE) < 7
THEN '不計(jì)費(fèi)'
ELSE '計(jì)費(fèi)' END AS CHARGE
FORM table
ORDER BY UID,CALLBACK_DATE

4.count開窗函數(shù)
select t.Docnum
from (
select * ,count(1) over (partition by Docnum) AS RN
from table
) t
where t.status = 'FULL'
and t.RN = 1

5.mysql5.6實(shí)現(xiàn)分組，排序獲取第一條數(shù)據(jù)
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)