首页 > 试题广场 >

某App用login_table表记录了每日登录数据： +-

[单选题]

某App用login_table表记录了每日登录数据：

+----+---------+------------+
| id | user_id | date |
+----+---------+------------+
| 1 | 1001 | 2023-03-01 |
| 2 | 1002 | 2023-03-01 |
| 3 | 1002 | 2023-03-02 |
| 4 | 1002 | 2023-03-03 |
| 5 | 1003 | 2023-03-03 |
| 6 | 1004 | 2023-03-03 |
| 7 | 1004 | 2023-03-04 |
| 8 | 1002 | 2023-03-04 |
| 9 | 1001 | 2023-03-04 |
| 10 | 1001 | 2023-03-05 |
| 11 | 1002 | 2023-03-05 |
| 12 | 1005 | 2023-03-05 |
+----+---------+------------+

统计每日新增用户数，比如3.3号新增2位(1003和1004)，3.5号新增1位(1005)，以下MySQL查询语句错误的是（）？

SELECT date, COUNT(*)
FROM (
    SELECT *
    FROM (
        SELECT user_id, date, row_number() OVER (PARTITION BY user_id ORDER BY date ASC) AS rn
        FROM login_table
    ) t1
    WHERE rn = 1
) t2
GROUP BY date
ORDER BY date;

SELECT date, COUNT(*)
FROM (
    SELECT user_id, date, row_number() OVER (PARTITION BY user_id ORDER BY date ASC) AS rn
    FROM login_table
) t1
WHERE rn = 1
GROUP BY date
ORDER BY date;

SELECT date, COUNT(*)
FROM (
    SELECT user_id, date, row_number() OVER (PARTITION BY user_id ORDER BY date ASC) AS rn
    FROM login_table
    WHERE rn = 1
) t2
GROUP BY date
ORDER BY date;

SELECT date, COUNT(*)
FROM (
    SELECT t1.user_id, t1.date
    FROM (
        SELECT user_id, date, row_number() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
        FROM login_table
    ) t1
        JOIN (
            SELECT user_id, MAX(rn) AS rn
            FROM (
                SELECT user_id, date, row_number() OVER (PARTITION BY user_id ORDER BY date DESC) AS rn
                FROM login_table
            ) t1
            GROUP BY user_id
        ) t2
        ON t1.user_id = t2.user_id
            AND t1.rn = t2.rn
) t3
GROUP BY date
ORDER BY date;

查看正确选项