Selection of interview questions--database statistics

There's always such a problem in the interview,

The database is an essential issue.

Today to share a database aspects of the face test!

The topics are as follows:

A data table AA is known, and the contents and format are as follows:

Write a SQL to output the following in the format:

............................................................

Let's take a look at how this problem should be done.

First, we first think of the statistics and grouping, the SQL is written as follows:

Select RQ, Shengfu, count(shengfu) from the AA group by RQ, Shengfu;

However, the results of the operation are not satisfactory.

This time, you need to apply the knowledge point in another SQL.

What is this point of knowledge?

Is the row to column!

In MySQL, there are a number of ways to row to column,

One of them is to use the SUM function, combined with the case statement combination to complete.

Like the interview we're having right now,

We can write it like this.

RQ,sum(' win

' win ',sum(

' Negative    ' negative   

RQ;

The results of the implementation are as follows:

How does this SQL explain it?

First of all, we show the winner, is to count his number

We have chosen the SUM function here.

Then select the case and then to combine

The format of the expression is as follows:

Colume

Result

End

By substituting the SQL in front of us, we can understand this:

When the value of the Shengfu field equals "Win", it becomes 1, if not equal to "win" then give 0.

So, the whole thing in parentheses is to turn all the "wins" into 1, then the sum sum is the result of the number of wins.

Negative is calculated the same way.

This will enable us to query the results shown.

For the case, to explain to you,

1. The simple case function is a true subset of the case search function

• The use of the simple case function is similar to a switch statement in some high-level languages such as: a case given a matching field, when a specific field value is given, and then if the match is returned.

• The simple case function is actually the implementation of the ' = ' Logic of the case search function. The case search function can implement all the functions of a simple case function, whereas a simple case function cannot implement a function other than the ' = ' Logic of the case search function.

3. Case function Matching principle

• The case function differs from switch in that the case only returns the result of the first match, and

• Switch will continue the subsequent judgment without interruption, and all matching results will be executed.

5. Case search function is more flexible than simple case function

• The flexibility of the case search function compared to the simple case function is that you can write a judgment in when.

For the magic of case in SQL, you can take a 53523176

To get a detailed look at the case.

Selection of interview questions--database statistics