Have friends, recently, I want to help solve a problem, the problem description:
There are three fields: F1, F2, F3, where each field may appear between 1 to 9, and the number of times each appearing in each table is now statistics.
Such as:
F1
F2
F3
1 1 2 1 2 3 2 1 2
In the above example: "1" appeared 4 times, "2" appeared 4 times, "3" has appeared once.
Of course, this problem is required to use SQL statements as much as possible. Because if you use a program, a variable, an array, it doesn't make this problem. When I spent a few minutes, after OVER, I felt this problem with SQL. It seems to have some meaning. How to use the simplest and clear ideas and methods to get this problem? Here, this problem is shared, I hope to let everyone work, busy, change the mood, and change a little fun.
Here, I will announce my solution as follows:
SELECT A1, Count (A1) AS EXPR1
From (SELECT A1 from Test
Union all
SELECT A2 from Test
Union all
Select a3 from test
)
Group by A1
I deliberately set the font color to white, to avoid interfering with your thinking, if you want to see, you can drag with the mouse, select the blank area above, paste the content into the writepad or query analyzer. Should
SQL
in
SQLServer2000
Lower debugging.
