Bỏ qua nội dung chính

Tổng hợp câu hỏi phỏng vấn SQL thường gặp (5)

GuideDatabaseDatabase BasicsSQLKhoảng 8678 từKhoảng 29 phút

Nguồn đề bài: Nowcoder - Thử thách SQL nâng cao

Các câu hỏi tương đối khó hoặc khó có thể được bỏ qua tùy theo tình hình thực tế và nhu cầu phỏng vấn của bạn.

Xử lý giá trị rỗng

Thống kê số lượng và tỷ lệ bài thi chưa hoàn thành

Mô tả:

Có bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm), dữ liệu như sau:

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:0180
2100190012021-05-02 10:01:012021-05-02 10:30:0181
3100190012021-09-02 12:01:01(NULL)(NULL)

Hãy thống kê số lượng incomplete_cnt và tỷ lệ incomplete_rate bài thi chưa hoàn thành trong các bài thi có trạng thái chưa hoàn thành. Kết quả từ dữ liệu mẫu như sau:

exam_idincomplete_cntcomplete_rate
900110.333

Giải thích: Bài thi 9001 có 3 bản ghi làm bài, trong đó 2 lần hoàn thành và 1 lần chưa hoàn thành, vì vậy số lượng chưa hoàn thành là 1, tỷ lệ chưa hoàn thành là 0.333 (giữ lại 3 chữ số thập phân).

Cách làm:

Bài này chỉ cần chú ý một phần có điều kiện, một phần không có điều kiện; có thể truy vấn hai phần điều kiện riêng rồi hợp nhất, hoặc trực tiếp phán đoán điều kiện trong select.

Đáp án:

Cách viết 1:

SELECT
    exam_id,
    (COUNT(*) - COUNT(submit_time)) AS incomplete_cnt,
    ROUND((COUNT(*) - COUNT(submit_time)) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    (COUNT(*) - COUNT(submit_time)) > 0;

Dùng COUNT(*) để thống kê tổng số bản ghi trong nhóm, COUNT(submit_time) chỉ thống kê các bản ghi có trường submit_time khác NULL (tức số lần đã hoàn thành). Hiệu của hai giá trị là số lần chưa hoàn thành.

Cách viết 2:

SELECT
    exam_id,
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) AS incomplete_cnt,
    ROUND(COUNT(CASE WHEN submit_time IS NULL THEN 1 END) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    COUNT(CASE WHEN submit_time IS NULL THEN 1 END) > 0;

Dùng biểu thức CASE: khi điều kiện đúng thì trả về một giá trị khác NULL (ví dụ 1), nếu không thì trả về NULL. Sau đó dùng hàm COUNT để thống kê số lượng giá trị khác NULL.

Cách viết 3:

SELECT
    exam_id,
    SUM(submit_time IS NULL) AS incomplete_cnt,
    ROUND(SUM(submit_time IS NULL) / COUNT(*), 3) AS incomplete_rate
FROM
    exam_record
GROUP BY
    exam_id
HAVING
    incomplete_cnt > 0;

Dùng hàm SUM để tính tổng một biểu thức. Khi submit_time là NULL, giá trị của biểu thức (submit_time IS NULL) là 1 (TRUE), nếu không thì là 0 (FALSE). Cộng các giá trị 1 và 0 lại sẽ thu được số lượng chưa hoàn thành.

Thời gian làm bài trung bình và điểm trung bình của bài thi khó của người dùng cấp 0

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký), dữ liệu như sau:

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1100Thuật toán2020-01-01 10:00:00
21002NiuKe số 221006Thuật toán2020-01-01 10:00:00

Bảng thông tin bài thi examination_info (exam_id ID bài thi, tag loại bài thi, difficulty độ khó bài thi, duration thời lượng thi, release_time thời gian phát hành), dữ liệu như sau:

idexam_idtagdifficultydurationrelease_time
19001SQLhard602020-01-01 10:00:00
29002SQLeasy602020-01-01 10:00:00
39004Thuật toánmedium802020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm), dữ liệu như sau:

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0187
4100190012021-06-02 19:01:012021-06-02 19:32:0020
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290

Hãy xuất thời gian làm bài trung bình và điểm trung bình của tất cả bài thi khó đối với mỗi người dùng cấp 0. Với bài chưa hoàn thành, mặc định dùng thời lượng thi tối đa của bài thi và điểm 0. Kết quả từ dữ liệu mẫu như sau:

uidavg_scoreavg_time_took
10013336.7

Giải thích: Người dùng cấp 0 là 1001, bài thi khó là 9001. Các bản ghi làm bài 9001 của 1001 có 3 bản ghi, thời gian lần lượt là 20 phút, chưa hoàn thành (dùng thời lượng bài thi 60 phút), 30 phút (chưa đủ 31 phút), điểm lần lượt là 80, chưa hoàn thành (xử lý là 0), 20. Vì vậy thời gian trung bình là 110/3=36.7 (giữ lại một chữ số thập phân), điểm trung bình là 33 (làm tròn lấy phần nguyên).

Cách làm: Bài này dùng IF là thuận tiện nhất vì có xử lý giá trị NULL. Dĩ nhiên cũng có thể dùng case when, về cơ bản tương tự. Điểm khó của bài nằm ở xử lý giá trị rỗng; các điều kiện truy vấn còn lại không quá khó.

Đáp án:

SELECT UID,
       round(avg(new_socre)) AS avg_score,
       round(avg(time_diff), 1) AS avg_time_took
FROM
  (SELECT er.uid,
          IF (er.submit_time IS NOT NULL, TIMESTAMPDIFF(MINUTE, start_time, submit_time), ef.duration) AS time_diff,
          IF (er.submit_time IS NOT NULL,er.score,0) AS new_socre
   FROM exam_record er
   LEFT JOIN user_info uf ON er.uid = uf.uid
   LEFT JOIN examination_info ef ON er.exam_id = ef.exam_id
   WHERE uf.LEVEL = 0 AND ef.difficulty = 'hard' ) t
GROUP BY UID
ORDER BY UID

Câu lệnh điều kiện nâng cao

Lọc người dùng theo nickname, điểm thành tích và ngày hoạt động được giới hạn (khá khó)

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 110002Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003Tiến công số 322005Thuật toán2020-01-01 10:00:00
41004NiuKe số 425006Thuật toán2020-01-01 10:00:00
51005NiuKe số 530007C++2020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
3100190022021-02-02 19:01:012021-02-02 19:30:0187
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
6100190022021-09-01 12:01:01(NULL)(NULL)
5100190022021-09-05 19:01:012021-09-05 19:40:0189
11100290012020-01-01 12:01:012020-01-01 12:31:0181
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
7100290022021-05-05 18:01:012021-05-05 18:59:0290
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
8100390032021-02-06 12:01:01(NULL)(NULL)
9100390012021-09-07 10:01:012021-09-07 10:31:0189
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-02-01 11:31:0184

Bảng ghi chép luyện tập practice_record (uid ID người dùng, question_id ID câu hỏi, submit_time thời gian nộp bài, score điểm):

iduidquestion_idsubmit_timescore
1100180012021-08-02 11:41:0160
2100280012021-09-02 19:30:0150
3100280012021-09-02 19:20:0170
4100280022021-09-02 19:38:0170
5100380022021-09-01 19:38:0180

Hãy tìm thông tin người dùng có nickname bắt đầu bằng “NiuKe”, kết thúc bằng “số”, điểm thành tích trong khoảng 1200~2500 và lần hoạt động gần nhất (trả lời câu hỏi hoặc làm bài thi) thuộc tháng 9 năm 2021.

Kết quả từ dữ liệu mẫu như sau:

uidnick_nameachievement
1002NiuKe số 21200

Giải thích: Người dùng có nickname bắt đầu bằng “NiuKe”, kết thúc bằng “số” và điểm thành tích trong khoảng 1200~2500 là 1002, 1004;

Hoạt động gần nhất của 1002 ở khu vực bài thi là tháng 9 năm 2021, hoạt động gần nhất ở khu vực câu hỏi cũng là tháng 9 năm 2021; hoạt động gần nhất của 1004 ở khu vực bài thi là tháng 8 năm 2021, khu vực câu hỏi không có hoạt động.

Vì vậy chỉ 1002 thỏa mãn điều kiện cuối cùng.

Cách làm:

Trước tiên liệt kê các câu lệnh truy vấn chính theo điều kiện.

Nickname bắt đầu bằng “NiuKe”, kết thúc bằng “số”: nick_name LIKE "NiuKe%số"

Điểm thành tích trong khoảng 1200~2500: achievement BETWEEN 1200 AND 2500

Điều kiện thứ ba đã giới hạn tháng 9 nên có thể viết trực tiếp: ( date_format( record.submit_time, '%Y%m' )= 202109 OR date_format( pr.submit_time, '%Y%m' )= 202109 )

Đáp án:

SELECT DISTINCT u_info.uid,
                u_info.nick_name,
                u_info.achievement
FROM user_info u_info
LEFT JOIN exam_record record ON record.uid = u_info.uid
LEFT JOIN practice_record pr ON u_info.uid = pr.uid
WHERE u_info.nick_name LIKE "NiuKe%số"
  AND u_info.achievement BETWEEN 1200
  AND 2500
  AND (date_format(record.submit_time, '%Y%m')= 202109
       OR date_format(pr.submit_time, '%Y%m')= 202109)
GROUP BY u_info.uid

Lọc bản ghi làm bài theo quy tắc nickname và quy tắc bài thi (khá khó)

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 119002Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003NiuKe số 3 ♂22005Thuật toán2020-01-01 10:00:00
41004NiuKe số 425006Thuật toán2020-01-01 10:00:00
51005NiuKe số 55520007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

Bảng thông tin bài thi examination_info (exam_id ID bài thi, tag loại bài thi, difficulty độ khó bài thi, duration thời lượng thi, release_time thời gian phát hành):

idexam_idtagdifficultydurationrelease_time
19001C++hard602020-01-01 10:00:00
29002c#hard802020-01-01 10:00:00
39003SQLmedium702020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
4100190012021-06-02 19:01:012021-06-02 19:32:0020
3100190022021-02-02 19:01:012021-02-02 19:30:0187
5100190022021-09-05 19:01:012021-09-05 19:40:0189
6100190022021-09-01 12:01:01(NULL)(NULL)
11100290012020-01-01 12:01:012020-01-01 12:31:0181
16100290012021-09-06 12:01:012021-09-06 12:21:0180
17100290012021-09-06 12:01:01(NULL)(NULL)
18100290012021-09-07 12:01:01(NULL)(NULL)
7100290022021-05-05 18:01:012021-05-05 18:59:0290
12100290022020-02-01 12:01:012020-02-01 12:31:0182
13100290022020-02-02 12:11:012020-02-02 12:31:0183
9100390012021-09-07 10:01:012021-09-07 10:31:0189
8100390032021-02-06 12:01:01(NULL)(NULL)
10100490022021-08-06 12:01:01(NULL)(NULL)
14100590012021-02-01 11:01:012021-02-01 11:31:0184
15100690012021-02-01 11:01:012021-09-01 11:31:0184

Hãy tìm ID bài thi đã hoàn thành và điểm trung bình của người dùng có nickname gồm “NiuKe” + chữ số thuần + “số”, hoặc chỉ gồm chữ số, đối với loại bài thi bắt đầu bằng chữ c (như C, C++, c#), rồi sắp xếp tăng dần theo ID người dùng và điểm trung bình. Kết quả từ dữ liệu mẫu như sau:

uidexam_idavg_score
1002900181
1002900285
1005900184
1006900184

Giải thích: Người dùng có nickname thỏa mãn điều kiện là 1002, 1004, 1005, 1006;

Bài thi bắt đầu bằng c là 9001, 9002;

Trong các bản ghi làm bài thỏa mãn điều kiện trên, điểm hoàn thành bài 9001 của 1002 là 81, 80, điểm trung bình là 81 (80.5 làm tròn là 81);

Điểm hoàn thành bài 9002 của 1002 là 90, 82, 83, điểm trung bình là 85;

Cách làm:

Vẫn như trước, vì đề bài đã đưa ra các điều kiện nên trước tiên viết từng điều kiện.

Tìm người dùng có nickname gồm “NiuKe” + chữ số thuần + “số”, hoặc chỉ gồm chữ số: Ban đầu có thể viết nick_name LIKE 'NiuKe%số' OR nick_name REGEXP '^[0-9]+$', nhưng nếu trong bảng có “NiuKe H số” thì cũng sẽ khớp.

Vì vậy ở đây cần dùng regex: nick_name LIKE '^NiuKe[0-9]+số'

Loại bài thi bắt đầu bằng chữ c: e_info.tag LIKE 'c%' hoặc tag regexp '^c|^C'; cách thứ nhất cũng có thể khớp chữ C viết hoa.

Đáp án:

SELECT UID,
       exam_id,
       ROUND(AVG(score), 0) avg_score
FROM exam_record
WHERE UID IN
    (SELECT UID
     FROM user_info
     WHERE nick_name RLIKE "^NiuKe[0-9]+số $"
       OR nick_name RLIKE "^[0-9]+$")
  AND exam_id IN
    (SELECT exam_id
     FROM examination_info
     WHERE tag RLIKE "^[cC]")
  AND score IS NOT NULL
GROUP BY UID,exam_id
ORDER BY UID,avg_score;

Xuất các trường hợp khác nhau tùy việc bản ghi chỉ định có tồn tại hay không (khó)

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1190Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003Tiến công số 3220Thuật toán2020-01-01 10:00:00
41004NiuKe số 4250Thuật toán2020-01-01 10:00:00
51005NiuKe số 55520007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0187
4100190022021-09-01 12:01:01(NULL)(NULL)
5100190032021-09-02 12:01:01(NULL)(NULL)
6100190042021-09-03 12:01:01(NULL)(NULL)
7100290012020-01-01 12:01:012020-01-01 12:31:0199
8100290032020-02-01 12:01:012020-02-01 12:31:0182
9100290032020-02-02 12:11:01(NULL)(NULL)
10100290022021-05-05 18:01:01(NULL)(NULL)
11100290012021-09-06 12:01:01(NULL)(NULL)
12100390032021-02-06 12:01:01(NULL)(NULL)
13100390012021-09-07 10:01:012021-09-07 10:31:0189

Hãy lọc dữ liệu trong bảng. Khi có bất kỳ người dùng cấp 0 nào có số bài thi chưa hoàn thành lớn hơn 2, hãy xuất số lượng và tỷ lệ bài thi chưa hoàn thành của từng người dùng cấp 0 (giữ lại 3 chữ số thập phân); nếu không có người dùng nào như vậy, hãy xuất hai chỉ số này của tất cả người dùng có bản ghi làm bài. Kết quả sắp xếp tăng dần theo tỷ lệ chưa hoàn thành.

Kết quả từ dữ liệu mẫu như sau:

uidincomplete_cntincomplete_rate
100400.000
100310.500
100140.667

Giải thích: Người dùng cấp 0 là 1001, 1003, 1004; số bài thi đã làm và số bài chưa hoàn thành của họ lần lượt là: 6:4, 2:1, 0:0;

Người dùng cấp 0 là 1001 có số bài chưa hoàn thành lớn hơn 2, vì vậy xuất số lượng chưa hoàn thành và tỷ lệ chưa hoàn thành của ba người dùng này (1004 chưa từng làm bài thi, tỷ lệ chưa hoàn thành mặc định là 0, giữ lại 3 chữ số thập phân thành 0.000);

Kết quả được sắp xếp tăng dần theo tỷ lệ chưa hoàn thành.

Phụ chú: Nếu 1001 không thỏa mãn điều kiện “số bài thi chưa hoàn thành lớn hơn 2”, cần xuất hai chỉ số này của 1001, 1002, 1003, vì bảng ghi chép làm bài chỉ có bản ghi làm bài của ba người dùng này.

Cách làm:

Trước tiên viết SQL có thể thỏa mãn điều kiện “người dùng cấp 0 có số bài thi chưa hoàn thành lớn hơn 2”:

SELECT ui.uid UID
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE ui.uid IN
    (SELECT ui.uid
     FROM user_info ui
     LEFT JOIN exam_record er ON ui.uid = er.uid
     WHERE er.submit_time IS NULL
       AND ui.LEVEL = 0 )
GROUP BY ui.uid
HAVING sum(IF(er.submit_time IS NULL, 1, 0)) > 2

Sau đó viết riêng câu lệnh truy vấn SQL cho hai trường hợp:

Trường hợp 1. Truy vấn tỷ lệ bài thi chưa hoàn thành của người dùng cấp 0 thỏa mãn điều kiện

SELECT
	 tmp1.uid uid,
	sum(
	IF
	( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 )) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( tmp1.uid ),
		3
	) incomplete_rate
FROM
	(
	SELECT DISTINCT
		ui.uid
	FROM
		user_info ui
		LEFT JOIN exam_record er ON ui.uid = er.uid
	WHERE
		er.submit_time IS NULL
		AND ui.LEVEL = 0
	) tmp1
	LEFT JOIN exam_record er ON tmp1.uid = er.uid
GROUP BY
	tmp1.uid
ORDER BY
	incomplete_rate

Trường hợp 2. Truy vấn tỷ lệ bài thi chưa hoàn thành của tất cả người dùng có bản ghi làm bài khi không tồn tại điều kiện yêu cầu

SELECT
	ui.uid uid,
	sum( CASE WHEN er.submit_time IS NULL AND er.start_time IS NOT NULL THEN 1 ELSE 0 END ) incomplete_cnt,
	round(
		sum(
		IF
		( er.submit_time IS NULL AND er.start_time IS NOT NULL, 1, 0 ))/ count( ui.uid ),
		3
	) incomplete_rate
FROM
	user_info ui
	JOIN exam_record er ON ui.uid = er.uid
GROUP BY
	ui.uid
ORDER BY
	incomplete_rate

Ghép lại chính là đáp án:

WITH host_user AS
  (SELECT ui.uid UID
   FROM user_info ui
   LEFT JOIN exam_record er ON ui.uid = er.uid
   WHERE ui.uid IN
       (SELECT ui.uid
        FROM user_info ui
        LEFT JOIN exam_record er ON ui.uid = er.uid
        WHERE er.submit_time IS NULL
          AND ui.LEVEL = 0 )
   GROUP BY ui.uid
   HAVING sum(IF (er.submit_time IS NULL, 1, 0))> 2),
     tt1 AS
  (SELECT tmp1.uid UID,
                   sum(IF (er.submit_time IS NULL
                           AND er.start_time IS NOT NULL, 1, 0)) incomplete_cnt,
                   round(sum(IF (er.submit_time IS NULL
                                 AND er.start_time IS NOT NULL, 1, 0))/ count(tmp1.uid), 3) incomplete_rate
   FROM
     (SELECT DISTINCT ui.uid
      FROM user_info ui
      LEFT JOIN exam_record er ON ui.uid = er.uid
      WHERE er.submit_time IS NULL
        AND ui.LEVEL = 0 ) tmp1
   LEFT JOIN exam_record er ON tmp1.uid = er.uid
   GROUP BY tmp1.uid
   ORDER BY incomplete_rate),
     tt2 AS
  (SELECT ui.uid UID,
                 sum(CASE
                         WHEN er.submit_time IS NULL
                              AND er.start_time IS NOT NULL THEN 1
                         ELSE 0
                     END) incomplete_cnt,
                 round(sum(IF (er.submit_time IS NULL
                               AND er.start_time IS NOT NULL, 1, 0))/ count(ui.uid), 3) incomplete_rate
   FROM user_info ui
   JOIN exam_record er ON ui.uid = er.uid
   GROUP BY ui.uid
   ORDER BY incomplete_rate)
  (SELECT tt1.*
   FROM tt1
   LEFT JOIN
     (SELECT UID
      FROM host_user) t1 ON 1 = 1
   WHERE t1.uid IS NOT NULL )
UNION ALL
  (SELECT tt2.*
   FROM tt2
   LEFT JOIN
     (SELECT UID
      FROM host_user) t2 ON 1 = 1
   WHERE t2.uid IS NULL)

Phiên bản V2 (cải tiến dựa trên phần trên, đáp án ngắn hơn và logic chặt chẽ hơn):

SELECT
	ui.uid,
	SUM(
	IF
	( start_time IS NOT NULL AND score IS NULL, 1, 0 )) AS incomplete_cnt,#3. Số bài thi chưa hoàn thành
	ROUND( AVG( IF ( start_time IS NOT NULL AND score IS NULL, 1, 0 )), 3 ) AS incomplete_rate #4. Tỷ lệ chưa hoàn thành

FROM
	user_info ui
	LEFT JOIN exam_record USING ( uid )
WHERE
CASE

		WHEN (#1. Khi có bất kỳ người dùng cấp 0 nào có số bài thi chưa hoàn thành lớn hơn 2
		SELECT
			MAX( lv0_incom_cnt )
		FROM
			(
			SELECT
				SUM(
				IF
				( score IS NULL, 1, 0 )) AS lv0_incom_cnt
			FROM
				user_info
				JOIN exam_record USING ( uid )
			WHERE
				LEVEL = 0
			GROUP BY
				uid
			) table1
			)> 2 THEN
			uid IN ( #1.1 Tìm từng người dùng cấp 0
			SELECT uid FROM user_info WHERE LEVEL = 0 ) ELSE uid IN ( #2. Nếu không tồn tại người dùng như vậy, tìm người dùng có bản ghi làm bài
			SELECT DISTINCT uid FROM exam_record )
		END
		GROUP BY
			ui.uid
		ORDER BY
		incomplete_rate #5. Kết quả sắp xếp tăng dần theo tỷ lệ chưa hoàn thành

Tỷ lệ các mức điểm khác nhau theo cấp độ người dùng (khá khó)

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1190Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003NiuKe số 3 ♂220Thuật toán2020-01-01 10:00:00
41004NiuKe số 4250Thuật toán2020-01-01 10:00:00
51005NiuKe số 55520007C++2020-01-01 10:00:00
6100666666630006C++2020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100190012021-05-02 10:01:01(NULL)(NULL)
3100190022021-02-02 19:01:012021-02-02 19:30:0175
4100190022021-09-01 12:01:012021-09-01 12:11:0160
5100190032021-09-02 12:01:012021-09-02 12:41:0190
6100190012021-06-02 19:01:012021-06-02 19:32:0020
7100190022021-09-05 19:01:012021-09-05 19:40:0189
8100190042021-09-03 12:01:01(NULL)(NULL)
9100290012020-01-01 12:01:012020-01-01 12:31:0199
10100290032020-02-01 12:01:012020-02-01 12:31:0182
11100290032020-02-02 12:11:012020-02-02 12:41:0176

Để thể hiện định tính kết quả làm bài của người dùng, chúng ta chia điểm bài thi thành bốn mức Xuất sắc, Tốt, Trung bình, Kém theo các mốc [90,75,60] (mốc được tính vào khoảng bên trái). Hãy thống kê tỷ lệ từng mức điểm trong các bài thi đã hoàn thành của người dùng ở các cấp độ khác nhau (giữ lại 3 chữ số thập phân). Người dùng chưa từng hoàn thành bài thi không cần xuất, kết quả sắp xếp giảm dần theo cấp độ người dùng, sau đó giảm dần theo tỷ lệ.

Kết quả từ dữ liệu mẫu như sau:

levelscore_graderatio
3Tốt0.667
3Xuất sắc0.333
0Tốt0.500
0Trung bình0.167
0Xuất sắc0.167
0Kém0.167

Giải thích: Người dùng đã hoàn thành bài thi là 1001, 1002; cấp độ người dùng và mức điểm tương ứng của các bài thi đã hoàn thành như sau:

uidexam_idscorelevelscore_grade
10019001800Tốt
10019002750Tốt
10019002600Trung bình
10019003900Xuất sắc
10019001200Kém
10019002890Tốt
10029001993Xuất sắc
10029003823Tốt
10029003763Tốt

Vì vậy tỷ lệ từng mức điểm của người dùng cấp 0 (chỉ có 1001) là: Xuất sắc 1/6, Tốt 1/6, Trung bình 1/6, Kém 3/6; tỷ lệ từng mức điểm của người dùng cấp 3 (chỉ có 1002) là: Xuất sắc 1/3, Tốt 2/3. Kết quả giữ lại 3 chữ số thập phân.

Cách làm:

Trước tiên viết điều kiện “chia điểm bài thi thành bốn mức Xuất sắc, Tốt, Trung bình, Kém theo các mốc [90,75,60]”, ở đây có thể dùng case when.

CASE
		WHEN a.score >= 90 THEN
		'Xuất sắc'
		WHEN a.score < 90 AND a.score >= 75 THEN
		'Tốt'
		WHEN a.score < 75 AND a.score >= 60 THEN
	'Trung bình' ELSE 'Kém'
END

Điểm mấu chốt của bài là phần này, những phần còn lại chỉ là ghép điều kiện.

Đáp án:

SELECT a.LEVEL,
       a.score_grade,
       ROUND(a.cur_count / b.total_num, 3) AS ratio
FROM
  (SELECT b.LEVEL AS LEVEL,
          (CASE
               WHEN a.score >= 90 THEN 'Xuất sắc'
               WHEN a.score < 90
                    AND a.score >= 75 THEN 'Tốt'
               WHEN a.score < 75
                    AND a.score >= 60 THEN 'Trung bình'
               ELSE 'Kém'
           END) AS score_grade,
          count(1) AS cur_count
   FROM exam_record a
   LEFT JOIN user_info b ON a.uid = b.uid
   WHERE a.submit_time IS NOT NULL
   GROUP BY b.LEVEL,
            score_grade) a
LEFT JOIN
  (SELECT b.LEVEL AS LEVEL,
          count(b.LEVEL) AS total_num
   FROM exam_record a
   LEFT JOIN user_info b ON a.uid = b.uid
   WHERE a.submit_time IS NOT NULL
   GROUP BY b.LEVEL) b ON a.LEVEL = b.LEVEL
ORDER BY a.LEVEL DESC,
         ratio DESC

Truy vấn giới hạn

Ba người đăng ký sớm nhất

Mô tả:

Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1190Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-02-01 10:00:00
31003NiuKe số 3 ♂220Thuật toán2020-01-02 10:00:00
41004NiuKe số 4250Thuật toán2020-01-02 11:00:00
51005NiuKe số 55540007C++2020-01-11 10:00:00
6100666666630006C++2020-11-01 10:00:00

Hãy tìm 3 người có thời gian đăng ký sớm nhất. Kết quả từ dữ liệu mẫu như sau:

uidnick_nameregister_time
1001NiuKe số 12020-01-01 10:00:00
1003NiuKe số 32020-01-02 10:00:00
1004NiuKe số 42020-01-02 11:00:00

Giải thích: Sau khi sắp xếp theo thời gian đăng ký, chọn ba người đầu tiên và xuất ID người dùng, nickname, thời gian đăng ký.

Đáp án:

SELECT uid, nick_name, register_time
    FROM user_info
    ORDER BY register_time
    LIMIT 3

Danh sách trang thứ ba những người hoàn thành bài thi ngay trong ngày đăng ký (khá khó)

Mô tả: Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1190Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003NiuKe số 3 ♂220Thuật toán2020-01-01 10:00:00
41004NiuKe số 4250Thuật toán2020-01-01 10:00:00
51005NiuKe số 55540007Thuật toán2020-01-11 10:00:00
61006NiuKe số 6250Thuật toán2020-01-02 11:00:00
71007NiuKe số 7250Thuật toán2020-01-02 11:00:00
81008NiuKe số 8250Thuật toán2020-01-02 11:00:00
91009NiuKe số 9250Thuật toán2020-01-02 11:00:00
101010NiuKe số 10250Thuật toán2020-01-02 11:00:00
11101166666630006C++2020-01-02 10:00:00

Bảng thông tin bài thi examination_info (exam_id ID bài thi, tag loại bài thi, difficulty độ khó bài thi, duration thời lượng thi, release_time thời gian phát hành):

idexam_idtagdifficultydurationrelease_time
19001Thuật toánhard602020-01-01 10:00:00
29002Thuật toánhard802020-01-01 10:00:00
39003SQLmedium702020-01-01 10:00:00

Bảng ghi chép làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-02 09:01:012020-01-02 09:21:5980
2100290032020-01-20 10:01:012020-01-20 10:10:0181
3100290022020-01-01 12:11:012020-01-01 12:31:0183
4100390022020-01-01 19:01:012020-01-01 19:30:0175
5100490022020-01-01 12:01:012020-01-01 12:11:0160
6100590022020-01-01 12:01:012020-01-01 12:41:0190
7100690012020-01-02 19:01:012020-01-02 19:32:0020
8100790022020-01-02 19:01:012020-01-02 19:40:0189
9100890032020-01-02 12:01:012020-01-02 12:20:0199
10100890012020-01-02 12:01:012020-01-02 12:31:0198
11100990022020-01-02 12:01:012020-01-02 12:31:0182
12101090022020-01-02 12:11:012020-01-02 12:41:0176
13101190012020-01-02 10:01:012020-01-02 10:31:0189

Hãy tìm những người có hướng nghề nghiệp là kỹ sư thuật toán và đã hoàn thành bài thi loại thuật toán ngay trong ngày đăng ký, rồi xếp hạng theo điểm cao nhất trong tất cả các kỳ thi đã tham gia. Bảng xếp hạng rất dài nên sẽ được phân trang, mỗi trang 3 bản ghi; cần lấy thông tin của người ở trang thứ 3 (trang bắt đầu từ 1).

Kết quả từ dữ liệu mẫu như sau:

uidlevelregister_timemax_score
101002020-01-02 11:00:0076
100302020-01-01 10:00:0075
100402020-01-01 11:00:0060

Giải thích: Ngoài 1011, hướng nghề nghiệp của các người dùng khác đều là kỹ sư thuật toán; bài thi loại thuật toán là 9001 và 9002, 11 người dùng đều hoàn thành bài thi loại thuật toán ngay trong ngày đăng ký; khi tính điểm cao nhất của tất cả kỳ thi, chỉ 1002 và 1008 hoàn thành hai kỳ thi, điểm cao nhất của hai kỳ thi của 1002 là 81, của 1008 là 99.

Xếp hạng theo điểm cao nhất như sau:

uidlevelregister_timemax_score
100802020-01-02 11:00:0099
100572020-01-01 10:00:0090
100702020-01-02 11:00:0089
100232020-01-01 10:00:0083
100902020-01-02 11:00:0082
100102020-01-01 10:00:0080
101002020-01-02 11:00:0076
100302020-01-01 10:00:0075
100402020-01-01 11:00:0060
100602020-01-02 11:00:0020

Mỗi trang có 3 bản ghi, trang thứ ba là các bản ghi thứ 7~9, nên chỉ cần trả về các dòng của 1010, 1003, 1004.

Cách làm:

  1. Mỗi trang có ba bản ghi, để lấy thông tin người ở trang thứ ba cần dùng limit.

  2. Thống kê thông tin và điểm của từng bản ghi của những người có hướng nghề nghiệp là kỹ sư thuật toán và đã hoàn thành bài thi loại thuật toán ngay trong ngày đăng ký: trước tiên tìm người dùng thỏa mãn điều kiện, sau đó dùng left join để nối và tìm thông tin cùng điểm của từng bản ghi.

Đáp án:

SELECT t1.uid,
       LEVEL,
       register_time,
       max(score) AS max_score
FROM exam_record t
JOIN examination_info USING (exam_id)
JOIN user_info t1 ON t.uid = t1.uid
AND date(t.submit_time) = date(t1.register_time)
WHERE job = 'Thuật toán'
  AND tag = 'Thuật toán'
GROUP BY t1.uid,
         LEVEL,
         register_time
ORDER BY max_score DESC
LIMIT 6,3

Hàm chuyển đổi văn bản

Sửa bản ghi bị gộp cột

Mô tả: Có bảng thông tin bài thi examination_info (exam_id ID bài thi, tag loại bài thi, difficulty độ khó bài thi, duration thời lượng thi, release_time thời gian phát hành):

idexam_idtagdifficultydurationrelease_time
19001Thuật toánhard602021-01-01 10:00:00
29002Thuật toánhard802021-01-01 10:00:00
39003SQLmedium702021-01-01 10:00:00
49004Thuật toán,medium,8002021-01-01 10:00:00

Người nhập đề đã lỡ nhập đồng thời loại câu hỏi tag, độ khó và thời lượng của một số bản ghi vào trường tag. Hãy tìm các bản ghi nhập sai này, tách chúng rồi xuất theo đúng loại cột.

Kết quả từ dữ liệu mẫu như sau:

exam_idtagdifficultyduration
9004Thuật toánmedium80

Cách làm:

Trước tiên tìm hiểu hàm cần dùng trong bài này.

Hàm SUBSTRING_INDEX dùng để trích xuất một phần của chuỗi theo dấu phân cách được chỉ định. Hàm nhận ba tham số: chuỗi gốc, dấu phân cách và số lượng phần cần trả về.

Cú pháp của hàm SUBSTRING_INDEX:

SUBSTRING_INDEX(str, delimiter, count)
  • str: chuỗi gốc cần tách.
  • delimiter: chuỗi hoặc ký tự dùng làm dấu phân cách.
  • count: số lượng phần cần trả về.
    • Nếu count lớn hơn 0, trả về count phần đầu tính từ bên trái (ranh giới là dấu phân cách).
    • Nếu count nhỏ hơn 0, trả về count phần đầu tính từ bên phải (ranh giới là dấu phân cách), tức đếm từ phải sang trái.

Dưới đây là một số ví dụ minh họa cách dùng hàm SUBSTRING_INDEX:

  1. Trích xuất phần đầu tiên của chuỗi:

    SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 1);
-- Kết quả: 'apple'

  2. Trích xuất phần cuối cùng của chuỗi:

    SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1);
-- Kết quả: 'cherry'

  3. Trích xuất hai phần đầu tiên của chuỗi:

    SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 2);
-- Kết quả: 'apple,banana'

  4. Trích xuất hai phần cuối cùng của chuỗi:

    SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -2);
-- Kết quả: 'banana,cherry'

Đáp án:

SELECT
	exam_id,
	substring_index( tag, ',', 1 ) tag,
	substring_index( substring_index( tag, ',', 2 ), ',',- 1 ) difficulty,
	substring_index( tag, ',',- 1 ) duration
FROM
	examination_info
WHERE
	difficulty = ''

Xử lý nickname quá dài bằng cách cắt ngắn

Mô tả: Có bảng thông tin người dùng user_info (uid ID người dùng, nick_name nickname, achievement điểm thành tích, level cấp độ, job hướng nghề nghiệp, register_time thời gian đăng ký):

iduidnick_nameachievementleveljobregister_time
11001NiuKe số 1190Thuật toán2020-01-01 10:00:00
21002NiuKe số 212003Thuật toán2020-01-01 10:00:00
31003NiuKe số 3 ♂220Thuật toán2020-01-01 10:00:00
41004NiuKe số 4250Thuật toán2020-01-01 11:00:00
51005NiuKe số 567890123440007Thuật toán2020-01-11 10:00:00
61006NiuKe số 67890123456789250Thuật toán2020-01-02 11:00:00

Nickname của một số người dùng đặc biệt dài, gây rối kiểu hiển thị trong một số trường hợp. Vì vậy cần chuyển đổi nickname quá dài trước khi xuất. Hãy xuất thông tin người dùng có số ký tự lớn hơn 10; với người có số ký tự lớn hơn 13, xuất 10 ký tự đầu rồi thêm ba dấu chấm: ....

Kết quả từ dữ liệu mẫu như sau:

uidnick_name
1005NiuKe số 5678901234
1006NiuKe số 67890123...

Giải thích: Người dùng có số ký tự lớn hơn 10 là 1005 và 1006, độ dài lần lượt là 13, 17; vì vậy cần cắt nickname của 1006 trước khi xuất.

Cách làm:

Bài này liên quan đến việc đếm ký tự. Để tính số ký tự của chuỗi (tức độ dài chuỗi), có thể dùng hàm LENGTH hoặc CHAR_LENGTH. Hai hàm này khác nhau ở cách xử lý ký tự nhiều byte.

  1. Hàm LENGTH: trả về số byte của chuỗi cho trước. Với chuỗi chứa ký tự nhiều byte, mỗi ký tự được tính như một byte.

Ví dụ:

SELECT LENGTH('ê'); -- Kết quả: 2, vì ký tự 'ê' chiếm 2 byte trong UTF-8
  1. Hàm CHAR_LENGTH: trả về số ký tự của chuỗi cho trước. Với chuỗi chứa ký tự nhiều byte, mỗi ký tự được tính là một ký tự.

Ví dụ:

SELECT CHAR_LENGTH('ê'); -- Kết quả: 1, vì 'ê' chỉ có một ký tự

Đáp án:

SELECT
	uid,
CASE

		WHEN CHAR_LENGTH( nick_name ) > 13 THEN
		CONCAT( SUBSTR( nick_name, 1, 10 ), '...' ) ELSE nick_name
	END AS nick_name
FROM
	user_info
WHERE
	CHAR_LENGTH( nick_name ) > 10
GROUP BY
	uid;

Lọc và thống kê khi chữ hoa chữ thường lẫn lộn (khá khó)

Mô tả:

Có bảng thông tin bài thi examination_info (exam_id ID bài thi, tag loại bài thi, difficulty độ khó bài thi, duration thời lượng thi, release_time thời gian phát hành):

idexam_idtagdifficultydurationrelease_time
19001Thuật toánhard602021-01-01 10:00:00
29002C++hard802021-01-01 10:00:00
39003C++hard802021-01-01 10:00:00
49004sqlmedium702021-01-01 10:00:00
59005C++hard802021-01-01 10:00:00
69006C++hard802021-01-01 10:00:00
79007C++hard802021-01-01 10:00:00
89008SQLmedium702021-01-01 10:00:00
99009SQLmedium702021-01-01 10:00:00
109010SQLmedium702021-01-01 10:00:00

Bảng thông tin làm bài exam_record (uid ID người dùng, exam_id ID bài thi, start_time thời gian bắt đầu làm bài, submit_time thời gian nộp bài, score điểm):

iduidexam_idstart_timesubmit_timescore
1100190012020-01-01 09:01:012020-01-01 09:21:5980
2100290032020-01-20 10:01:012020-01-20 10:10:0181
3100290022020-02-01 12:11:012020-02-01 12:31:0183
4100390022020-03-01 19:01:012020-03-01 19:30:0175
5100490022020-03-01 12:01:012020-03-01 12:11:0160
6100590022020-03-01 12:01:012020-03-01 12:41:0190
7100690012020-05-02 19:01:012020-05-02 19:32:0020
8100790032020-01-02 19:01:012020-01-02 19:40:0189
9100890042020-02-02 12:01:012020-02-02 12:20:0199
10100890012020-02-02 12:01:012020-02-02 12:31:0198
11100990022020-02-02 12:01:012020-01-02 12:43:0181
12101090012020-01-02 12:11:01(NULL)(NULL)
13101090012020-02-02 12:01:012020-01-02 10:31:0189

Loại bài thi tag có thể bị lẫn chữ hoa chữ thường. Trước tiên hãy lọc các loại tag có số lần làm bài nhỏ hơn 3, sau đó thống kê số lần làm bài ban đầu tương ứng sau khi chuyển chúng thành chữ hoa.

Nếu tag không thay đổi sau khi chuyển đổi thì không xuất kết quả đó.

Kết quả từ dữ liệu mẫu như sau:

taganswer_cnt
C++6

Giải thích: Các bài thi đã được làm là 9001, 9002, 9003, 9004; loại tag và số lần làm bài của chúng như sau:

exam_idtaganswer_cnt
9001Thuật toán4
9002C++6
9003c++2
9004sql2

Các tag có số lần làm bài nhỏ hơn 3 là c++ và sql. Sau khi chuyển thành chữ hoa, chỉ C++ đã có số lần làm bài, vì vậy xuất số lần làm bài sau khi chuyển c++ thành chữ hoa là 6.

Cách làm:

Trước hết, bài này hơi không nhất quán: theo dữ liệu mẫu, 9004 chỉ có 1 lần, nhưng ở đây hiển thị là 2 lần.

Xem các hàm chuyển đổi chữ hoa chữ thường:

  1. Hàm UPPER(s) hoặc UCASE(s) chuyển toàn bộ ký tự chữ cái trong chuỗi s thành chữ hoa;

  2. Hàm LOWER(s) hoặc LCASE(s) chuyển toàn bộ ký tự chữ cái trong chuỗi s thành chữ thường.

Điểm khó là nối cùng một bảng để truy vấn các giá trị khác nhau.

Đáp án:

WITH a AS
  (SELECT tag,
          COUNT(start_time) AS answer_cnt
   FROM exam_record er
   JOIN examination_info ei ON er.exam_id = ei.exam_id
   GROUP BY tag)
SELECT a.tag,
       b.answer_cnt
FROM a
INNER JOIN a AS b ON UPPER(a.tag)= b.tag # a viết thường, b viết hoa
AND a.tag != b.tag
WHERE a.answer_cnt < 3;

Lời cuối

Nếu nội dung hữu ích với bạn, hãy tiện tay tặng JavaGuide một Star miễn phí để ủng hộ: GitHub | Gitee.

JavaGuide đã được duy trì gần bảy năm, tích lũy 6100+ commit, với sự chung tay hoàn thiện của 620+ contributor. Star, phản hồi và PR của bạn đều là động lực để dự án tiếp tục cập nhật.

Nếu bạn đang chuẩn bị phỏng vấn backend / phát triển ứng dụng AI, có thể tham khảo Knowledge Planet của tôi, bao gồm các project thực tế về backend và AI, tối ưu CV, hỏi đáp 1-1 và tài liệu về các trọng điểm thường gặp, đã được duy trì liên tục sáu năm.