精华内容
下载资源
问答
  • sql题目
    千次阅读
    2019-05-22 23:05:37
    Easy
    

    175.组合两个表
    左连接

    select Person.FirstName, Person.LastName, Address.City, Address.State
    from Person
    left join Address
    on Person.PersonId = Address.PersonId
    

    176.第二高的薪水
    先选出最大的,再选出第二大的

    SELECT MAX(Salary) SecondHighestSalary
    FROM Employee
    WHERE Salary<(SELECT MAX(Salary) FROM Employee) //where条件里是薪水小于最大薪水
    

    181.超过经理收入的员工
    表自身按不同属性进行内连接
    当一个表要比较自身的不同列的时候可以对自身进行表连接

    SELECT e.Name as Employee
    FROM Employee as e
    JOIN Employee as m
    ON e.ManagerId = m.Id
    WHERE e.Salary>m.Salary
    

    182.查找重复的电子邮箱
    在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
    HAVING 子句可以让我们筛选分组后的各组数据。

    SELECT Email
    FROM Person
    GROUP BY Email
    HAVING COUNT(*)>1
    

    183.从不订购的客户

    SELECT Name as Customers from Customers
    LEFT JOIN Orders
    ON Customers.Id = Orders.CustomerId
    WHERE Orders.CustomerId IS NULL;
    

    196.删除重复的电子邮件
    参考:
    https://leetcode.com/problems/delete-duplicate-emails/discuss/55614/A-skillful-mysql-solution-avoid-"-select-and-update-conflict"

    https://leetcode.com/problems/delete-duplicate-emails/discuss/55553/Simple-Solution

    197.上升的温度
    表自身连接

    SELECT t1.Id
    FROM Weather t1
    INNER JOIN Weather t2
    ON TO_DAYS(t1.Date) = TO_DAYS(t2.Date) + 1
    WHERE t1.Temperature > t2.Temperatur
    

    595.大的国家
    参考:
    https://leetcode.com/problems/big-countries/discuss/103561/Union-and-OR-and-the-Explanation

    用Union代替Or,加快速度(适用于查询不同列的情况,如果是同一列则OR快)

    596.超过5名学生的课
    普通的group by,每组统计的时候用distinct去重

    627.交换工资
    https://leetcode.com/problems/swap-salary/discuss/104716/Short-and-Simple

    https://leetcode.com/problems/swap-salary/discuss/104722/Simple-and-short-with-IF

    Medium
    

    177.第N高的薪水

    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
    DECLARE M INT;
    SET M=N-1;
      RETURN (
          SELECT DISTINCT Salary
          FROM Employee
          ORDER BY Salary DESC
          LIMIT 1 OFFSET M     
      );
    END
    

    LIMIT m OFFSET n 表示从第n个记录开始取m条(第一个记录编号是0)
    参考:
    https://leetcode.com/problems/nth-highest-salary/discuss/53041/Accpted-Solution-for-the-Nth-Highest-Salary

    178.分数排名

    SELECT Scores.Score, COUNT(Ranking.Score) AS RANK 
      FROM Scores   //Score表
         , (
           SELECT DISTINCT Score
             FROM Scores
           ) Ranking  //Rankin表,表中存放所有不重复的成绩
           //FROM对两个表进行了笛卡尔积,假设一共有4个不同的成绩,score表中的每一行被复制4次,并添加成绩一列
     WHERE Scores.Score <= Ranking.Score  //用来计算Score中每个成绩分别对应的名次,假设当前行的成绩是2,所有的成绩是1,2,3,4,那么他的名次就是第3名,他小于1,2,3,4,中的三个数
     GROUP BY Scores.Id, Scores.Score
     ORDER BY Scores.Score DESC;
    

    180.连续出现的数字
    参考:(好理解)
    https://leetcode.com/problems/consecutive-numbers/discuss/53418/Simple-solution

    Select DISTINCT l1.Num As ConsecutiveNums
    from Logs l1, Logs l2, Logs l3   //三个表连接
    where l1.Id=l2.Id-1 and l2.Id=l3.Id-1  //取Id相连的行
    and l1.Num=l2.Num and l2.Num=l3.Num  //取数字相同的行
    

    参考:(时间占用最小,至于为什么不太清楚)
    https://leetcode.com/problems/consecutive-numbers/discuss/282107/Simple-MySql-Tuple-Check.-Fast

    SELECT Distinct(Num) as ConsecutiveNums
    FROM Logs
    WHERE (ID+1,Num) IN (Select Id, Num From Logs)
    AND (ID+2,Num) IN (Select Id, Num From Logs)
    //用两次表查询
    

    184.部门工资最高的员工
    参考:
    https://leetcode.com/problems/department-highest-salary/discuss/53607/Three-accpeted-solutions
    这里取的第1,3种方法

    SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
    FROM
    	Employee E,
    	(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T,  //将部门以及最高工资提取成一个表
    	Department D   //三个表连接
    WHERE E.DepartmentId = T.DepartmentId 
      AND E.Salary = T.max
      AND E.DepartmentId = D.id
    
    SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
    from 
    	Employee E,
    	Department D 
    WHERE E.DepartmentId = D.id 
      AND (DepartmentId,Salary) in 
      (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) //将部门以及最高工资提取成一个表
    

    626.换座位

    /* get all the even numbered rows as odd numbered rows */
    SELECT s1.id - 1 as id, s1.student
    FROM Seat s1
    WHERE s1.id MOD 2 = 0   //先凑出偶数id的
    
    UNION
    
    /* get all the odd numbered rows as even numbered rows */
    SELECT s2.id + 1 as id, s2.student
    FROM Seat s2
    WHERE s2.id MOD 2 = 1 AND s2.id != (SELECT MAX(id) FROM Seat)  //凑出奇数id的,但是不处理最后一行,因为每次都是以1开头的,那么如果最后一个以奇数结尾,说明一共有奇数行,则最后一行不需要处理
    /* Just don't get the last row as we will handle it in the next UNION */
    
    UNION
    
    /* get the last row if odd and don't change the id value */
    SELECT s3.id, s3.student
    FROM Seat s3
    WHERE s3.id MOD 2 = 1 AND s3.id = (SELECT MAX(id) FROM Seat) //如果是奇数行,直接取最后一行奇数
    
    /* Order the result by id */
    ORDER BY id ASC;
    

    参考:
    https://leetcode.com/problems/exchange-seats/discuss/104707/Using-two-UNION-operators

    更多相关内容
  • PostgreSQL DBA认证 PGCE-E-092-中级SQL题目,可以测试你对SQL的掌握程序,特别好的文档,分享给大家学习了
  • 一些简单的sql题目.txt

    2020-07-23 14:56:21
    这是一本关于sqi题目的文档,相关的有问题,有答案,有一些讲解。希望能够对您有所帮助,谢谢。
  • LeetCode-SQL题目

    千次阅读 2021-12-23 15:41:38
    题目 题解链接 标签 备注 1 176 176. 第二高的薪水 【LeetCode-SQL】176. 第二高的薪水 2 184 184. 部门工资最高的员工 【LeetCode-SQL】184. 部门工资最高的员工 3 4 5 6 ...

    一、重点

    序号题号题目题解链接标签备注
    1176176. 第二高的薪水【LeetCode-SQL】176. 第二高的薪水重复值如何返回?
    2177177. 第N高的薪水【LeetCode-SQL】177. 第N高的薪水排名、几种窗口函数比较
    3178178. 分数排名【LeetCode-SQL】178. 分数排名
    4180180. 连续出现的数字【LeetCode-SQL】180. 连续出现的数字求连续
    5184184. 部门工资最高的员工【LeetCode-SQL】184. 部门工资最高的员工
    6185185. 部门工资前三高的所有员工【LeetCode-SQL】185. 部门工资前三高的所有员工Hard
    7196196. 删除重复的电子邮箱【LeetCode-SQL】196. 删除重复的电子邮箱
    8197197. 上升的温度【LeetCode-SQL】197. 上升的温度日期差值
    9262262. 行程和用户【LeetCode-SQL】262. 行程和用户Hard
    10601601. 体育馆的人流量【LeetCode-SQL】601. 体育馆的人流量Hard
    11626626. 换座位【LeetCode-SQL】626. 换座位
    1211791179. 重新格式化部门表【LeetCode-SQL】1179. 重新格式化部门表case when的单行输出情况
    13580580. 统计各专业学生人数【LeetCode-SQL】580. 统计各专业学生人数空值不要忽略
    1410971097. 游戏玩法分析 V【LeetCode-SQL】1097. 游戏玩法分析 VHard
    1519881988. 找出每所学校的最低分数要求【LeetCode-SQL】1988. 找出每所学校的最低分数要求
    16569569. 员工薪水中位数【LeetCode-SQL】569. 员工薪水中位数Hard
    1711411141. 查询近30天活跃用户数【LeetCode-SQL】1141. 查询近30天活跃用户数几种时间间隔写法
    18607607. 销售员【LeetCode-SQL】607. 销售员注意自关联
    1911271127. 用户购买平台【LeetCode-SQL】1127. 用户购买平台Hard
    2015321532. 最近的三笔订单1532. 最近的三笔订单
    21586586. 订单最多的客户【LeetCode-SQL】586. 订单最多的客户常规、dense_rank()
    22608608. 树节点【LeetCode-SQL】608. 树节点
    2316991699. 两人之间的通话次数【LeetCode-SQL】1699. 两人之间的通话次数重复项
    2418671867. 最大数量高于平均水平的订单【LeetCode-SQL】1867. 最大数量高于平均水平的订单子查询+all
    25578578. 查询回答率最高的问题【LeetCode-SQL】578. 查询回答率最高的问题
    2616511651. Hopper 公司查询 III【LeetCode-SQL】1651. Hopper 公司查询 III窗口函数定义 or betweenHard
    2711261126. 查询活跃业务【LeetCode-SQL】1126. 查询活跃业务
    28571571. 给定数字的频率查询中位数【LeetCode-SQL】571. 给定数字的频率查询中位数Hard
    2910771077. 项目员工 III【LeetCode-SQL】1077. 项目员工 III
    3010841084. 销售分析III【LeetCode-SQL】1084. 销售分析III
    3114791479. 周内每天的销售情况【LeetCode-SQL】1479. 周内每天的销售情况dayofweek()、weekday()、date_format()Hard
    32585585. 2016年的投资【LeetCode-SQL】585. 2016年的投资with、having、dense_rank() over()
    33550550. 游戏玩法分析 IV【LeetCode-SQL】550. 游戏玩法分析 IVlag()、rank()、datediff()、lef join
    3411121112. 每位学生的最高成绩【LeetCode-SQL】1112. 每位学生的最高成绩子查询、窗口函数
    3511641164. 指定日期的产品价格【LeetCode-SQL】1164. 指定日期的产品价格GROUP BY VS PARTITION BY
    36618618. 学生地理信息报告【LeetCode-SQL】618. 学生地理信息报告row_number() + max(case when then end)表格格式化Hard(消化掉)
    3710831083. 销售分析 II【LeetCode-SQL】1083. 销售分析 II
    3813551355. 活动参与者【LeetCode-SQL】1355. 活动参与者
    3917091709. 访问日期之间最大的空档期【LeetCode-SQL】1709. 访问日期之间最大的空档期lead()、UNION ALL+INNER JOIN+min()+max()
    4018111811. 寻找面试候选人【LeetCode-SQL】1811. 寻找面试候选人求连续的四种方法
    41579579. 查询员工的累计薪水【LeetCode-SQL】579. 查询员工的累计薪水窗口函数定义Hard
    4211491149. 文章浏览 II【LeetCode-SQL】1149. 文章浏览 II
    4313211321. 餐馆营业额变化增长【LeetCode-SQL】1321. 餐馆营业额变化增长
    4415111511. 消费者下单频率【LeetCode-SQL】1511. 消费者下单频率sum(if())+月份
    4516131613. 找到遗失的ID【LeetCode-SQL】1613. 找到遗失的IDwith recursive
    4619901990. 统计实验的数量【LeetCode-SQL】 1990. 统计实验的数量Full outer join vs Cross join
    47574574. 当选者【LeetCode-SQL】 574. 当选者
    48603603. 连续空余座位【LeetCode-SQL】603. 连续空余座位seat_id - row_number() over() 、变量理解
    4914541454. 活跃用户【LeetCode-SQL】1454. 活跃用户
    50597597. 好友申请 I:总体通过率【LeetCode-SQL】597. 好友申请 I:总体通过率
    51615615. 平均工资:部门与公司比较【LeetCode-SQL】615. 平均工资:部门与公司比较partition by VS group byHard
    52534534. 游戏玩法分析 III【LeetCode-SQL】534. 游戏玩法分析 IIIjoin、sum over、变量求累加
    5313361336. 每次访问的交易次数【LeetCode-SQL】1336. 每次访问的交易次数仔细拆解,造表关联Hard
    5412851285. 找到连续区间的开始和结束数字【LeetCode-SQL】1285. 找到连续区间的开始和结束数字连续区间起始值
    5515271527. 患某种疾病的患者【LeetCode-SQL】1527. 患某种疾病的患者正则表达式
    5613841384. 按年度列出销售总额【LeetCode-SQL】1384. 按年度列出销售总额递归、一行变多行、非相等joinHard

    二、简单

    序号题号题目题解链接标签备注
    1175175. 组合两个表简单,略列名&左连接
    2183183. 从不订购的客户简单,略left join b on xx=yy and b.col is null;
    3595595. 大的国家简单,略至少是>=, or 或 union
    4596596. 超过5名学生的课简单,略having count(distinct(student)) >= 5
    5620620. 有趣的电影简单,略mod(id, 2)=1id%2 = 1
    6627627. 变更性别简单,略sex = if (sex = "m", "f", "m");sex =(case sex when 'm' then 'f' else 'm' end)
    720262026. 低质量的问题简单,略审题+N-count
    8577577. 员工奖金简单,略ifnull(bonus, 0) < 1000
    9612612. 平面上的最近距离简单,略(p1.x, p1.y) <> (p2.x,p2.y), sqrt, round, cast
    10614614. 二级关注者简单,略注意将子查询转换为join
    1110981098. 小众书籍简单,略子查询与join转换 & ifnull(sum(quantity), 0) < 10
    12602602. 好友申请 II :谁有最多的好友简单,略union all+order by
    1310821082. 销售分析 I 简单,略with、all、rank()
    14570570. 至少有5名直接下属的经理简单,略注意将子查询转换为join
    1511731173. 即时食物配送 I简单,略avg()
    1611931193. 每月交易 I简单,略月份处理
    1712121212. 查询球队积分简单,略union all+sum(case when x then y end)
    1816831683. 无效的推文简单,略length() vs char_length()
    19584584. 寻找用户推荐人简单,略注意null的情况
    20512512. 游戏玩法分析 II简单,略子查询、dense_rank()、all
    2110701070. 产品销售分析 III略,可复习rank()、子查询&join
    2210761076. 项目员工II简单,略rank() over()
    2311131113. 报告的记录简单,略注意理解题意
    2411581158. 市场分析 I简单,略注意where和on筛选条件区别
    2512111211. 查询结果的质量和占比简单,略sum()/count() = avg()
    261795. 每个产品在不同商店的价格简单,略行转列、union
    271484. 按日期分组销售产品简单,略组内连接与排序group_concat(distinct product order by product separator ',')
    2816671667. 修复表中的名字简单,略字符串数

    三、可略

    序号题号题目题解链接标签备注
    1181181. 超过经理收入的员工简单,略
    2182182. 查找重复的电子邮箱简单,略
    3511511. 游戏玩法分析 I简单,略
    410501050. 合作过至少三次的演员和导演简单,略
    511071107. 每日新用户统计简单,略
    6619619. 只出现一次的最大数字简单,略
    711481148. 文章浏览 I简单,略
    816611661. 每台机器的进程平均运行时间简单,略
    919781978. 上级经理已离职的公司员工简单,略
    10610610. 判断三角形简单,略
    11613613. 直线上的最近距离简单,略
    1210681068. 产品销售分析 I简单,略
    1310691069. 产品销售分析 II简单,略
    1411321132. 报告的记录 II简单,略
    151581. 进店却未进行过交易的顾客简单,略
    1614451445. 苹果和桔子简单,略
    1712641264. 页面推荐简单,略
    1818211821. 寻找今年具有正收入的客户简单,略
    1917291729. 求关注者的数量简单,略
    2017311731. 每位经理的下属员工数量简单,略
    2117411741. 查找每个员工花费的总时间简单,略
    2216931693. 每天的领导和合伙人简单,略
    2313931393. 股票的资本损益简单,略
    241075
    251142
    261045
    271159
    281174
    291194
    301204
    311205
    展开全文
  • 这是我自己整理的sql题目,里面有建表语句还有代码答案以及讲解,特别适合初级参考学习。
  • 超经典的20道SQL题目(附加解题思路)

    千次阅读 热门讨论 2022-03-23 16:31:58
    最近学了SQL的内容,老师也给我们发了练习题,感觉在做题的过程中得到了提高,所以将题目和我自己的答案分享一下,希望对大家有所帮助。我使用的是SQL Server 2014 Management Studio。 ---学生表 create table ...

    最近学了SQL的内容,老师也给我们发了练习题,感觉在做题的过程中得到了提高,所以将题目和我自己的答案分享一下,希望对大家有所帮助。我使用的是SQL Server 2014 Management Studio。

    ---学生表
    create table student(
    sno varchar(10) primary key,              	--学号
    sname varchar(20),                            	--姓名
    sage numeric(2),		            	--年龄
    ssex varchar(5)			--性别
    );
    ----教师表
    create table teacher(
    tno varchar(10) primary key,          	---教师编号
    tname varchar(20)			---教师姓名
    );
    ----课程表
    create table course(			
    cno varchar(10),			---课程号
    cname varchar(20),		--课程名
    tno varchar(20),			--教师编号
    constraint pk_course primary key (cno,tno)
    );
    ---成绩表
    create table sc(			
    sno varchar(10),			---学号
    cno varchar(10),			---课程号
    score numeric(4,2),			---学生成绩
    constraint pk_sc primary key (sno,cno)
    );
    /*******初始化学生表的数据******/
    insert into student values ('s001','张三',23,'男');
    insert into student values ('s002','李四',23,'男');
    insert into student values ('s003','吴鹏',25,'男');
    insert into student values ('s004','琴沁',20,'女');
    insert into student values ('s005','王丽',20,'女');
    insert into student values ('s006','李波',21,'男');
    insert into student values ('s007','刘玉',21,'男');
    insert into student values ('s008','萧蓉',21,'女');
    insert into student values ('s009','陈萧晓',23,'女');
    insert into student values ('s010','陈美',22,'女');
    
    /******************初始化教师表***********************/
    insert into teacher values ('t001', '刘阳');
    insert into teacher values ('t002', '谌燕');
    insert into teacher values ('t003', '胡明星');
    
    /***************初始化课程表****************************/
    insert into course values ('c001','J2SE','t002');
    insert into course values ('c002','Java Web','t002');
    insert into course values ('c003','SSH','t001');
    insert into course values ('c004','Oracle','t001');
    insert into course values ('c005','SQL SERVER 2005','t003');
    insert into course values ('c006','C#','t003');
    insert into course values ('c007','JavaScript','t002');
    insert into course values ('c008','DIV+CSS','t001');
    insert into course values ('c009','PHP','t003');
    insert into course values ('c010','EJB3.0','t002');
    
    /***************初始化成绩表***********************/
    insert into sc values ('s001','c001',78.9);
    insert into sc values ('s002','c001',80.9);
    insert into sc values ('s003','c001',81.9);
    insert into sc values ('s004','c001',60.9);
    insert into sc values ('s001','c002',82.9);
    insert into sc values ('s002','c002',72.9);
    insert into sc values ('s003','c002',81.9);
    insert into sc values ('s001','c003','59');
    

    1、 查询“c001”课程比“c002”课程成绩高的所有学生的学号;
    解题思路:我需要查询的数据都在同一个表里面,所以这里我使用的是自连接

    select a.sno from dbo.sc a
    inner join dbo.sc b
    on a.sno=b.sno
    where a.cno='c001' and b.cno='c002'
    and a.score>b.score
    

    2、 查询平均成绩大于60 分的同学的学号和平均成绩;
    解题思路: 这里使用了聚合函数求平均值avg

    select sno,avg(score) from dbo.sc
    group by sno
    having avg(score)>60
    

    3、查询所有同学的学号、姓名、选课数、总成绩;
    解题思路:使用了聚合函数sum求和、count个数,以及左连接来查询出所有同学的信息

    select xs.sno,sname,sum(score),count(cno)from dbo.student xs
    left join dbo.sc cj
    on xs.sno=cj.sno
    group by xs.sno,sname
    

    4、 查询姓“刘”的老师的个数;
    解题思路:这里使用了like模糊查询,通配符百分比( % ):任何零个或多个字符的字符串

    select count(tname)from dbo.teacher
    where tname like'刘%'
    

    5、 查询没学过“谌燕”老师课的同学的学号、姓名;
    解题思路:这里我使用了嵌套子查询来查询没学过‘谌燕’老师的课,‘没学过’用not in,所以这里学生表要用 not in

    select sno,sname from dbo.student 
    where sno not in(
    select sno from dbo.sc
    where cno in(
    select cno from dbo.teacher
    where tname='谌燕'
    ))
    

    6、 查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
    解题思路:使用了子查询和自连接,以及使用and查找满足两个条件的行

    select sno,sname from dbo.student
    where sno in(
    select a.sno from dbo.sc a
    inner join dbo.sc b
    on a.sno=b.sno
    where a.cno='c001' and b.cno='c002'
    )
    

    7、 查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
    解题思路:跟上面的第五题差不多,只是把‘没学过’not in 改成‘学过’ in

    select sno,sname from dbo.student 
    where sno  in(
    select sno from dbo.sc
    where cno in(
    select cno from dbo.teacher
    where tname='谌燕'
    ))
    

    8、查询所有课程成绩小于60 分的同学的学号、姓名;
    解题思路:使用了两个内连接和where条件查询子句

    select xs.sno sname from dbo.student xs
    inner join dbo.sc cj
    on xs.sno=cj.sno
    inner join dbo.course kc
    on kc.cno=cj.cno
    where cj.score<60
    

    9、 查询没有学全所有课的同学的学号、姓名;
    解题思路:使用子查询

    select*from dbo.student
    where sno not in (
    select sno from dbo.sc
    )
    

    10、 查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
    解题思路:使用嵌套子查询和where子句

    select*from dbo.student
    where sno in(
    select sno from dbo.sc
    where cno in(
    select cno from dbo.student
    where sno='s001'
    )
    )
    

    11、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
    解题思路:使用聚合函数 min最小值 max最大值

    select cno 课程ID,max(score)最高分,min(score)最低分
     from dbo.sc
     group by cno
    

    12、查询每门课程被选修的学生数
    解题思路:使用聚合函数count个数

    select cno ,count(*) from dbo.sc
    group by cno
    

    13、查询出只选修了一门课程的全部学生的学号和姓名
    解题思路:这里只需要查询出只选修一门课的学生,首先我使用了内连接,其次是使用having子句来根据指定的条件列表过滤分组

    select xs.sno,xs.sname,count(cno) from dbo.student xs
    inner join dbo.sc cj
    on xs.sno=cj.sno
    group by xs.sno,xs.sname
    having count(cno)=1
    

    14、1981 年出生的学生名单(注:Student 表中Sage 列的类型是numeric)
    解题思路:getdate获取当前时间datediff是计算日期时间。它有三个值datepart/startdate/Enddate,第一个是规定了应在日期的哪一部分计算差额的参数;第二个是计算的开始日期;第三个是计算的终止日期

    select*from dbo.student
    where sage=datediff(yyyy,'1981',getdate())
    

    15、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
    解题思路:使用聚合函数,其次是asc升序和desc降序

    select cno,avg(score)from dbo.sc
    group by cno
    order by avg(score) asc,cno desc
    
    

    16、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
    解题思路:使用了内连接和聚合函数

    select xs.sno,sname,avg(score)from dbo.student xs
    inner join dbo.sc cj
    on xs.sno=cj.sno
    group by xs.sno,sname
    having avg(score)>85
    

    17、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
    解题思路:使用了内连接,以及使用and查找满足两个条件的行

    select xs.sno,sname,score from dbo.student xs
    inner join dbo.sc cj
    on xs.sno=cj.sno
    where cno='c001' and score>80
    

    18、查询两门以上不及格课程的同学的学号及其平均成绩
    解题思路:这里查询两门不及格的学生,使用where子句直接小于60,再通过having来判断是否大于1

    select sno, avg(score) from dbo.sc
    where score<60 
    group by sno
    having count(*)>1
    

    19、检索“c004”课程分数小于60,按分数降序排列的同学学号
    解题思路:直接使用and来满足where查询出课程号为c004和分数小于60

    select sno from dbo.sc
    where cno='c004' 
    and score<60
    order by score desc
    

    20、删除“s002”同学的“c001”课程的成绩

     delete from dbo.sc
     where  sno='s002' and cno='c001'
    
    展开全文
  • 本文记录了牛客网sql全部题目的答案与难题解析,部分题目包含多种解法,并且涵盖了开窗函数等各种语法点的理解 标题中高亮的题目,是易错题 牛客网刷题链接:牛客网sql在线练习 参考教程资源: ​ mysql教程1 ​ ...

    本文记录了牛客网sql全部题目的答案与难题解析,部分题目包含多种解法,并且涵盖了开窗函数等各种语法点的理解

    标题中高亮的题目,是易错题

    牛客网刷题链接:牛客网sql在线练习
    本文所有语句使用mysql8.0

    参考教程资源:

    mysql教程1

    mysql教程2

    1.where+子查询

    select * from employees
        order by hire_date desc
        limit 0,1;
        
        
    select 
    * 
    from employees
    where hire_date = 
        (
             select 
             max(hire_date) 
             from employees
        )
    
    • limit m,n 从m+1处开始,返回n行,m可以省略
    • 使用子查询的方法比较准确

    2limit倒数第n个

    select
    *
    from employees
    where hire_date = 
        (
            select 
             distinct hire_date 
             from employees
            order by hire_date desc
            limit 2,1
        )
    
    • 注意使用distinct进行去重

    3简单表连接

    1. select
      b.*,a.dept_no
      from dept_manager a left join salaries b
      on a.emp_no = b.emp_no
      where a.to_date='9999-01-01'
      and b.to_date='9999-01-01'
      ORDER BY b.emp_no
      
      • 注意过滤掉已经离职的领导

    4内连接inner join

      select last_name
      ,first_name
      ,dept_no
      from employees inner join dept_emp
      on employees.emp_no = dept_emp.emp_no
    
    • 注意使用内连接
    • 注意连接时,大表(数据量多的表要放在前面)

    5 左连接

    select last_name
    ,first_name
    ,dept_no
    from employees left join dept_emp
    on employees.emp_no = dept_emp.emp_no
    

    7简单分组

      -- 筛选变动次数超过15次的员工
      select 
      emp_no
      ,count()
      from salaries
      group by emp_no
      having count()>15
      
      -- 筛选加薪次数超过15次的员工
      select 
      a.emp_no
      , count(*) as t
      from salaries a 
      inner join salaries b
      on a.emp_no=b.emp_no
      where a.salaries < b.salaries
      group by a.emp_no
      having t>15
    
    • 在第二个语句中,使用了自连接来,筛选出加薪的员工
    • 当遇到一个同个字段进行比较时,应该使用自连接

    8结果去重distinct或group by

      --使用distinct进行去重
      select 
      distinct salary
      from salaries
      where to_date = '9999-01-01'
      order by salary desc
      
      -- 使用groupby进行去重
      select 
      salary
      from salaries
      where to_date = '9999-01-01'
      group by salary
      order by salary desc
    
    • 当数据量较大时,建议使用groupby进行去重
    • distinct会将select之后的所有字段合并进行去重
    • group by 和distinct在去重的时候,都会包含null,将所有的null看作一个组

    10null判断

      -- 使用join+is null
      select
      employees.emp_no
      from employees 
      left join dept_manager
      on employees.emp_no = dept_manager.emp_no
      where dept_manager.dept_no is  null
      
      -- 使用子查询
      SELECT emp_no FROM employees
      WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
    
    • 判断非空时,应该使用is null / is not null,不能使用!=null
    • 连接的性能要高于子查询,尽量使用连接而不是子查询

    11简单表连接

        select 
        a.emp_no
        ,b.emp_no
        from dept_emp a inner join dept_manager b
        on a.dept_no = b.dept_no
        where a.emp_no != b.emp_no
        and a.to_date = '9999-01-01' 
        AND b.to_date = '9999-01-01'
    

    12求分组字段最大值的信息

    • 方法1
        -- 使用子查询方法
        select
        d1.dept_no
        ,d1.emp_no
        ,s1.salary
        from salaries s1 inner join dept_emp d1
        on s1.emp_no = d1.emp_no
        AND d1.to_date='9999-01-01'
        AND s1.to_date='9999-01-01'
        where s1.salary in 
         (
             select max(s2.salary)
             from salaries s2 inner join dept_emp d2
             on s2.emp_no = d2.emp_no
             AND d2.to_date='9999-01-01'
             AND s2.to_date='9999-01-01'
             AND d2.dept_no = d1.dept_no -- 利用内查询获取外查询的部门编号
         )
        order by d1.dept_no asc
        
    
    • 方法2

      
      -- 使用开窗函数
      select
      t.dept_no
      ,t.emp_no
      ,t.salary
      from
          (
              select
              d.dept_no
              ,d.emp_no
              ,s.salary
              ,dense_rank() over (partition by d.dept_no order by s.salary desc) as rk
              from dept_emp d inner join salaries s
              on d.emp_no = s.emp_no
              where d.to_date='9999-01-01'
              and s.to_date='9999-01-01'
          ) t
      where t.rk = 1
      
      • 开窗函数使用方法链接
      • dense_rank() 对每个dept_no部门中的partition分组数据进行排序
      • row_number(),rank(),ntile(n),以及其他聚合函数

    15奇数判断

    1. select
      *
      

    from employees
    where emp_no%2=1
    and last_name != ‘Mary’
    order by hire_date desc
    ```

    • 不等于的几种表示方法
      • !=
      • is not
      • <>
    • 奇数的表示方法
      • x%2=1
      • x&1 使用了位运算

    16简单分组

    select 
    title
    ,avg(salary)as avg_salary
    from salaries s inner join titles t
    on s.emp_no = t.emp_no
    where s.to_date='9999-01-01'
    group by title
    order by avg_salary
    

    17排序取第n个值(order by + limit)

    select 
    emp_no
    ,salary
    from salaries
    order by salary desc
    limit 1,1
    

    18使用子查询或自连接进行排序

    -- 使用子查询
    select 
    s.emp_no
    ,s.salary
    ,e.last_name
    ,e.first_name
    from employees e 
    inner join salaries s
    on e.emp_no = s.emp_no
    where s.salary = 
        (
            select 
            max(salary)  -- 2 第二高工资
            from salaries
            where salary<>
            (
                select max(salary) -- 1查出最高工资
                from salaries
                where to_date='9999-01-01'
            )
        )
    and s.to_date = '9999-01-01'
    
    -- 使用自连接
    select s.emp_no
    , s.salary
    , e.last_name
    , e.first_name
    from salaries s 
    join employees e
    on s.emp_no = e.emp_no
    where s.salary =
        (
            select s1.salary
            from salaries s1 join salaries s2      -- 自连接查询
            on s1.salary <= s2.salary
            group by s1.salary                     -- 当s1<=s2链接并以s1.salary分组时一个s1会对应多个s2
            having count(distinct s2.salary) = 2   -- (去重之后的数量就是对应的名次)
        )
    and s.to_date = '9999-01-01'
    
    • 当不使用order by 时,可以使用自连接或者多个子查询

    19 三表连接

    select last_name
    ,first_name
    ,dept_name
    from employees e 
    left join dept_emp d
    on e.emp_no = d.emp_no
    left join departments 
    on d.dept_no = departments.dept_no
    
    • 多表连接时的执行顺序: 链接链接

    • 多表连接的时候,数据库内部执行的顺序,可能并不是我们写的顺序

    21筛选某一字段的变化

    select
    a.emp_no
    ,b.salary - a.salary as growth
    from salaries a 
    inner join employees e
        on e.emp_no=a.emp_no
        and e.hire_date = a.from_date
    inner join salaries b
        on a.emp_no = b.emp_no
    where b.to_date= '9999-01-01'
    order by growth
    
    • 第一次join筛选出入职时的工资

    • 第二次自连接筛选出,通过where筛选出当前的工资

    22简单连接+分组

    select 
    de.dept_no
    ,de.dept_name
    ,count(*) as sum
    from salaries s
    join dept_emp d
        on s.emp_no = d.emp_no
    right join departments de
        on d.dept_no = de.dept_no
    group by de.dept_name,de.dept_no
    order by de.dept_no
    
    • 是用两次表连接
    • 使用groupby 进行分组

    23窗口函数

    select
    emp_no
    ,salary
    ,dense_rank() over(order by salary desc) t_rank
    from salaries
    order by t_rank,emp_no
    
    • 使用一个开窗函数来统计排名,相等的看作一个排名

    24简单连接

    select 
    d.dept_no
    ,d.emp_no
    ,s.salary
    from salaries s
    join dept_emp d
        on s.emp_no = d.emp_no
        and s.to_date = '9999-01-01'
    where s.emp_no not in 
        (
            select
            emp_no
            from dept_manager de
            where de.to_date = '9999-01-01'
        )
    

    25多表复用

    select
    emp_no
    ,manager_no
    ,emp_salary
    ,manager_salary
    from 
        (
            select 
            s1.emp_no as emp_no
            ,s1.salary as emp_salary
            ,dep1.dept_no
            from salaries s1 
            join dept_emp dep1
            on s1.emp_no = dep1.emp_no
            where s1.emp_no not in 
                (
                    select emp_no
                    from dept_manager der
                    where der.to_date = '9999-01-01'
                )
            and s1.to_date = '9999-01-01'
        )tem1-- 非领导的工资
    join 
        (
            select 
            s2.emp_no as manager_no
            ,s2.salary as manager_salary
            ,dep2.dept_no
            from salaries s2
            join dept_emp dep2
                on dep2.emp_no = s2.emp_no 
            where s2.emp_no in 
                (
                    select emp_no
                    from dept_manager der
                    where der.to_date = '9999-01-01'
                )
            and s2.to_date = '9999-01-01'
        ) tem2 -- 部门领导的工资
    on tem1.dept_no = tem2.dept_no -- 按部门进行连接
    where emp_salary>manager_salary -- 筛选工资高低
    
    select de.emp_no,
           dm.emp_no as manager_no,
           s1.salary as emp_salary,
           s2.salary as manager_salary
    from dept_emp de,dept_manager dm,salaries s1,salaries s2
    where de.dept_no=dm.dept_no
    and de.emp_no=s1.emp_no
    and dm.emp_no=s2.emp_no
    and s1.to_date='9999-01-01'
    and s2.to_date='9999-01-01'
    and s1.salary>s2.salary
    
    • 使用多表复用可以将多个子查询融合在一起,减少代码量

    26简单分组

    select de.dept_no
    ,dep.dept_name
    ,title
    ,count(*)
    from dept_emp de
    join titles ti
        on ti.emp_no = de.emp_no
        and de.to_date = '9999-01-01'
    join departments dep
        on de.dept_no = dep.dept_no
    group by de.dept_no,title
    order by de.dept_no
    

    28虚表的使用

    select 
    c.name
    ,count(f.film_id) as film_num
    from film_category fc 
    join category c
        on fc.category_id = c.category_id
    join film f
        on fc.film_id = f.film_id
    where f.description like '%robot%'
    and fc.category_id in 
        (
            select category_id
            from film_category
            group by category_id
            having count(*)>=5
        )
    group by c.name
    
    
    • 使用子查询来约束类别,保证类别的电影数量>=5
    SELECT 
    c.name
    , COUNT(fc.film_id) 
    FROM
     	(
            select 
            category_id
            , COUNT(film_id) AS category_num 
            FROM film_category  
            GROUP BY category_id 
            HAVING count(film_id)>=5
        )  AS cc
    	,film AS f
    	, film_category AS fc
    	, category AS c
    WHERE  f.description LIKE '%robot%'
    AND f.film_id = fc.film_id
    AND c.category_id = fc.category_id
    AND c.category_id=cc.category_id
    
    • 使用虚表来限制电影类别

    29on和where

    select
    f.film_id
    ,title
    from film f 
    left join film_category fc
        on f.film_id = fc.film_id
    where category_id is null
    
    • 注意on和where的执行顺序,把条件放在on上,会得出不正确的结果
    • 当条件放在on上时,条件会和连接字段同时计算,从而导致结果不正确

    30简单查询

    select
    title
    ,description
    from film f
    join film_category fc
        on f.film_id = fc.film_id
    join category c
        on c.category_id = fc.category_id
    where name = 'Action'
    

    32 concat字符串拼接

    select
    concat(last_name,' ' ,first_name) name
    from employees
    

    33 创建表

    CREATE TABLE 
    IF NOT EXISTS actor ( -- 判断是否已存在
            actor_id smallint(5) NOT NULL PRIMARY KEY, -- 设置主键
            first_name varchar(45) NOT NULL,
            last_name varchar(45) NOT NULL,
            last_update date not null default current_timestamp -- 设置默认时间
            )
    
    • 当前日期:CURDATE(),CURRENT_DATE

    • 当前时间:now()语句开始执行的时间,sysdate()语句执行到的时间

    34批量插入

    insert into actor (actor_id,
                      first_name,
                      last_name,
                      last_update)
    values
        (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
        (2,'NICK','WAHLBERG','2006-02-15 12:34:33')
    

    35ignore insert

    insert ignore into actor(
                       actor_id
                        ,first_name
                        ,last_name
                        ,last_update)
    values 
        (
            '3','ED','CHASE','2006-02-15 12:34:33'
        )
        
    
    • insert into:插入数据,如果主键重复,则报错
    • insert repalce:插入替换数据,如果存在主键或unique数据则替换数据
    • insert ignore:如果存在主键或unique数据,则不进行insert。

    36select insert

    create table if not exists actor_name
    (
        first_name varchar(45) not null comment '名称',
        last_name varchar(45) not null comment '姓氏'
    );
    
    insert into actor_name(first_name
                          ,last_name)
    (select
        first_name
        ,last_name
        from actor )
    
    • 注意同时执行多条语句时,要加“ ;”号

    37创建索引

    alter table actor
    add unique index uniq_idx_firstname(first_name);
    alter table actor
    add index idx_lastname(last_name)
    
    -- 1通过create创建索引
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    USING [BTREE | HASH | RTREE] -- 索引数据类型,可以省略
    ON table_name (column_name [(length)] [ASC | DESC],...) -- 可以添加多个字段,并指定排序方式
    -- 索引的类型 PRIMARY KEY,KEY,UNIQUE或INDEX,当创建表时,若声明了主键或者unique,会自动生成索引
    
    -- 2 通过alter table创建索引
    ALTER TABLE tbl_name ADD [PRIMARY KEY/UNIQUE INDEX indexname/fulltext indexname] (col_list);
    
    -- 3删除索引
    DROP INDEX index_name ON tbl_name;
    // 或者
    ALTER TABLE tbl_name DROP INDEX index_name;
    ALTER TABLE tbl_name DROP PRIMARY KEY;
    

    38创建视图

    CREATE VIEW actor_name_view 
    AS
        SELECT first_name as first_name_v
        , last_name as last_name_v
        FROM actor
    
    CREATE 
       [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
    VIEW [database_name].[view_name] 
    AS
    	[SELECT  statement]
    -- 使用条件:
    -- SELECT语句可以在WHERE子句中包含子查询,但FROM子句中的不能包含子查询。
    -- SELECT语句不能引用任何变量,包括局部变量,用户变量和会话变量。
    -- SELECT语句不能引用准备语句的参数。
    
    

    39强制索引

    SELECT 
    * 
    FROM salaries 
    FORCE INDEX(idx_emp_no )
    WHERE emp_no = 10005
    
    • 使用FORCE INDEX(idx_name )可以指定查询时使用的索引

    40增加一列

    alter table actor
    add column create_date datetime not null default '2020-10-1 00:00:00'
    

    41创建触发器

    create trigger audit_log 
    after insert
    on employees_test
    for each row
    begin
        insert into audit(EMP_no,NAME)
        values(NEW.ID,NEW.NAME);-- 注意加分号,表示语句的结束
    end
    
    • 触发器创建:链接
    • 在触发语句中,使用new指代插入、更新后的行
    • old代表更新或者删除的行

    42条件删除

    delete 
    from titles_test
    where id not in
        (
            select id
            from
            (
                select min(id) as id
                from titles_test
                group by emp_no
            ) as a
        )
    
    • 注意,在删除或者跟更新时,条件中的子查询不能使用更新或者删除的表本身,必须使用别名的方式,将子查询存储为新表

    • DELETE FROM customers
      WHERE country = 'France'
      ORDER BY creditLimit
      LIMIT 5;
      
    • 使用order by 和limit也可以约束删除的范围

    • 当删除表中全部数据时,使用truncate table table_name更加高效

    43更新表

    update  titles_test
    set to_date = null,
        from_date='2001-01-01'
    where to_date = '9999-01-01'
    

    44 更新表(使用replace函数)

    UPDATE titles_test
    SET emp_no = 
        REPLACE(emp_no, 10001, 10005)
    WHERE id = 5
    
    replace into titles_test
    select
    5
    , 10005
    , title
    , from_date
    , to_date
    from titles_test
    where id = 5;
    
    1. 类似与insert
    REPLACE INTO table_name(column_list)
    VALUES(value_list)
    
    1. 类似于update
    REPLACE INTO table
    SET column1 = value1,
        column2 = value2
    
    1. insert select
    REPLACE INTO cities(name,population)
    SELECT name,population FROM cities 
    WHERE id = 1
    

    45alter的使用

    alter table titles_test
    rename to titles_2017
    
    • alter的常用方法:

    • ALTER TABLE 表名 ADD 列名/索引/主键/外键等;

    • ALTER TABLE 表名 DROP 列名/索引/主键/外键等;

    • ALTER TABLE 表名 ALTER 仅用来改变某列的默认值;

    • ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名;

    • ALTER TABLE 表名 RENAME TO/AS 新表名;

    • ALTER TABLE 表名 MODIFY 列的定义但不改变列名;

    • ALTER TABLE 表名 CHANGE 列名和定义都可以改变。

    46 外键

    alter table audit
    add foreign key fk (emp_no)
    references employees_test(id)
    

    外键简介:https://www.yiibai.com/mysql/foreign-key.html

    外键约束语法:

    CONSTRAINT constraint_name 
    FOREIGN KEY foreign_key_name (columns) 
    REFERENCES parent_table(columns) 
    ON DELETE action 
    ON UPDATE action
    

    action可以是CASCADE创建级联删除或者级联更新

    action是RESTRICT或者no action时,不允许删除或者更新

    action是SET NULL时,删除或更新父表时,子表被设置为null

    48条件更新

    update salaries
    set
        salary=1.1*salary
    where
        to_date = '9999-01-01'
    and emp_no in
        (
            select emp_no
            from emp_bonus
        )
    

    50字符串拼接

    select concat(last_name,'\'',first_name)
    from employees
    

    51字符串长度与替换

    select 
    char_length('A,10,B')- char_length(
        replace('10,A,B',',','')
    );
    
    • 使用length统计字符串长度时,统计的是字节长度,当编码不同时,容易出错,使用char_length更加准确

    52截取字符串

    select first_name
    from employees
    order by right(first_name,2)	
    
    • 常见的字符串函数

    • 字符串处理函数

      • 大小写;

      ​ Lower(列名)

      ​ Upper(列名)

      • 字符串定位

      ​ POSITION(字符 IN 列名) mysql

      ​ PATINDEX(’%s1%’, s2) sqlserver

      • 合并

      ​ CONCAT(列名, ’ is in ', 列名…)

      • 替换

      ​ replace(列名,旧字符串,新字符串)

      • 取左

      ​ left (列名,位数) 返回字符串列中左边多少位的字符

      • 匹配单引号等特殊字符

      ​ 使用转义字符+特殊字符

      • 提取字符串

      ​ SUBSTRING(列名 , 开始位 ,字符长度 ) mysql

    53分组字符串拼接

    select
    dept_no
    ,group_concat(emp_no separator ',')
    from dept_emp
    group by dept_no
    order by dept_no
    
    • group_concat函数使用方法:链接

    • 使用语法:

    • GROUP_CONCAT(DISTINCT expression
          ORDER BY expression
          SEPARATOR sep);
      

      按照orderby的顺序,用sep分隔符,连接每个group中的字符串

    54剔除最大最小值

    select 
    avg(salary) as avg_salary
    from salaries
    where to_date = '9999-01-01'
    and salary not in
        (
            select 
            max(salary)
            from salaries
            where to_date = '9999-01-01'
        )
    and salary not in
        (
            select 
            min(salary)
            from salaries
            where to_date = '9999-01-01'
        )
    
    • 使用聚合函数
    SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary 
    FROM salaries 
    where to_date = '9999-01-01';
    

    55 分页显示

    select 
    *
    from
    employees
    order by emp_no
    limit 5,5
    
    • limit 每页显示数量*(第n页 -1),每页显示数量

    56多表连接

    select
    distinct
    e.emp_no
    ,d.dept_no
    ,btype
    ,received
    from employees e
    inner join dept_emp d
        on e.emp_no = d.emp_no
    left join emp_bonus b
        on d.emp_no = b.emp_no
    

    57exists

    select
    * 
    from employees e
    where
        not exists
        (
            select *
            from dept_emp d
            where d.emp_no = e.emp_no
        )
    
    • exists和in通常可以实现相同的功能
    • 当从表(子查询的表)很大时,通常使用in

    59case语句

    select
    b.emp_no
    ,first_name
    ,last_name
    ,btype
    ,salary
    , case when btype = 1 
                then salary*0.1
           when btype = 2 
                then salary*0.2
            else salary * 0.3
        end
        as bonus
    from emp_bonus b
    left join employees e
        on b.emp_no = e.emp_no
    join salaries s
        on e.emp_no = s.emp_no
    where s.to_date = '9999-01-01'
    
    • case语法

    • CASE  case_expression
         WHEN when_expression_1 THEN commands
         WHEN when_expression_2 THEN commands
         ...
         ELSE commands
      END CASE
      
      

    60累计和

    select emp_no
    ,salary
    ,sum(salary) over(order by emp_no) as running_total
    from salaries
    where to_date = '9999-01-01'
    
    • 累计和求法:sum(xx) over (order by xx)

    61开窗函数排序

    SELECT
        e.first_name
    FROM employees e JOIN
    (
        SELECT 
            first_name
            , ROW_NUMBER() OVER(ORDER BY first_name ASC) AS  r_num
        FROM employees
    ) AS t 
    ON e.first_name = t.first_name
    WHERE t.r_num % 2 = 1;
    

    62分组筛选

    select
    number
    from
    grade
    group by number
    having count(*)>2
    

    63开窗函数排序

    select
    id
    ,number
    ,dense_rank() over(order by number desc) as t_rank
    from passing_number
    order by t_rank,id
    
    • 不要忘记over函数后的排序方式desc

    64左连接

    select
    p.id
    ,name
    ,content
    from person p
    left join task t
    on p.id = t.person_id
    

    65case与avg求组内某个字段数值比例

    select
    e.date
    , round(AVG(
                    case e.type when 'no_completed' then 1
                    else 0
                    end
                )
            ,3
           )as p
    from email e 
    join `user` u1
        on e.receive_id = u1.id
    join `user` u2
        on e.send_id = u2.id
    where u1.is_blacklist = 0
        and u2.is_blacklist = 0
    group by e.date
    order by e.date
    

    66开窗函数与分组

    select 
    user_id
    ,max(`date`) as d
    from login
    group by user_id
    order by user_id
    
    • 使用开窗函数
    select distinct
        user_id,
        last_value(date) 
            over(partition by user_id -- 按照user_id进行分组
                 order by date -- 组内按照date进行排序
                 rows between current row and unbounded following -- 计算时,从当前行到组内最后一行
                ) as d
    from login;
    
    window_function_name(expression) 
        OVER (
            [partition_defintion]
            [order_definition]
            [frame_definition]
        ) 
    
    frame_definition就是在partition内,取每一行的时候,在组内计算时,选择的计算范围
    
    
    

    image-20210214204524090

    67开窗函数求最晚日期

    /*开窗函数分组求最大时间,然后用子查询筛选*/
    select n.un u_n, n.cn c_n,n.d 
    from(
            select u.name un
                    , c.name cn
                    , l.date,
                    (max(l.date) over(partition by l.user_id)) d
            from login l,user u,client c
            where l.user_id=u.id
            and c.id=l.client_id
        ) n
    where n.date=n.d
    order by u_n;
    

    68求初始注册日留存率

    • 使用开窗函数
    select
    round(
            (
                select  
                count(*) -- 通过where找到第二天仍然登陆的用户记录数
                from
                    (
                        select -- 1找到初始登陆日期
                        user_id
                        ,l.date
                        ,min(date) over(partition by user_id) as first_date
                        from login l
                    ) as temp1
                where temp1.date = date_add(first_date,interval 1 day)
            )
        /
            (
                select -- 统计用户总数
                count(distinct user_id)
                from login
            )
        ,3)
    as p 
    
    • 使用where子查询
    SELECT
    ROUND(
        COUNT(DISTINCT user_id)*1.0  
                /( -- 总用户量
                SELECT 
                 COUNT(DISTINCT user_id) 
                 FROM login
                )
        , 3)
    FROM login
    WHERE (user_id, date) -- 筛选出所有第二天登陆的用户
    IN
    (    -- 如果第二天登陆的日期和id
        SELECT user_id
        , DATE_ADD(MIN(date),INTERVAL 1 DAY) 
        FROM login GROUP BY user_id
    );
    

    date_add函数的使用:链接

    DATE_ADD(start_date, INTERVAL expr unit)
    
    常用unit
    second,minute,hour,day,month,year
    

    69使用row_number 来统计每天新注册人数

    select 
    date
    ,sum(
        case tem.rk
        when tem.rk = 1 then 1
        else 0
        end
    ) as new
    from
    (
        select
        user_id
        ,date
        ,row_number() over(partition by user_id order by `date`) as rk
        from login
    ) as tem
    group by tem.date
    

    70按日期统计留存率

    select 
    log_today.date
    ,round(count(distinct log_nextday.user_id)-- 当天新用户的数量
            /
            count(distinct log_today.user_id)-- 第二天重新登录的新用户数量
           ,3) as p
    from login as log_today
    left join(-- 注意要使用左连接
                    select -- 1 将日期向前移动一天,从而进行表连接,判断第二天留存率
                    user_id
                    ,date_sub(date,interval 1 day) as date
                    from login
                ) as log_nextday
        on log_today.date = log_nextday.date
        and log_today.user_id = log_nextday.user_id
    where (log_today.user_id,log_today.date) -- 筛选每天登陆的新用户,就是这里把没有新用户登陆的天给筛选掉了,因此需要union
            in (
                select user_id
                ,min(date)
                from login
                group by user_id
            )
    group by log_today.date
    union -- 将没有新用户的登陆的天,补充留存率为零
    select date 
    ,0.000 as p
    from login
    where date not in
        (
            select
            min(date)
                from login
                group by user_id
        )
    order by date
    

    71按日期和用户的累计和

    select
    u.name as u_n
    ,login.date
    ,ps_num
    from 
        (
            select
            user_id
            ,date
            ,sum(number) over(partition by user_id order by date) as ps_num
            from passing_number
        ) as pas
        join login
            on login.user_id = pas.user_id
            and login.date = pas.date
        join `user` u 
        on u.id = login.user_id
    order by date,u_n
    

    72普通分组求平均

    select
    job
    ,round(avg(score),3) as avg_score
    from
    grade
    group by job
    order by avg_score desc
    

    73大于平均值筛选

    select
    g1.*
    from
    grade g1
    join (
        select
        id,
        avg(score) over(partition by job) avg_score
        from grade
    ) g2
    on g1.id = g2.id
    where g1.score > g2.avg_score
    order by id
    

    74求分组最高的两个值

    select 
    g.id
    ,l.name
    ,g.score
    from grade g 
    join `language` l
        on g.language_id = l.id
    join (
        select
        id
        ,dense_rank() over(partition by language_id order by score desc) as rk
        from grade
    ) as tem
        on tem.id = g.id
    where tem.rk in (1,2)
    order by name asc,score desc
    

    75求中位数的位置

    select
    job
    ,floor(( count(*) + 1 )/ 2 ) AS `start`
    ,floor(( count(*) + 2 )/ 2 ) AS 'end'
    from grade
    group by job
    order by job
    
    • 取整函数
      • 四舍五入round()
      • 向上取整ceiling()
      • 向下取整floor()

    76自连接求中位数

    select
    g1.id
    ,g1.job
    ,g1.score
    ,g2.rk as t_rank
    from grade g1
    join (
            select 
            id
            ,row_number() over(partition by job order by score desc) as rk-- 每条记录的排序
            ,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每个job的记录数量
            from grade
        ) as g2
    on g1.id = g2.id
    where -- 判断中位数
        g2.rk = floor((g2.num + 1 )/ 2 )
        or g2.rk = floor((g2.num + 2 )/ 2 )
    order by id
    

    er_id
    order by date,u_n

    
    ## 72普通分组求平均
    
    ```mysql
    select
    job
    ,round(avg(score),3) as avg_score
    from
    grade
    group by job
    order by avg_score desc
    

    73大于平均值筛选

    select
    g1.*
    from
    grade g1
    join (
        select
        id,
        avg(score) over(partition by job) avg_score
        from grade
    ) g2
    on g1.id = g2.id
    where g1.score > g2.avg_score
    order by id
    

    74求分组最高的两个值

    select 
    g.id
    ,l.name
    ,g.score
    from grade g 
    join `language` l
        on g.language_id = l.id
    join (
        select
        id
        ,dense_rank() over(partition by language_id order by score desc) as rk
        from grade
    ) as tem
        on tem.id = g.id
    where tem.rk in (1,2)
    order by name asc,score desc
    

    75求中位数的位置

    select
    job
    ,floor(( count(*) + 1 )/ 2 ) AS `start`
    ,floor(( count(*) + 2 )/ 2 ) AS 'end'
    from grade
    group by job
    order by job
    
    • 取整函数
      • 四舍五入round()
      • 向上取整ceiling()
      • 向下取整floor()

    76自连接求中位数

    select
    g1.id
    ,g1.job
    ,g1.score
    ,g2.rk as t_rank
    from grade g1
    join (
            select 
            id
            ,row_number() over(partition by job order by score desc) as rk-- 每条记录的排序
            ,count(id) over(partition by job rows between unbounded preceding and unbounded following) as num -- 每个job的记录数量
            from grade
        ) as g2
    on g1.id = g2.id
    where -- 判断中位数
        g2.rk = floor((g2.num + 1 )/ 2 )
        or g2.rk = floor((g2.num + 2 )/ 2 )
    order by id
    
    展开全文
  • 一些有趣的SQL题目

    千次阅读 2017-09-20 10:29:47
    题目来自: 点击打开链接 下面是本人的答案: --一下语句中用到的表tb均指这个测试表 --1、你猜下面这条语句能得到什么结果,这个很常用 if object_id('tb') is not null drop table tb go select getdate() ...
  • 13道SQL题目

    2015-05-17 11:45:53
    13道SQL题目,童鞋们下载后先别看答案自己试着做一下,看ok不?
  • 中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目
  • 考试常考的SQL题目---无私奉献了!希望对大家有帮助
  • 高级查询sql题目

    2013-10-15 10:27:28
    .zip,高级查询题目,指导学习部分参考答案.sql,高级查询复习参考答案.sql
  • 题目详解SQL 实践 本repo涵盖SQL问题解决方案,以提升技能为目的,因此根据其涵盖的概念对问题进行选择和分类,并与查询一起进行分析和解释。 问题来自 LeetCode、Hackerrank 或其他来源。 概括 这里列出了我们可能...
  • –原题目 https://blog.csdn.net/tu451953337/article/details/45147757 --create table t_student(stuid int primary key,name varchar(20), age int, sex char(1)); -- ---------------------------- -- ...
  • 中兴SQL题目中兴SQL题目中兴SQL题目中兴SQL题目
  • SQL题目-次日留存率

    2021-09-21 12:17:29
    题目 解答 -- 第一步 求日期间隔 select a.user_id,a.log_date as log_d,date_diff(a.log_date,b.log_date) as day_diff from userlogs a left join userlogs b on a.user_id=b.user_id --第二步 算留存率 ...
  • SQL题目练习---三表联查

    千次阅读 2022-06-02 21:15:21
    分析:1)本质是一个三表联查问题,SQL语句为: select * from A inner join 表B on 表A.列1=表B.列2 inner join 表C on 表B.列1=表C.列1 2)降序问题语法为:order by 字段 desc; -- 查询橘右京的学生所有分数...
  • SQL 经典50题(题目+解答)(1)

    千次阅读 2022-03-07 16:12:20
    SQL 经典50题(题目+解答)(1)
  • 微软SQL认证考试题目461 微软SQL认证考试题目461 微软SQL认证考试题目461 微软SQL认证考试题目461 微软SQL认证考试题目461
  • SQL题目:计算连续登录的最大天数

    千次阅读 2021-01-14 22:59:07
    #SQL题目:计算连续登录的最大天数 ##已知有一张简单的用户登录表 uid date u1 2020/9/1 u1 2020/9/2 u1 2020/9/3 u1 2020/9/8 u1 2020/9/9 u2 2020/9/1 u2 2020/9/2 ##计算结果如下: ...
  • ORACLE SQL练习题(带答案)ORACLE SQL练习题(带答案)
  • sql 题目练习

    千次阅读 2013-11-09 23:29:17
    第一题比较简单,查询出销售表中,销售额大于本地区平均水平的记录,用一条sql语句就搞定了。 SELECT * FROM sales s,(SELECT region,AVG(total) avge from sales GROUP BY region) AS avge WHERE s.region=avge....
  • 几个很好的SQL题目

    2008-09-22 12:30:33
    几个很好的SQL题目,都是得好好想一想的,希望大家喜欢
  • sql题目: (1)Customer 客户表: cid 客户id,name 姓名,age 年龄,gender 性别,company_name 所属公司,create_at 注册时间 (2)Products产品表: pid 产品id,name 产品名称,type 产品类型 (3)Orders订单...
  • 如何快速掌握MYSQL?附牛客网所有SQL题目详解

    万次阅读 多人点赞 2021-10-17 23:37:20
    如果你目前啥也不会,只停留在知道SELECT用于查询的层面的话,又想要快速掌握MYSQL,那么刷题,并且过程中不会什么补什么,就是巩固和提升自己的SQL语言能力最快捷的方法。 补充:牛客网相对于LeetCode而言题目较少...
  • oracle数据库sql练习题(含答案)用于练习增删改查操作。
  • SQL-Server-期末考试题目及答案.docSQL-Server-期末考试题目及答案.docSQL-Server-期末考试题目及答案.doc
  • 很实用,很详细,是我们上课时用的题目

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 79,624
精华内容 31,849
关键字:

sql题目

友情链接: dayincms.zip