【 title 】

There are two tables, one is a list of orders, the table is called “Order details”; One is a list of users called “Registry”. The user ID in The Order List corresponds to the user ID in the Registry.

Question 1: Query the number of payers, amount of payment, and amount of payment orders successfully paid every day. Question 2: The number of payers and amount of payment of registered users on June 14, 2013

【 答 案 】

Query the number of successful payers, payment amount and payment volume every day

1. Query “every day” data, involving “every” this kind of problem should think of “monkey from zero to learn SQL” in the “group summary” to solve this kind of problem

Group by “paid time”. The number of paid users per day is summarized (count counts the number of rows in the user ID column), the amount of paid daily is summarized (sum sums the amount of paid column), and the amount of paid daily is summarized (count counts the number of rows in the order number column).

2. The user ID, payment amount and order number mentioned above are all in the “Order List”

3. Note that the statistics are the number of users, not the person-times. Therefore, you need to de-process the user ID statistics

Date_format (‘%Y-% C -%d’); date_format(‘%Y-% C -% D ‘);

SQL > select order status as’ successful ‘, order status as’ successful ‘, order status as’ failed ‘, order status as’ successful

The query results

【 答 案 】

1. If it involves “every” problem, think of group summary or window function to solve it

2. Investigate the grouping application of SQL data, in this case, the data is filtered according to conditions before grouping, using the WHERE clause. If you want to specify conditions for the data after grouping results, use HAVING instead of WHERE

3. Investigate the application of SQL summary function, commonly used summary function has the following several:

【 答 案 】

Query the number of paid users and amount of paid users registered on June 14, 2013

Firstly, we disassemble the problem by multi-dimensional disassembly analysis method

1. First find “Registered users on June 14, 2013”, which can be found in the “registry”

2. Add up the total number of “paid users” and “paid amount” of “registered users on June 14, 2013”, which can be found in the “Order Details”

3. Because two tables are involved, a multi-table join is required. The user ID column in the two tables has an intersection. The user ID column in the two tables has an intersection with the user ID column.

You should use the “inner join” in multi-table joins for all data that is common to both tables that you need to find.

Because we need to count the number of people who pay and the amount paid, we can get it by adding the summary function in SQL in the previous step

4. Locate the common data in the User ID column of the two tables and set the conditional WHERE registry. Registration time = “June 14, 2013”. It also needs to be a valid order, i.e. a list of orders. Order Status = “Success”

Date_format (‘%Y-%c-%d’); date_format(%Y-% C -%d);

The query results

【 答 案 】

SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL

2. Examine multiple table queries. You can use the AS statement to define the name of a table and compare two tables conveniently. And how to decide which join to use.

Recommended: How to learn SQL from zero?