一个表tb_clazz，两个字段（clazz:班级名，name：学生姓名），输出班级人数最多的5个班级名称。(网易)

select clazz,count(*) as student_cnt
from tb_clazz
group  by clazz
order by student_cnt desc
limit 5

存有姓名和分数的数据库中查排名前一百？（作业帮）

SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 100;

查询班级里同名的学生（满帮）

SELECT name, COUNT(*) as count
FROM students
GROUP BY name
HAVING count > 1;

写SQL：设计一个社交的数据库，就是可以允许用户去发帖子，然后可以发评论，然后也可以给帖子点赞。你设计几张表，然后并且从里面去找到点赞数最高的帖子，以及发评论数最多的一个用户和他最新的一条评论（美团）

用户表，帖子表，点赞表，评论表。

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE Likes (
    like_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES Posts(post_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

CREATE TABLE Comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES Posts(post_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

查询点赞最高的帖子：

SELECT p.post_id, p.content, COUNT(l.like_id) AS like_count
FROM Posts p
LEFT JOIN Likes l ON p.post_id = l.post_id
GROUP BY p.post_id
ORDER BY like_count DESC
LIMIT 1;

查询发表评论最多的用户和他最新的一条评论：

WITH CommentCount AS (
    SELECT c.user_id, COUNT(c.comment_id) AS comment_count
    FROM Comments c
    GROUP BY c.user_id
),
LatestComment AS (
    SELECT c.user_id, c.content, c.created_at
    FROM Comments c
    WHERE c.created_at = (
        SELECT MAX(created_at)
        FROM Comments
        WHERE user_id = c.user_id
    )
)
SELECT u.username, cc.comment_count, lc.content, lc.created_at
FROM Users u
JOIN CommentCount cc ON u.user_id = cc.user_id
JOIN LatestComment lc ON u.user_id = lc.user_id
ORDER BY cc.comment_count DESC
LIMIT 1;

SQL题：学生表，属性有id、name、yuwen_grade、math_grade、english_grade、all_grade（总成绩）。取总成绩第三名的学生id（可能会有并列的情况，并列的情况，所有第三名都要查出来），如果把all_grade去掉，查总成绩第三的学生。（蚂蚁）

方法一

select id
from (
    select id, dense_rank() over (order by all_grade desc) as rnk
    from student 
) as ranked
where rnk=3;

方法二，对于每个学生s，子查询统计比他总成绩更高的不同成绩有多少种。如果恰有2种更高的成绩，则该学生是第三名。

select id
from student as s
where (
    select count(distinct all_grade)
    from student 
    where all_grade > s.all_grade
) = 2 ;

如果没有all_grade字段，则：

select id
from (
    select id,
    dense_rank() over(
        order by (yuwen_grade + math_grade + english_grade) desc
    ) as rnk
    from student 
)as ranked
where rnk=3;

select id 
from student as s
where(
    select count(distinct (yuwen_grade+math_grade+english_grade))
    from student 
    where (yuwen_grade + math_grade + english_grade)
    >(s.yuwen_grade + s.math_grade + s.english_grade)
) = 2;