Oracle row and column statistics conversion !!!

Experience: Use deCODE/group by/order by/sign/sum to generate different statistics and reports.

-- Calculate the value of Y in hkb_test1, which is 1, 3, and 5 X.
Select * From hkb_test1;
X Y
---------
A 1
B 1
A 3
D 2
E 4
F 5
A 5
D 3
D 6
B 5
C 4
B 3

Result:
X
----
A
B
-- Method 1
Select x
From hkb_test1
Group by X
Having sum (decode (Y, 1,-1, 3,-1, 5,-1, 0) =-3;
-- Method 2
Select x
From hkb_test1
Group by X
Having (sign (sum (decode (Y, 1,-1, 0) + sign (sum (decode (Y, 3,-1, 0 ))) + sign (sum (decode (Y, 5,-1, 0) <=-3 );

PS:
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.
Therefore, you can use sign and decode to compare the field size to distinguish a field.
Select decode (sign (Field 1-Field 2),-1, Field 3, field 4) from dual;

I can't quite understand what I copied:
Sign is a powerful function for writing and analyzing SQL statements.
Below are some summary of the sign:
However, if the attribute student is set to a value other than 0 and 1, or student is set to two or more standard values, the problem will not be so simple.
The solution is feature functions (ABS (), sign ())

Common Features Algorithm
[A = B] = 1-abs (sign (A-B ))
[! = B] = ABS (sign (A-B ))
[A <B] = 1-sign (1 + sign (A-B) cannot use-sign (A-B): Because if a does not meet
[A <= B] = Sign (1-sign (A-B ))
[A> B] = 1-sign (1-sign (A-B ))
[A> = B] = Sign (1 + sign (A-B )))
[Not α] = 1-D [α]
[α andb] = d [α] * d [B]
[α or B] = Sign (d [α] + d [B])

For example:
A <B decode (sign (A-B),-1, 1, 0)
A <= B decode (sign (A-B), 1, 0, 1)
A> B decode (sign (A-B), 1, 1, 0)
A> = B decode (sign (A-B),-1, 0, 1)
A = B decode (A, B, 1, 0)
A between B and C decode (sign (A-B),-1, 0, decode (sign (A-C), 1, 0, 1 ))
A is null decode (A, null, 1, 0)
A is not null decode (A, null, 0, 1)
A In (B1, B2,..., bn) decode (A, B1, 1, B2, 1,..., bn, 1, 0)
Nor Loga decode (Loga, 0, 1, 0)
Loga and logb Loga * logb
Loga or logb Loga + logb
Loga XOR logb decode (sign (Loga), sign (logb), 0, 1)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>
Select * From grade1;
Studentid subjectid mark
----------------------
101 A01 59
101 A02 72
101 A03 90
102 A01 75
102 A02 91
103 A01 71

-- Calculate the scores of Students
Select studentid,
Sum (decode (subjectid, 'a01', mark),
Sum (decode (subjectid, 'a02', mark) B,
Sum (decode (subjectid, 'a03', mark) c
From grade1
Group by studentid;
Studentid A B C
---------------------------------------
102 75 91
101 59 72 90
103 71

-- Count the number of people in four score segments
Select sum (Case
When Mark <60 then
1
Else
0
End) as "not passed ",
Sum (Case
When Mark between 60 and 79 then
1
Else
0
End) as "passed ",
Sum (Case
When Mark between 80 and 89 then
1
Else
0
End) as "good ",
Sum (Case
When Mark> = 90 then
1
Else
0
End) as "excellent"
From grade1;
Not passed good excellent
----------------------------------------
1 3 0 2

-- Count the consumption of each month each year
Select * from sale;
Sum_month limit
---------------------
200001 1000.00
200002 1100.00
200003 1200.00
200004 1300.00
200005 1400.00
200006 1500.00
200007 1600.00
200101 1100.00
200202 1200.00
200301 1300.00

Select substrb (sum_month, 1, 4) Year,
Sum (decode (substrb (sum_month, 5, 2), '01', interval, 0) January,
Sum (decode (substrb (sum_month, 5, 2), '02', plural, 0), January 1, February,
Sum (decode (substrb (sum_month, 5, 2), '03', hour, 0) March,
Sum (decode (substrb (sum_month, 5, 2), '04 ', hour, 0) April,
Sum (decode (substrb (sum_month, 5, 2), '05 ', interval, 0) May,
Sum (decode (substrb (sum_month, 5, 2), '06', plural, 0) June,
Sum (decode (substrb (sum_month, 5, 2), '07', interval, 0), January 1, July,
Sum (decode (substrb (sum_month, 5, 2), '08', plural, 0), January 1, August,
Sum (decode (substrb (sum_month, 5, 2), '09', plural, 0), January 1, September,
Sum (decode (substrb (sum_month, 5, 2), '10', hour, 0), January 1, October,
Sum (decode (substrb (sum_month, 5, 2), '11', hour, 0) November,
Sum (decode (substrb (sum_month, 5, 2), '12', hour, 0) December
From sale
Group by substrb (sum_month, 1, 4 );
Year, month, month
-------------------------------------------------------------------------
2000 1000 1100 1200 1300 1400 1500 0 0 0 0
2001 1100 0 0 0 0 0 0 0 0 0 0 0
2003 1300 0 0 0 0 0 0 0 0 0 0 0
2000 0 1200 0 0 0 0 0 0 0 0 0

Attention: Use Case in statistics !!!
Example:
Select * From hkb_case_test;
Userid empno Sal sex
---------------------
1 A 800 1
1 A 800 1
2 B 900 2
3 A 400 1
4 d 1400 2
5 e 1200 1
6 F 500 1
7 A 300 2
8 d 1000 1
9 d 1230 2
10 B 2000 2
11 c 2000 1
12 B 1200 1

Select a. empno as department,
Count (A. userid) as count,
Sum (Case A. Sex
When 1 then
1
Else
0
End) as male,
Sum (Case A. Sex
When 2 then
1
Else
0
End) as female,
Sum (case sign (A. Sal-800)
When-1 then
1
Else
0
End) as is less than 800 RMB. -- note that aliases cannot start with numbers.
Sum (case sign (A. Sal-800) * sign (A. Sal-1000) -- use * to implement <and> functions.
When-1 then
1
Else
0
End) + (Case A. Sal
When 800 then
1
Else
0
End) as from 800 to 999,
Sum (case sign (A. Sal-1000) * sign (A. Sal-1200)
When-1 then
1
Else
0
End) + (Case A. Sal
When 1000 then
1
Else
0
End) as from 1000 yuan to 1199 yuan,
Sum (case sign (A. Sal-1200)
When 1 then
1
Else
0
End) + (Case A. Sal
When 1200 then
1
Else
0
End) as is more than 1200 yuan
From hkb_case_test
Group by A. empno;

The number of men and women in the Department is less than 800 yuan, from 800 to 999 yuan, from 1000 yuan to 1199 yuan, more than 1200 yuan
---------------------------------------------------------------
A 4 3 1 2 2 0 0
B 3 1 2 0 1 0 2
C 1 1 0 0 0 1
E 1 1 0 0 0 1
D 3 1 2 0 0 1 2
F 1 1 0 1 0 0 0