On query a table to repeat the number of occurrences-CodePudding

a few days ago to the written test, test database, encountered such a title: a record of past record form CP, there are eight fields, the first field is the clauses of the lottery, the rest of the seven fields represent the seven of the lottery number, as shown in figure, the problem is that (1) write a SQL query out the lottery record Numbers with the highest occurrences in the table (2) write a SQL query of the lottery record did not exist in the table number, do not implement the idea, didn't write it, have a great god help have a look at how to implement this two SQL statements

CodePudding user response:

The above two phones back is empty
Hope to you have inspired
He had set a table experiment of the
Select * from
(
Select a, count zs (a)
The from
(
Select a1 a from aa
Union all
Select a2 from aa a
Union all
Select a3 a from aa
)
Group by a order by zs desc
)
Where rownum<2

CodePudding user response:

The Numbers 1, appear the most query out all the columns of figures, and then compare the count number, take out the maximum count the number of digital line,
2, will appear Numbers are the rules? Otherwise it wouldn't have an infinite number of Numbers? Have the rules is to query out all these Numbers, then does not exist within the rules of Numbers,

CodePudding user response:

The figure you provided is too small, number is too much,
You listed in the table below fields, provides several groups of test data, and instructions to achieve the effect of, such ability convenient SQL statements,

CodePudding user response:

So easy to write SQL statements,

CodePudding user response:

Line turn column is easy,
The double chromosphere is 1-33 red ball, basketball 1-16, no area of share out bonus blue statistics with
1

 WITH TMP AS 
(SELECT the 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 interchange, TRUNC (DBMS_RANDOM. VALUE (1, 34)) N5, 
TRUNC (DBMS_RANDOM. VALUE (1, 34)) N6, TRUNC (DBMS_RANDOM. VALUE (1, 17)) N7 
The FROM DUAL 
CONNECT BY LEVEL & lt;=20) 
SELECT V, C 
The FROM (SELECT V, COUNT (1) C 
The FROM (SELECT V 
The FROM TMP UNPIVOT (V FOR COL IN (N1, N2 and N3 and N4 interchange, N5 and N6, N7))) 
GROUP BY V 
The ORDER BY DESC) 
 CWHERE ROWNUM=1

The second question

 WITH TMP AS 
(SELECT the 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 interchange, TRUNC (DBMS_RANDOM. VALUE (1, 34)) N5, 
TRUNC (DBMS_RANDOM. VALUE (1, 34)) N6, TRUNC (DBMS_RANDOM. VALUE (1, 17)) N7 
The FROM DUAL 
CONNECT BY LEVEL & lt;=20) 
The SELECT LEVEL AS L 
The FROM DUAL 
CONNECT BY LEVEL & lt;=33 
MINUS 
SELECT DISTINCT V 
The FROM (SELECT V 
The FROM TMP UNPIVOT (V FOR COL IN (N1, N2 and N3 and N4 interchange, N5 and N6, N7)))

CodePudding user response:

Upstairs to the second SQL can be optimized under:

 
WITH TMP AS 
(SELECT the 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 interchange, TRUNC (DBMS_RANDOM. VALUE (1, 34)) N5, 
TRUNC (DBMS_RANDOM. VALUE (1, 34)) N6, TRUNC (DBMS_RANDOM. VALUE (1, 17)) N7 
The FROM DUAL 
CONNECT BY LEVEL & lt;=20) 
The SELECT LEVEL AS L FROM DUAL CONNECT BY LEVEL & lt;=33 
MINUS 
SELECT V FROM TMP UNPIVOT (V FOR COL IN (N1, N2 and N3 and N4 interchange, N5 and N6, N7))

CodePudding user response:

Some people don't see what mean, may I described also has a problem, this is an example of the lottery: a table for storing information about the record of the lottery, lottery have periods and corresponding seven number, and then the table is designed to include eight fields, the first field's period according to store the lottery, the rest of the seven fields respectively record the seven Numbers, is the scope of digital contains 1-36;
Request 1: write a SQL statement, in to provide the lottery periods, found out the lottery record Numbers in the most times;
Requirement 2: write a SQL statement, in to provide the lottery periods, found out the lottery record has never been seen in the digital
The above several great god wrote, have a more simple way

CodePudding user response:

references 9 f xiehx_java response:

some people don't see what do you mean, can I describe the problem, this is an example of the lottery: a table for storing information about the record of the lottery, lottery is applicable and the corresponding number seven, and then the table is designed to include eight fields, the first field's period according to store the lottery, the rest of the seven fields respectively record the seven Numbers, is the scope of digital contains 1-36;
Request 1: write a SQL statement, in to provide the lottery periods, found out the lottery record Numbers in the most times;
Requirement 2: write a SQL statement, in to provide the lottery periods, found out the lottery record has never been seen in the digital
The above a few great god wrote, are there any easier way

The above sentence and then write out, more simple... The with clause is to build a test data