来自某公司的面试题。对题目及数据做一定程度的修改。这里数据采用了中国福利彩票官方的数据。 data.17500.cn/ssq_asc.txt

处理入库之后如下表所示：

表结构：

CREATE TABLE `open_tickle` (
  `id` int NOT NULL,
  `dt` varchar(14) DEFAULT NULL,
  `n1` tinyint DEFAULT NULL,
  `n2` tinyint DEFAULT NULL,
  `n3` tinyint DEFAULT NULL,
  `n4` tinyint DEFAULT NULL,
  `n5` tinyint DEFAULT NULL,
  `n6` tinyint DEFAULT NULL,
  `n7` tinyint DEFAULT NULL,
  `n8` tinyint DEFAULT NULL,
  `n9` tinyint DEFAULT NULL,
  `n10` tinyint DEFAULT NULL,
  `n11` tinyint DEFAULT NULL,
  `n12` tinyint DEFAULT NULL,
  `n13` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

题目

n1列出现次数最多的中奖号码
temp表N列为1-36，查询n1列在temp表中没有出现过的数字
n1-n13中出现次数最多的中奖号码
n1-n13中所有中奖号码中，1-36没有出现过的数字

解答

1. 对n1列计数后降序排序取top 1即可。


select n1 from open_tickle group by n1 order by count(1) desc limit 1;

2. 外连接方法可以处理

open_tickle作为左表，temp作为右表，右外连接，可以查询出temp中的数字在open_tickle中n1列没有出现的数字代码如下：

select temp.n
from (select n1 from open_tickle group by n1) as tb
right outer join temp on tb.n1=temp.n where tb.n1 is null
;

图示：

3.

方法一：将n1-n13这13列用union合并为一列后再求出现次数最多的

select n from
(
select n1 as n, count(1) as cnt from open_tickle group by n1
union all
select n2 as n, count(1) as cnt from open_tickle group by n2
union all
select n3 as n, count(1) as cnt from open_tickle group by n3
union all
select n4 as n, count(1) as cnt from open_tickle group by n4
union all
select n5 as n, count(1) as cnt from open_tickle group by n5
union all
select n6 as n, count(1) as cnt from open_tickle group by n6
union all
select n7 as n, count(1) as cnt from open_tickle group by n7
union all
select n8 as n, count(1) as cnt from open_tickle group by n8
union all
select n9 as n, count(1) as cnt from open_tickle group by n9
union all
select n10 as n, count(1) as cnt from open_tickle group by n10
union all
select n11 as n, count(1) as cnt from open_tickle group by n11
union all
select n12 as n, count(1) as cnt from open_tickle group by n12
union all
select n13 as n, count(1) as cnt from open_tickle group by n13) as tb 
group by n order by sum(cnt) desc limit 1;

方法二：通过行专列的方法转为一列再求解，但是MySQL不支持pivot跟unpivot。这里就不贴代码了

4. 需要先生成一个1-36的表，然后两个表进行join

mysql8的生成1-36的表的代码：


with recursive c(n) AS ( select 1 union ALL select n + 1 from c where n < 50 ) 
select * from c;

参考自：blog.csdn.net/u010520724/…

mysql5的话用通过变量及union生成

SELECT 
    @xi:=@xi+1 as xc 
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION select 6) xc1, 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION select 6) xc2,  
    (SELECT @xi:=0) xc0
;

参考自：www.cnblogs.com/gentleschol…

那么可以得出解题的sql：

with recursive c(n) AS
(
select 1
union ALL
select n + 1
from c
where n < 36
)
select
    c.n
from c left outer join
(select n1 as n from open_tickle
union
select n2 as n from open_tickle
union
select n3 as n from open_tickle
union
select n4 as n from open_tickle
union
select n5 as n from open_tickle
union
select n6 as n from open_tickle
union
select n7 as n from open_tickle
union
select n8 as n from open_tickle
union
select n9 as n from open_tickle
union
select n10 as n from open_tickle
union
select n11 as n from open_tickle
union
select n12 as n from open_tickle
union
select n13 as n from open_tickle) as tb 
on c.n=tb.n where tb.n is null
;

个人能力有限，以上sql应该有更优雅的解法，如果各位技术大大有更好的解法可以分享一下的。谢谢

每日SQL一练#2023101801

题目

解答

1. 对n1列计数后降序排序取top 1即可。

2. 外连接方法可以处理

3.

4. 需要先生成一个1-36的表，然后两个表进行join