# 牛客-sql大厂笔试真题（40-120）

### **SQL42** **分析客户逾期情况**

#### 描述

有贷款信息表：**loan\_tb**（agreement\_id：合同id，customer\_id：客户id，loan\_amount：贷款金额，pay\_amount：已还金额，overdue\_days：逾期天数）

| agreement\_id | customer\_id | loan\_amount | pay\_amount | overdue\_days |
| ------------- | ------------ | ------------ | ----------- | ------------- |
| 10111         | 1111         | 20000        | 18000       | NULL          |
| 10112         | 1112         | 10000        | 10000       | NULL          |
| 10113         | 1113         | 15000        | 10000       | 38            |
| 10114         | 1114         | 50000        | 30000       | NULL          |
| 10115         | 1115         | 60000        | 50000       | NULL          |
| 10116         | 1116         | 10000        | 8000        | NULL          |
| 10117         | 1117         | 50000        | 50000       | NULL          |
| 10118         | 1118         | 25000        | 10000       | 5             |
| 10119         | 1119         | 20000        | 1000        | 106           |

客户信息表：**customer\_tb**（customer\_id：客户id，customer\_age：客户年龄，pay\_ability：还款能力级别）

| customer\_id | customer\_age | pay\_ability |
| ------------ | ------------- | ------------ |
| 1111         | 28            | B            |
| 1112         | 38            | A            |
| 1113         | 20            | C            |
| 1114         | 30            | A            |
| 1115         | 29            | B            |
| 1116         | 21            | C            |
| 1117         | 35            | B            |
| 1118         | 36            | B            |
| 1119         | 25            | C            |

请根据以上数据分析各还款能力级别的客户逾期情况，按照还款能力级别统计有逾期行为客户占比。要求输出还款能力级别、逾期客户占比。

注：逾期客户占比要求按照百分数形式输出并四舍五入保留 1 位小数，最终结果按照占比降序排序。

示例数据结果如下：

| pay\_ability | overdue\_ratio |
| ------------ | -------------- |
| C            | 66.7%          |
| B            | 25.0%          |
| A            | 0.0%           |

结果解释：

还款能力级别为 C 的客户有1113、1116、1119，其中有逾期行为的客户为 1113、1119，故结果为 2/3=66.7%；其他结果同理。

#### 示例1

输入：

```sql
drop table if exists  `loan_tb` ; 
CREATE TABLE `loan_tb` (
`agreement_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`loan_amount` int(11) NOT NULL,
`pay_amount` int(11) NOT NULL,
`overdue_days` int(11),
PRIMARY KEY (`agreement_id`));
INSERT INTO loan_tb VALUES(10111,1111,20000,18000,null); 
INSERT INTO loan_tb VALUES(10112,1112,10000,10000,null); 
INSERT INTO loan_tb VALUES(10113,1113,15000,10000,38); 
INSERT INTO loan_tb VALUES(10114,1114,50000,30000,null); 
INSERT INTO loan_tb VALUES(10115,1115,60000,50000,null); 
INSERT INTO loan_tb VALUES(10116,1116,10000,8000,null); 
INSERT INTO loan_tb VALUES(10117,1117,50000,50000,null); 
INSERT INTO loan_tb VALUES(10118,1118,25000,10000,5); 
INSERT INTO loan_tb VALUES(10119,1119,20000,1000,106); 

drop table if exists  `customer_tb` ; 
CREATE TABLE `customer_tb` (
`customer_id` int(11) NOT NULL,
`customer_age` int(11) NOT NULL,
`pay_ability` varchar(2) NOT NULL,
PRIMARY KEY (`customer_id`));
INSERT INTO customer_tb VALUES(1111,28,'B'); 
INSERT INTO customer_tb VALUES(1112,38,'A'); 
INSERT INTO customer_tb VALUES(1113,20,'C'); 
INSERT INTO customer_tb VALUES(1114,30,'A'); 
INSERT INTO customer_tb VALUES(1115,29,'B'); 
INSERT INTO customer_tb VALUES(1116,21,'C'); 
INSERT INTO customer_tb VALUES(1117,35,'B'); 
INSERT INTO customer_tb VALUES(1118,36,'B'); 
INSERT INTO customer_tb VALUES(1119,25,'C'); 
```

输出：

```
pay_ability|overdue_ratio
C|66.7%
B|25.0%
A|0.0%
```

#### 答案

```sql
SELECT ct.pay_ability,
       CONCAT(ROUND(SUM(CASE WHEN lt.overdue_days IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 1), '%') AS overdue_ratio
FROM loan_tb lt
         join customer_tb ct ON lt.customer_id = ct.customer_id
GROUP BY ct.pay_ability
ORDER BY overdue_ratio DESC
```

### 解析

**计算是否总人数**

```sql
SUM(CASE WHEN lt.overdue_days IS NOT NULL THEN 1 ELSE 0 END)
```

只要不是空就+1

**除法结果四舍五入保留一位小数**

```sql
ROUND(一个数,1)
```

**拼接字符串**

`CONCAT(...,'%')`：输出格式为‘数字’+‘%’，此处使用字符串操作，`CONCAT(A,B)`即将A字符串和B字符串连接成‘AB’字符串。

### **SQL45** **统计所有课程参加培训人次**

#### 描述

某公司员工培训信息数据如下：

员工培训信息表**cultivate\_tb**(info\_id-信息id,staff\_id-员工id,course-培训课程)，如下所示：

注：该公司共开设了三门课程，员工可自愿原则性培训0-3项，每项课程每人可培训1次。

| info\_id | staff\_id | course                  |
| -------- | --------- | ----------------------- |
| 101      | 1         | course1,course2         |
| 102      | 2         | course2                 |
| 103      | 3         | course1,course3         |
| 104      | 4         | course1,course2,course3 |
| 105      | 5         | course3                 |
| 106      | 6         | NULL                    |
| 107      | 7         | course1,course2         |

问题：请统计该公司所有课程参加培训人次？

示例数据结果如下：

| staff\_nums |
| ----------- |
| 11          |

解释：course1课程共有员工1、3、4、7共4名员工培训；

course2课程共有员工1、2、4、7共4名员工培训；

course3课程共有员工3、4、5共3名员工培训。

#### 示例1

输入：

```sql
drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `cultivate_tb` ;   
CREATE TABLE `cultivate_tb` (
`info_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`course` varchar(32) NULL,
PRIMARY KEY (`info_id`));
INSERT INTO cultivate_tb VALUES(101,1,'course1,course2');
INSERT INTO cultivate_tb VALUES(102,2,'course2');
INSERT INTO cultivate_tb VALUES(103,3,'course1,course3');
INSERT INTO cultivate_tb VALUES(104,4,'course1,course2,course3');
INSERT INTO cultivate_tb VALUES(105,5,'course3');
INSERT INTO cultivate_tb VALUES(106,6,NULL);
INSERT INTO cultivate_tb VALUES(107,7,'course1,course2');
```

输出：

```
staff_nums
11
```

#### 答案

```sql
select sum(num) AS staff_nums
from (
    select IF(course is null, 0, length(course) - length(replace(course, ',', '')) + 1) AS num
      from cultivate_tb) as sub

```

#### 解析

**替换字符串操作**

REPLACE(string, old\_substring, new\_substring)：

* `string`：原始字符串（可以是列名或字符串常量）。
* `old_substring`：要被替换的子字符串。
* `new_substring`：用来替换的新字符串。

  **计算字符串长度**

  使用 `LENGTH()` 函数，返回**字符数**（对于多字节字符集，如 UTF-8，一个字符可能占多个字节）。

  SELECT LENGTH('Hello'); -- 结果：5

  SELECT LENGTH('你好'); -- MySQL/PostgreSQL 中通常返回 2（字符数）

  SELECT LENGTH(''); -- 结果：0

  那么结合以上两个函数：length(course) - length(replace(course,',','')) 计算的其实就是，的个数

### **SQL46** **查询培训指定课程的员工信息**

简单 通过率：66.71% 时间限制：1秒 空间限制：256M

#### 描述

某公司员工信息数据及员工培训信息数据如下：

员工信息表**staff\_tb**(staff\_id-员工id，staff\_name-员工姓名，staff\_gender-员工性别，post-员工岗位类别，department-员工所在部门)，如下所示：

| staff\_id | staff\_name | staff\_gender | post      | department |
| --------- | ----------- | ------------- | --------- | ---------- |
| 1         | Angus       | male          | Financial | dep1       |
| 2         | Cathy       | female        | Director  | dep1       |
| 3         | Aldis       | female        | Director  | dep2       |
| 4         | Lawson      | male          | Engineer  | dep1       |
| 5         | Carl        | male          | Engineer  | dep2       |
| 6         | Ben         | male          | Engineer  | dep1       |
| 7         | Rose        | female        | Financial | dep2       |

员工培训信息表**cultivate\_tb**(info\_id-信息id，staff\_id-员工id，course-培训课程)，如下所示：

注：该公司共开设了三门课程，员工可自愿原则性培训0-3项；

| info\_id | staff\_id | course                    |
| -------- | --------- | ------------------------- |
| 101      | 1         | course1, course2          |
| 102      | 2         | course2                   |
| 103      | 3         | course1, course3          |
| 104      | 4         | course1, course2, course3 |
| 105      | 5         | course3                   |
| 106      | 6         | NULL                      |
| 107      | 7         | course1, course2          |

问题：请查询培训课程course3的员工信息？

注：只要培训的课程中包含course3课程就计入结果

要求输出：员工id、姓名，按照员工id升序排序； 示例数据结果如下：

| staff\_id | staff\_name |
| --------- | ----------- |
| 3         | Aldis       |
| 4         | Lawson      |
| 5         | Carl        |

解释：有员工3、4、5培训了course3课程，故结果如上

#### 示例1

```sql
drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `cultivate_tb` ;   
CREATE TABLE `cultivate_tb` (
`info_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`course` varchar(32) NULL,
PRIMARY KEY (`info_id`));
INSERT INTO cultivate_tb VALUES(101,1,'course1,course2');
INSERT INTO cultivate_tb VALUES(102,2,'course2');
INSERT INTO cultivate_tb VALUES(103,3,'course1,course3');
INSERT INTO cultivate_tb VALUES(104,4,'course1,course2,course3');
INSERT INTO cultivate_tb VALUES(105,5,'course3');
INSERT INTO cultivate_tb VALUES(106,6,NULL);
INSERT INTO cultivate_tb VALUES(107,7,'course1,course2');
```

#### 答案

```sql
select c.staff_id,
       s.staff_name
from cultivate_tb c
         left join staff_tb s on c.staff_id = s.staff_id
where c.course like '%course3%'
order by c.staff_id;
```

#### 解析

**常用正则表达式语法**

不同数据库支持的正则表达式语法略有差异，以下是常见模式：

* ^：匹配字符串开头。
* $：匹配字符串结尾。
* .：匹配任意单个字符。
* \*：匹配前面的字符 0 次或多次。
* +：匹配前面的字符 1 次或多次。
* \[]：匹配括号内的任意单个字符（如 \[a-z] 匹配小写字母）。
* |：表示“或”，匹配左右任一模式。
* \d：匹配数字（部分数据库需用 \[0-9]）。
* \w：匹配字母、数字或下划线（部分数据库需用 \[a-zA-Z0-9\_]）。

数据库中正则表达式的使用

**MySQL 正则表达式**

使用 REGEXP 运算符进行正则匹配。

```sql
SELECT * FROM table_name WHERE column_name REGEXP 'course[0-9]';
```

* 示例：查询字段 column\_name 中包含 "course" 后跟一个数字的记录。
* 常见模式：
  * ^course：匹配以 "course" 开头的字符串。
  * \[0-9]+：匹配一个或多个数字。
  * course$：匹配以 "course" 结尾的字符串。

### **SQL49** **统计各岗位员工平均工作时长**

#### 描述

某公司员工信息数据及单日出勤信息数据如下：

员工信息表**staff\_tb**(staff\_id-员工id,staff\_name-员工姓名,staff\_gender-员工性别,post-员工岗位类别,department-员工所在部门)，如下所示：

| staff\_id | staff\_name | staff\_gender | post      | department |
| --------- | ----------- | ------------- | --------- | ---------- |
| 1         | Angus       | male          | Financial | dep1       |
| 2         | Cathy       | female        | Director  | dep1       |
| 3         | Aldis       | female        | Director  | dep2       |
| 4         | Lawson      | male          | Engineer  | dep1       |
| 5         | Carl        | male          | Engineer  | dep2       |
| 6         | Ben         | male          | Engineer  | dep1       |
| 7         | Rose        | female        | Financial | dep2       |

出勤信息表**attendent\_tb**(info\_id-信息id,staff\_id-员工id,first\_clockin-上班打卡时间,last\_clockin-下班打卡时间)，如下所示：

| info\_id | staff\_id | first\_clockin      | last\_clockin       |
| -------- | --------- | ------------------- | ------------------- |
| 101      | 1         | 2022-03-22 08:00:00 | 2022-03-22 17:00:00 |
| 102      | 2         | 2022-03-22 08:30:00 | 2022-03-22 18:00:00 |
| 103      | 3         | 2022-03-22 08:45:00 | 2022-03-22 17:00:00 |
| 104      | 4         | 2022-03-22 09:00:00 | 2022-03-22 18:30:00 |
| 105      | 5         | 2022-03-22 09:00:00 | 2022-03-22 18:10:00 |
| 106      | 6         | 2022-03-22 09:15:00 | 2022-03-22 19:30:00 |
| 107      | 7         | 2022-03-22 09:30:00 | 2022-03-22 18:29:00 |

问题：请统计该公司各岗位员工平均工作时长？要求输出：员工岗位类别、平均工作时长（以小时为单位输出并保留三位小数），按照平均工作时长降序排序。

注：如员工未打卡该字段数据会存储为NULL，那么不计入在内。

示例数据结果如下：

| post      | work\_hours |
| --------- | ----------- |
| Engineer  | 9.639       |
| Financial | 8.992       |
| Director  | 8.875       |

解释：Engineer类岗位有4、5、6共计3名员工，工作时长分别为：9.500、9.167、10.250，则平均工作时长为 (9.500+9.167+10.250)/3=9.639小时。

其他结果同理.....

#### 示例1

输入：

```sql
drop table if exists  `staff_tb` ; 
CREATE TABLE `staff_tb` (
`staff_id` int(11) NOT NULL,
`staff_name` varchar(16) NOT NULL,
`staff_gender` char(8) NOT NULL,
`post` varchar(11) NOT NULL,
`department` varchar(16) NOT NULL,
PRIMARY KEY (`staff_id`));
INSERT INTO staff_tb VALUES(1,'Angus','male','Financial','dep1'); 
INSERT INTO staff_tb VALUES(2,'Cathy','female','Director','dep1'); 
INSERT INTO staff_tb VALUES(3,'Aldis','female','Director','dep2'); 
INSERT INTO staff_tb VALUES(4,'Lawson','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(5,'Carl','male','Engineer','dep2'); 
INSERT INTO staff_tb VALUES(6,'Ben','male','Engineer','dep1'); 
INSERT INTO staff_tb VALUES(7,'Rose','female','Financial','dep2'); 

drop table if exists  `attendent_tb` ;   
CREATE TABLE `attendent_tb` (
`info_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`first_clockin` datetime NULL,
`last_clockin` datetime NULL,
PRIMARY KEY (`info_id`));
INSERT INTO attendent_tb VALUES(101,1,'2022-03-22 08:00:00','2022-03-22 17:00:00');
INSERT INTO attendent_tb VALUES(102,2,'2022-03-22 08:30:00','2022-03-22 18:00:00');
INSERT INTO attendent_tb VALUES(103,3,'2022-03-22 08:45:00','2022-03-22 17:00:00');
INSERT INTO attendent_tb VALUES(104,4,'2022-03-22 09:00:00','2022-03-22 18:30:00');
INSERT INTO attendent_tb VALUES(105,5,'2022-03-22 09:00:00','2022-03-22 18:10:00');
INSERT INTO attendent_tb VALUES(106,6,'2022-03-22 09:15:00','2022-03-22 19:30:00');
INSERT INTO attendent_tb VALUES(107,7,'2022-03-22 09:30:00','2022-03-22 18:29:00');
```

输出：

```
post|work_hours
Engineer|9.639
Financial|8.992
Director|8.875
```

#### 答案

```sql
SELECT
    s.post,
    ROUND(AVG(TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60), 3) AS work_hours
FROM
    staff_tb s
JOIN
    attendent_tb a ON s.staff_id = a.staff_id
WHERE
    a.first_clockin IS NOT NULL
    AND a.last_clockin IS NOT NULL
GROUP BY
    s.post
ORDER BY
    work_hours DESC;

```

#### 解析

**时间函数**

计算时长

**`TIMESTAMPADD(unit, interval, datetime_expr)`**：加时间戳

**`TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)`**：两个时间差（返回整数）

```sql
TIMESTAMPDIFF(MINUTE, a.first_clockin, a.last_clockin) / 60
```

| 分类       | 函数                             | 说明          | 示例                                                             |
| -------- | ------------------------------ | ----------- | -------------------------------------------------------------- |
| **当前时间** | `NOW()`                        | 当前日期+时间     | `2025-09-05 10:30:25`                                          |
|          | `CURDATE()`                    | 当前日期        | `2025-09-05`                                                   |
|          | `CURTIME()`                    | 当前时间        | `10:30:25`                                                     |
|          | `UTC_TIMESTAMP()`              | 当前 UTC 时间   | `2025-09-05 02:30:25`                                          |
| **提取部分** | `YEAR(d)`                      | 年           | `YEAR('2025-09-05') → 2025`                                    |
|          | `MONTH(d)`                     | 月           | `MONTH('2025-09-05') → 9`                                      |
|          | `DAY(d)`                       | 日           | `DAY('2025-09-05') → 5`                                        |
|          | `HOUR(t)`                      | 小时          | `HOUR('10:30:25') → 10`                                        |
|          | `MINUTE(t)`                    | 分钟          | `MINUTE('10:30:25') → 30`                                      |
|          | `SECOND(t)`                    | 秒           | `SECOND('10:30:25') → 25`                                      |
|          | `DAYNAME(d)`                   | 星期名         | `DAYNAME('2025-09-05') → Friday`                               |
|          | `WEEKDAY(d)`                   | 星期几(0=周一)   | `WEEKDAY('2025-09-05') → 4`                                    |
|          | `QUARTER(d)`                   | 季度          | `QUARTER('2025-09-05') → 3`                                    |
| **时间运算** | `DATE_ADD(d, INTERVAL n unit)` | 日期加         | `DATE_ADD('2025-09-05', INTERVAL 7 DAY) → 2025-09-12`          |
|          | `DATE_SUB(d, INTERVAL n unit)` | 日期减         | `DATE_SUB('2025-09-05', INTERVAL 1 MONTH) → 2025-08-05`        |
|          | `TIMESTAMPADD(unit,n,dt)`      | 加时间戳        | `TIMESTAMPADD(HOUR, 5, NOW())`                                 |
|          | `TIMESTAMPDIFF(unit,dt1,dt2)`  | 时间差（整数）     | `TIMESTAMPDIFF(DAY, '2025-01-01', '2025-09-05') → 247`         |
| **时间差**  | `DATEDIFF(d1,d2)`              | 相差天数        | `DATEDIFF('2025-12-31','2025-09-05') → 117`                    |
|          | `TIMEDIFF(t1,t2)`              | 相差时分秒       | `TIMEDIFF('12:30:00','10:00:00') → 02:30:00`                   |
|          | `TO_DAYS(d)`                   | 日期转天数       | `TO_DAYS('2025-09-05') → 739521`                               |
|          | `FROM_DAYS(n)`                 | 天数转日期       | `FROM_DAYS(739521) → 2025-09-05`                               |
|          | `UNIX_TIMESTAMP(d)`            | 转为 Unix 时间戳 | `UNIX_TIMESTAMP('2025-09-05 10:30:00') → 1757068200`           |
|          | `FROM_UNIXTIME(ts)`            | Unix 时间戳转日期 | `FROM_UNIXTIME(1757068200) → 2025-09-05 10:30:00`              |
| **格式化**  | `DATE_FORMAT(d, fmt)`          | 格式化日期       | `DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') → 2025-09-05 10:30:25` |
|          | `TIME_FORMAT(t, fmt)`          | 格式化时间       | `TIME_FORMAT('10:30:25','%H:%i') → 10:30`                      |
|          | `STR_TO_DATE(str, fmt)`        | 字符串转日期      | `STR_TO_DATE('2025-09-05','%Y-%m-%d') → 2025-09-05`            |
| **时区**   | `CONVERT_TZ(dt,from_tz,to_tz)` | 时区转换        | `CONVERT_TZ(NOW(),'UTC','+08:00')`                             |

**聚合函数**

常见的聚合函数有：`COUNT`、`SUM`、`AVG`、`MIN`、`MAX`

**聚合函数 + GROUP BY**

聚合函数经常和 `GROUP BY` 搭配，按组计算。

```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
```

👉 结果：每个部门的平均工资。

**聚合函数 + HAVING**

`WHERE` 不能用聚合函数，但 `HAVING` 可以对分组后的结果过滤。

```
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;
```

👉 结果：筛选出平均工资大于 8000 的部门。

**聚合函数和 NULL 的关系**

* **`COUNT(\*)`** 会统计所有行（包括 NULL）。
* **`COUNT(col)`** 不统计 NULL。
* **`SUM` / `AVG` / `MIN` / `MAX`** 都会忽略 NULL 值。

### **SQL51** **统计商家不同会员每日访问人次及访问人数**

#### 描述

现有某商家用户访问数据及用户会员等级数据，如下所示：

用户访问信息表：visit\_tb（访问信息id-info\_id，用户id-user\_id，访问时间-visit\_time，离开时间-leave\_time）

| info\_id | user\_id | visit\_time         | leave\_time         |
| -------- | -------- | ------------------- | ------------------- |
| 911      | 10       | 2022-09-01 08:00:00 | 2022-09-01 09:02:00 |
| 912      | 11       | 2022-09-01 08:30:00 | 2022-09-01 09:10:00 |
| 913      | 12       | 2022-09-01 09:50:00 | 2022-09-01 10:12:00 |
| 914      | 13       | 2022-09-01 11:40:00 | 2022-09-01 12:22:00 |
| 921      | 11       | 2022-09-02 10:30:00 | 2022-09-02 11:05:00 |
| 922      | 11       | 2022-09-02 12:00:00 | 2022-09-02 12:02:00 |
| 923      | 12       | 2022-09-02 11:40:00 | 2022-09-02 13:15:00 |
| 924      | 13       | 2022-09-02 09:00:00 | 2022-09-02 09:02:00 |
| 925      | 14       | 2022-09-02 10:00:00 | 2022-09-02 10:40:00 |
| 931      | 10       | 2022-09-03 09:00:00 | 2022-09-03 09:22:00 |
| 932      | 11       | 2022-09-03 08:30:00 | 2022-09-03 09:10:00 |
| 933      | 13       | 2022-09-03 09:00:00 | 2022-09-03 09:32:00 |

会员等级信息表：uservip\_tb（用户id-user\_id，会员等级-vip，积分-point）

| user\_id | vip  | point |
| -------- | ---- | ----- |
| 10       | 银卡会员 | 530   |
| 11       | 银卡会员 | 1555  |
| 12       | 钻石会员 | 12000 |
| 13       | 金卡会员 | 6115  |
| 14       | 普通会员 | 230   |
| 15       | 银卡会员 | 810   |
| 16       | 普通会员 | 330   |

根据用户访问数据统计出不同会员等级访问人次及访问人数总和。 要求输出：会员等级、访问人次、访问人数 注：按照访问人次降序排序

示例数据结果如下：

| vip  | visit\_nums | visit\_users |
| ---- | ----------- | ------------ |
| 银卡会员 | 6           | 2            |
| 金卡会员 | 3           | 1            |
| 钻石会员 | 2           | 1            |
| 普通会员 | 1           | 1            |

结果解释：

钻石会员有user\_id为12的用户，该用户分别在9月1日、9月2日访问，故访问人次为2、访问人数为1；

其他结果同理。

#### 示例1

输入：

```sql
drop table if exists  `visit_tb` ; 
CREATE TABLE `visit_tb` (
`info_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`visit_time` datetime NOT NULL,
`leave_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO visit_tb VALUES(0911,10,'2022-09-01 08:00:00','2022-09-01 09:02:00'); 
INSERT INTO visit_tb VALUES(0912,11,'2022-09-01 08:30:00','2022-09-01 09:10:00'); 
INSERT INTO visit_tb VALUES(0913,12,'2022-09-01 09:50:00','2022-09-01 10:12:00'); 
INSERT INTO visit_tb VALUES(0914,13,'2022-09-01 11:40:00','2022-09-01 12:22:00'); 
INSERT INTO visit_tb VALUES(0921,11,'2022-09-02 10:30:00','2022-09-02 11:05:00'); 
INSERT INTO visit_tb VALUES(0922,11,'2022-09-02 12:00:00','2022-09-02 12:02:00'); 
INSERT INTO visit_tb VALUES(0923,12,'2022-09-02 11:40:00','2022-09-02 13:15:00'); 
INSERT INTO visit_tb VALUES(0924,13,'2022-09-02 09:00:00','2022-09-02 09:02:00'); 
INSERT INTO visit_tb VALUES(0925,14,'2022-09-02 10:00:00','2022-09-02 10:40:00'); 
INSERT INTO visit_tb VALUES(0931,10,'2022-09-03 09:00:00','2022-09-03 09:22:00'); 
INSERT INTO visit_tb VALUES(0932,11,'2022-09-03 08:30:00','2022-09-03 09:10:00'); 
INSERT INTO visit_tb VALUES(0933,13,'2022-09-03 09:00:00','2022-09-03 09:32:00'); 

drop table if exists  `uservip_tb` ; 
CREATE TABLE `uservip_tb` (
`user_id` int(11) NOT NULL,
`vip` varchar(16) NOT NULL,
`point` int(11) NOT NULL,
PRIMARY KEY (`user_id`));
INSERT INTO uservip_tb VALUES(10,'银卡会员',530); 
INSERT INTO uservip_tb VALUES(11,'银卡会员',1555); 
INSERT INTO uservip_tb VALUES(12,'钻石会员',12000); 
INSERT INTO uservip_tb VALUES(13,'金卡会员',6115); 
INSERT INTO uservip_tb VALUES(14,'普通会员',230); 
INSERT INTO uservip_tb VALUES(15,'银卡会员',810); 
INSERT INTO uservip_tb VALUES(16,'普通会员',330);
```

输出：

```
vip|visit_nums|visit_users
银卡会员|6|2
金卡会员|3|1
钻石会员|2|1
普通会员|1|1
```

#### 答案

```sql
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qiangrens-organization.gitbook.io/qkd90/shu-ju-ku-he-zhong-jian-jian/sql-yu-ju/niu-ke-sql-da-chang-bi-shi-zhen-ti-40120.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.