上面两个拿手机回的是空的 希望对你有启发 自己设置了一个三列的表实验了一下 select * from ( select a,count(a) zs from ( select a1 a from aa union all select a2 a from aa union all select a3 a from aa ) group by a order by zs desc ) where rownum<2
行转列就简单了, 双色球是 红球1-33, 篮球1-16, 不区分红蓝一起统计的话 第一题 WITH TMP AS (SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7 FROM DUAL CONNECT BY LEVEL <= 20) SELECT V, C FROM (SELECT V, COUNT(1) C FROM (SELECT V FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7))) GROUP BY V ORDER BY C DESC) WHERE ROWNUM = 1第二题 WITH TMP AS (SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7 FROM DUAL CONNECT BY LEVEL <= 20) SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= 33 MINUS SELECT DISTINCT V FROM (SELECT V FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7)))
楼上第二个SQL可以再优化下:WITH TMP AS (SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7 FROM DUAL CONNECT BY LEVEL <= 20) SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= 33 MINUS SELECT V FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7))
希望对你有启发
自己设置了一个三列的表实验了一下
select * from
(
select a,count(a) zs
from
(
select a1 a from aa
union all
select a2 a from aa
union all
select a3 a from aa
)
group by a order by zs desc
)
where rownum<2
2,会出现的数字是不是有规则?不然那岂不是有无限多的数字?有规则的话就是查询出所有这些数字,然后不存在于规则内的数字就行了。
你列下表字段,提供几组测试数据,以及说明要达到的效果。这样才方便些SQL语句。
双色球是 红球1-33, 篮球1-16, 不区分红蓝一起统计的话
第一题
WITH TMP AS
(SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7
FROM DUAL
CONNECT BY LEVEL <= 20)
SELECT V, C
FROM (SELECT V, COUNT(1) C
FROM (SELECT V
FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7)))
GROUP BY V
ORDER BY C DESC)
WHERE ROWNUM = 1第二题
WITH TMP AS
(SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7
FROM DUAL
CONNECT BY LEVEL <= 20)
SELECT LEVEL AS L
FROM DUAL
CONNECT BY LEVEL <= 33
MINUS
SELECT DISTINCT V
FROM (SELECT V
FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7)))
(SELECT DATE '2017-10-10' - LEVEL AS T_DATE, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N1,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N2, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N3,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N4, TRUNC(DBMS_RANDOM.VALUE(1, 34)) N5,
TRUNC(DBMS_RANDOM.VALUE(1, 34)) N6, TRUNC(DBMS_RANDOM.VALUE(1, 17)) N7
FROM DUAL
CONNECT BY LEVEL <= 20)
SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL <= 33
MINUS
SELECT V FROM TMP UNPIVOT(V FOR COL IN(N1, N2, N3, N4, N5, N6, N7))
要求1:写一个sql语句,在给提供的开奖期数中,查出开奖记录中出现次数最多的数字;
要求2:写一个sql语句,在给提供的开奖期数中,查出开奖记录中从来就没有出现过的数字
上面的几个大神写了,有更简单的方式吗