msyql去重分组统计

//去重分组统计

SELECT *,uid, count(distinct uid) as total FROM `payment` group by product_id

SELECT product_id, count(distinct uid) as total FROM `payment` group by product_id

//去除重复记录

SELECT id,COUNT(*) FROM table GROUP BY id  HAVING COUNT(*) > 1;

//去重查出某字段最大或最小的所有记录

SELECT uid,id FROM payment a WHERE NOT EXISTS (SELECT 1 FROM payment where a.uid = uid AND a.id > id) 

//mysql 统计

SELECT 
sum(case when gift_score > 5000  then 1 else 0 end) as 'tota5',
sum(case when gift_score > 10000  then 1 else 0 end) as 'total0',
sum(case when gift_score > 20000 then 1 else 0 end) as 'tota20',
sum(case when gift_score > 25000 then 1 else 0 end) as 'tota25',
sum(case when gift_score > 30000 then 1 else 0 end) as 'tota30'

FROM `usergameinfo`

SELECT 
p1.uid as '用户',
count(*) as '总购物次数',
p2.total as '购买商品品种数',
p2.first_buy_time as '首次购买'
FROM payment as p1
LEFT JOIN (
SELECT 
t1.uid, 
count(distinct t1.product_id) as total,
min(t1.create_time) as 'first_buy_time'
FROM payment AS t1
GROUP BY t1.uid
) as p2
ON p2.uid = p1.uid
GROUP BY p1.uid

//mysql 判读

SELECT 
`NAME`, AGE, SEX ,
case when AGE < 17 then '小孩' 
when AGE > 22 then '帅哥'
else '学生'
end as 'val'
FROM `student`