关于面试中的mysql试题1

落日映苍穹つ 2021-10-23 06:32 188阅读 0赞

1、按时间列出每天输赢的量

![1204721-20190720232936928-1962187879.png][]\---->![1204721-20190720233503142-160160013.png][]

SELECT W_Time,COUNT(CASE WHEN WoR='赢' THEN 1 END) AS 赢, COUNT(CASE WHEN WoR='输' THEN 1 END) AS 输
    FROM WinOrGohome GROUP BY W_time

2、求某个时间段内彩票开奖号码次数最多的数字

![1204721-20190720234043963-1381443602.png][]\--->![1204721-20190720234105142-996169773.png][]

SELECT b.n1 FROM(
    	SELECT COUNT(a.n1) AS numCount, a.n1 AS n1 FROM(
    		SELECT n1 FROM test_t3 UNION ALL
    		SELECT n2 FROM test_t3 UNION ALL
    		SELECT n3 FROM test_t3 UNION ALL
    		SELECT n4 FROM test_t3 UNION ALL
    		SELECT n5 FROM test_t3 ) AS a
    		GROUP BY a.n1 ORDER BY numCount DESC
    	) AS b
    WHERE b.numCount=(
    	SELECT MAX(b.numCount) FROM(
    		SELECT COUNT(a.n1) AS numCount, a.n1 AS n1 FROM(
    			SELECT n1 FROM test_t3 UNION ALL
    			SELECT n2 FROM test_t3 UNION ALL
    			SELECT n3 FROM test_t3 UNION ALL
    			SELECT n4 FROM test_t3 UNION ALL
    			SELECT n5 FROM test_t3 ) AS a
    		GROUP BY a.n1 ORDER BY numCount DESC
    	) AS b
    )

3、计算博物馆最长连续闭馆时间

![1204721-20190721144949674-67899566.png][]\--->![1204721-20190721145109607-1281888060.png][]

SELECT  MAX(a.dataOpen-b.dataOpen-1) AS result FROM
    	(SELECT (@arow:=@arow+1) AS rownum, dataOpen FROM test_t1, (SELECT @arow:=0) AS table1 ) AS a,
    	(SELECT (@brow:=@brow+1) AS rownum, dataOpen FROM test_t1, (SELECT @brow:=1) AS table1 ) AS b
    WHERE a.rownum=b.rownum

4、将成绩分数转化为优秀良好及格等

![1204721-20190721150242972-458889302.png][]\--->![1204721-20190721150224186-430360139.png][]

SELECT C_Name,(CASE WHEN 语文>=90 THEN '优秀' WHEN 语文<=60 THEN '不及格' ELSE '良好' END) AS 语文,
    	(CASE WHEN 数学>=90 THEN '优秀' WHEN 数学<=60 THEN '不及格' ELSE '良好' END) AS 数学,
    	(CASE WHEN 英语>=90 THEN '优秀' WHEN 英语<=60 THEN '不及格' ELSE '良好' END) AS 英语
    FROM score2

转载于:https://www.cnblogs.com/stupid-chan/p/11221255.html

[1204721-20190720232936928-1962187879.png]: https://img2018.cnblogs.com/blog/1204721/201907/1204721-20190720232936928-1962187879.png
[1204721-20190720233503142-160160013.png]: /images/20211023/3aaba222f4e74270bda23ab08f66bdd6.png
[1204721-20190720234043963-1381443602.png]: https://img2018.cnblogs.com/blog/1204721/201907/1204721-20190720234043963-1381443602.png
[1204721-20190720234105142-996169773.png]: /images/20211023/68b9da48868a43059fef55431f0fff06.png
[1204721-20190721144949674-67899566.png]: https://img2018.cnblogs.com/blog/1204721/201907/1204721-20190721144949674-67899566.png
[1204721-20190721145109607-1281888060.png]: /images/20211023/83bf2f74fa904a15ab8252f61cb7f860.png
[1204721-20190721150242972-458889302.png]: https://img2018.cnblogs.com/blog/1204721/201907/1204721-20190721150242972-458889302.png
[1204721-20190721150224186-430360139.png]: /images/20211023/d0c0b67ceb4b427599fc2cd5e5f0eaf9.png