pta题目

原创已于 2024-11-19 16:34:53 修改 · 243 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2024-11-03 22:12:19 首次发布

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:

@row_number：这是一个用户定义的变量，用来标记每个用户连续登录的天数。初始化为 0。
@prev_user 和 @prev_date：这两个变量用于存储上一次查询的 user_id 和 log_time 的日期。用来判断当前的登录是否是连续的。

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;

AC2：（连续三天，所有并连了两个表，如果是4天，7天什么的，那这个方法还是不适用的）


select a.user_id from login_tb as a 
inner join 
login_tb as b on a.user_id = b.user_id and date(a.log_time)=date(b.log_time)-1 
inner join login_tb as c on b.user_id = c.user_id and date(b.log_time)= date(c.log_time) -1 
where a.user_id in (select user_id from register_tb) 
order by a.user_id;

3. SQL255 给出employees表中排名为奇数行的first_name

AC:

select e1.first_name
from employees as e1
where 
(
    select count(*)
    from employees as e2
    where e1.first_name>=e2.first_name
)%2!=0

4.计算用户的平均次日留存率

描述
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

AC：

select 
count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1
left join question_practice_detail as q2
     on q1.device_id=q2.device_id
and date_add(q1.date,interval 1 day)=q2.date

dete_add:interal 加后面跟时间，这里是一天

5.统计salary的累计和running_total

描述
按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。具体结果如下Demo展示。。
CREATE TABLE salaries ( emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
输出格式:

示例：

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');

AC:

select sa1.emp_no,max(sa1.salary) as salary,sum(sa2.salary) as running_total
from salaries as sa1
join salaries as sa2
on sa1.emp_no>=sa2.emp_no and sa1.to_date='9999-01-01' and sa2.to_date='9999-01-01'
group by sa1.emp_no
order by sa1.emp_no;

6.部门工资前三高的所有员工

AC：

SELECT
    d.NAME AS Department,
    e.NAME AS Employee,
    e.salary AS Salary
FROM 
    Employee e
LEFT JOIN Department d ON e.departmentId = d.id
WHERE e.id IN (
    SELECT 
        e1.id
    FROM 
        Employee e1
    JOIN Employee e2 ON e1.departmentId= e2.departmentId
    WHERE e1.salary <= e2.salary
    GROUP BY e1.id
    HAVING COUNT(DISTINCT e2.salary) <= 3
);

7.换座位

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序返回结果表。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：

Create table If Not Exists Seat (id int, student varchar(255));

AC:

SELECT
    (CASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
          WHEN MOD(id, 2) != 0 AND counts = id THEN id
     ELSE id - 1
     END) AS id,
    student
FROM
    Seat,
    (SELECT COUNT(*) AS counts
       FROM Seat) AS seat_counts
ORDER BY id ASC;

8.界面推荐

朋友关系列表： Friendship

喜欢列表： Likes

编写解决方案，向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

以 任意顺序 返回结果，其中不应当包含重复项。

返回结果的格式如下例所示。

输入：
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 1        | 3        |
| 1        | 4        |
| 2        | 3        |
| 2        | 4        |
| 2        | 5        |
| 6        | 1        |
+----------+----------+
 
Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1       | 88      |
| 2       | 23      |
| 3       | 24      |
| 4       | 56      |
| 5       | 11      |
| 6       | 33      |
| 2       | 77      |
| 3       | 77      |
| 6       | 88      |
+---------+---------+

输出：
+------------------+
| recommended_page |
+------------------+
| 23               |
| 24               |
| 56               |
| 33               |
| 77               |
+------------------+
解释：
用户1 同 用户2, 3, 4, 6 是朋友关系。
推荐页面为： 页面23 来自于 用户2, 页面24 来自于 用户3, 页面56 来自于 用户3 以及 页面33 来自于 用户6。
页面77 同时被 用户2 和 用户3 推荐。
页面88 没有被推荐，因为 用户1 已经喜欢了它。

AC:

 
SELECT DISTINCT l.page_id AS recommended_page
FROM Likes l
JOIN Friendship f ON (l.user_id = f.user1_id OR l.user_id = f.user2_id)
WHERE (f.user1_id = 1 OR f.user2_id = 1)  -- 朋友关系
  AND l.user_id != 1  -- 排除用户1自己
  AND l.page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 1)  -- 排除用户1已喜欢的页面