1.查询至少生产两种不同的计算机(PC或便携式电脑)且机器速度至少为133的厂商
AC:
select distinct(pd.maker)
--去重查询
from product pd
where pd.type in ('个人电脑', '便携式电脑')
--题目上要求的，至少一个，in是从里面选择
and
--这里也是model其实相当于id了，选择满足条件的pd.model，>133
pd.model in(
select model
from pc
where speed>=133
--选择速度至少为133的厂商，分别从个人电脑和便捷电脑里面选
union
--这个是连接查询的数据，别加all可以去重
select model
from laptop
where speed>=133
)
group by pd.maker
--分一下组，方便下面having计算
having count(distinct pd.model)>=2
--过滤分组后的结果，确保每个制造商至少有两个不同的符合条件的模型，就是题目里的（1）（2）（3）
order by pd.maker;
SELECT pd.maker
FROM product pd
WHERE pd.type IN ('个人电脑', '便携式电脑')
AND pd.model IN (
SELECT model
FROM pc
WHERE speed >= 133
UNION
SELECT model
FROM laptop
WHERE speed >= 133
)
GROUP BY pd.maker
HAVING COUNT(DISTINCT pd.model) >= 2
ORDER BY pd.maker;
2.
AC:
SELECT user_id
FROM (
SELECT user_id,
DATE(log_time) AS login_date,
@row_number := IF(@prev_user = user_id AND DATE(log_time) = DATE_SUB(@prev_date, INTERVAL 1 DAY), @row_number + 1, 1) AS rn,
@prev_user := user_id,
@prev_date := DATE(log_time)
FROM login_tb, (SELECT @row_number := 0, @prev_user := NULL, @prev_date := NULL) AS vars
WHERE user_id IN (
SELECT user_id
FROM register_tb
WHERE reg_time LIKE '2022-02-08%'
)
ORDER BY user_id, login_date
) AS consecutive_logins
GROUP BY user_id
HAVING COUNT(rn) >= 3
ORDER BY user_id;
