Table is as follows:


Output is

Image content just shows the brackets, don't show


O great god appeared

CodePudding user response:

 with a as (
Select '1' FXH, fa 'c', 'e' fb from dual 
The union 
Select FXH '2', 'c' fa, 'e' fb from dual 
The union 
Select '3' FXH, fa 'c', 'e' fb from dual 
The union 
Select FXH '4', 'c' fa, 'f' fb from dual 
The union 
Select the '5' FXH, fa 'c', 'f' fb from dual 
The union 
Select '6' FXH, fa 'd', 'g' fb from dual 
The union 
Select the '7' FXH, fa 'd', 'g' fb from dual 
The union 
Select the '8' FXH, fa 'd', 'g' fb from dual 
The union 
Select the '9' FXH, 'd' fa, 'h' fb from dual 
) 

SELECT b. a. as "the first column," b. b as "appear the most", ct as "the number of occurrences of the FROM (
The SELECT Max (ct) over (partition by fa) maxct, a. * FROM (
SELECT count (fb) over (partition by fa, fb) ct, fa, fb, row_number () over (partition by fa, fb order by fa) rn, FXH 
FROM a 
) where a a.r n=1 
B) where maxct=ct 

Recent brain a little smoke, might be a bit complicated, but you can complete your demand, also hope to have the thigh to write a simple SQL, to learn

CodePudding user response:

Look at the building requirements, if is tied for the most is a show or display only a, if all show that the second floor is no problem

CodePudding user response:

 with the test as 
(select the 'c', 'e' b from dual 
Union all select 'c', 'e' b from dual 
Union all select 'c', 'e' b from dual 
Union all select 'c' a, 'f' b from dual 
Union all select 'c' a, 'f' b from dual 
Union all select 'd' a, 'g' b from dual 
Union all select 'd' a, 'g' b from dual 
Union all select 'd' a, 'g' b from dual 
Union all select 'd' a, 'h' b from dual) 
Select a, b, c 
The from 
(select a, b, c, rank () over (partition by a order by c desc) rn 
The from 
Select a, b, count, (b) c from the test group by a, b)) 
Where an rn=1; 

CodePudding user response:

Table named X, SQL is as follows:

 SELECT a, b, CNT 
The FROM (SELECT a, b, CNT, row_number () over (PARTITION BY a ORDER BY DESC) CNT rn 
The FROM (SELECT a, b, COUNT (b) over (PARTITION BY a, b) CNT FROM x)) 
WHERE an rn=1; 

CodePudding user response:

Select A, B, count (1) the from table
Group by A and B

CodePudding user response:

Just don't understand all, changed the
Select A, B, d
The from (select A,
B,
D,
Dense_rank () over (partition by A order by d desc) RNN
The from (select A, B, count (1) d from table t group by A, B))
Where RNN=1;

CodePudding user response:

refer to 6th floor TheLittlePython response:

just didn't understand all, changed the
Select A, B, d
The from (select A,
B,
D,
Dense_rank () over (partition by A order by d desc) RNN
The from (select A, B, count (1) d from table t group by A, B))
Where RNN=1;

great god, and I don't really understand your algorithm, how to embody the figure 2 B C column column him?

CodePudding user response:

refer to 7th floor weixin_36805304 response:

Quote: refer to the sixth floor TheLittlePython response:

Just don't understand all, changed the
Select A, B, d
The from (select A,
B,
D,
Dense_rank () over (partition by A order by d desc) RNN
The from (select A, B, count (1) d from table t group by A, B))
Where RNN=1;

great god, and I don't really understand your algorithm, how to embody the figure 2 B C column column him?

refer to 7th floor weixin_36805304 response:

Quote: refer to the sixth floor TheLittlePython response:

Just don't understand all, changed the
Select A, B, d
The from (select A,
B,
D,
Dense_rank () over (partition by A order by d desc) RNN
The from (select A, B, count (1) d from table t group by A, B))
Where RNN=1;

great god, and I don't really understand your algorithm, how to embody the figure 2 B C column column him?

The inside of the query is A& Column B to count, the only AB value and the number of occurrences of the outer query is to get A list of the values in the corresponding column B in most times the value of the same rank dense_rank allows;
I write is figure 2 d C column

CodePudding user response:

Only through a nested query,
Inner nested statistics the number of occurrences of A column B letters, outer layer in the middle of A, B group, strives for the number of columns of the MAX value,