1. 查询2017年购买过的用户在2018年每月的购买单量
order 表字段:user_id, order_date, order_id
select user_id, month(order_date), count(order_id)
from order
where order_date like "2018%"
and user_id in (
select user_id
from order
where order_date like "2017%")
group by user_id, month(order_date)
2. 查找物流= 2, 服务=3,质量=5的评价id,表如下:
用户id | 评价id | 评价类型 | 评价星级 |
10095 | 23 | 物流 | 5 |
10095 | 23 | 服务 | 5 |
10095 | 23 | 质量 | 4 |
10096 | 24 | 物流 | 4 |
10096 | 24 | 服务 | 5 |
10095 | 24 | 质量 | 5 |
解答:
select 评价id
from table
group by 评价id
having sum((评价类别 = "物流")*评价星级) = 2
and sum((评价类别 = "服务")*评价星级) = 3
and sum((评价类别 = "质量")*评价星级) = 5;