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 modelfrom pcwhere speed>=133--选择速度至少为133的厂商，分别从个人电脑和便捷电脑里面选union--这个是连接查询的数据，别加all可以去重select modelfrom laptopwhere 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 modelFROM pcWHERE speed >= 133UNIONSELECT modelFROM laptopWHERE 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 varsWHERE 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;
