关于面试中的mysql试题1

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

---->

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、求某个时间段内彩票开奖号码次数最多的数字　　

--->

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、计算博物馆最长连续闭馆时间

--->

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、将成绩分数转化为优秀良好及格等

 --->

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