mysql查询表里的重复数据

#这种方法只是统计了该字段重复对应的具体的个数

SELECT user_id,count(*)as count
FROM alipay_b_order
WHERE date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
and  bus_id ="7119"
AND state =1
GROUP BY user_id HAVING COUNT >1;

SELECT user_id,count(user_id) as count
FROM alipay_b_order
WHERE date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
and  bus_id ="7119"
AND state =1
GROUP BY user_id HAVING count >1
ORDER BY count DESC;

image.png

#多表查数据

select a.user_id, spec_name,order_id,origin_pic_name,final_pic_name,print_pic_name from (SELECT user_id,spec_name,order_id,origin_pic_name,final_pic_name,print_pic_name
FROM alipay_b_order
WHERE date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
and  bus_id ="7119"
AND state =1)as a INNER JOIN (SELECT user_id,count(*)as count
FROM alipay_b_order
WHERE date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
and  bus_id ="7119"
AND state =1
GROUP BY user_id HAVING COUNT >1) as b on a.user_id =b.user_id ORDER BY a.user_id desc;

select * from alipay_b_order a
where user_id in (
SELECT user_id
FROM alipay_b_order
WHERE date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
and  bus_id ="7119"
AND state =1
GROUP BY user_id HAVING count(user_id) >1)
AND state =1
and  bus_id ="7119"
and date(FROM_UNIXTIME(payment_time)) >= '2019-05-14' AND date(FROM_UNIXTIME(payment_time)) <='2019-5-14'
order by user_id DESC;

image.png