A few questions about SQL (SQL advanced) and SQL advanced

A few questions about SQL (SQL advanced) and SQL advanced

When we get a question, we are not eager to answer the question. It will not be worth the candle, but to analyze the ideas, methods, and objectives, we also need to think about whether there are simple methods or general methods to achieve the same effect. In fact, this kind of inertial thinking was introduced as early as when we were in high school, the so-called "changing things will never let us go. The following questions come from what you see on a daily basis, QQ groups, interview questions, or blog parks.

Question 1: As shown in the following table, the total amount of collection and refund needs to be calculated by the cashier.

The implementation result must be shown as follows:

Analysis: the expected result (table B) has A common column (cashier) compared with the source data (table ), the difference is that the amount of Table A is divided into two columns Based on the tag and the cashier, so this requirement can be expressed in A language: First, according to the cashier group (group ), then, when marked as "receive", the amount is included in the total amount (sum); When marked as "return", the amount is included in the total amount of refund (sum ). When ...... Hour ......, Isn't that SQL condition judgment? There are not many SQL check condition statements. if ...... Else ...... And case ...... When ...... Then ...... Else ...... End. This solves the problem.

The solution is as follows:

With ta as (select 'accept' as tag, '20180101' as cashier, 100 as amount union select 'accept', '20180101', 150 union select 'return ', '123', 78 union select 'accept', '123', 74) select cashier, sum (case when Mark = 'accept' then amount else 0 end) as total receipts, sum (case when Mark = 'return' then amount else 0 end) as total refund from ta group by cashier

Question 2: The following table shows the employee information table on the left. ID indicates the employee ID and name indicates the employee name. Table B (on the right) indicates the employee Task Assignment table, the ID is the employee ID (corresponding to the ID in table A) and the Task ID.

The number of tasks required for each employee. The result is as follows:

In fact, the original question is: there is only one table B, and the number of tasks for each employee is required. We have not found a better method for implementation, so we will not discuss it. Thank you for your advice.

Analysis: the difficulty of this question lies in the complex representation of the IDS in Table B. In fact, this is contrary to the database design principles and should correspond to the IDs of tables A and B one by one. Since it is A question, we can only start with the current condition. The key to breaking the challenge is to determine whether the ID of A appears in B. If so, how to count the number of occurrences. The CHARINDEX function must be used to determine whether or not to appear.

The solution is as follows:

-- Create test data with taas (SELECT '1, 2' as id, 'job1' AS taskUNION SELECT '1, 2,3 ', 'job1' union select '2, 3 ', 'job2' union select '3, ', 'job1'), tb as (SELECT '1' as id, 'zhang san' as nameUNION SELECT '2 ', 'wang 2' union select '3', 'Li si' union select '4', 'Li Ming 'union select '5', 'wang 5') select B. ID, B. name, COUNT (1) as tasks from ta a, tb B where charindex (B. ID,. ID)> 0 group by B. ID, B. name order by B. ID

Title 3: original question see this article: http://www.bkjia.com/article/67885.htm

As shown in the following table, the code is the administrative region code (six digits, the first two represent the provincial level, the middle two represent the municipal level, and the last two represent the county level without considering xx00xx). The City is the city name, the CCode is the provincial or municipal administrative region code of the city.

The following results are required:

Analysis: In the analysis table "city", the code has obvious meanings and the expected results are also obvious. If it is a province, it indicates a province; if it is a city, it indicates a province + a city; if it is a county level, it is displayed as the province + city + county level. It seems that you can use the SQL condition statement mentioned in the analysis of the question 1. But in another thought, there is still a difference. here we need to determine that the city belongs to the provincial level first? Municipal? County Level? Then, it is complicated to have a reference table. Return to the results table for analysis. In fact, it is not difficult to determine that the city belongs to a province, City, or county. The meaning of the code has been explained as long as the conversion statement is as follows: In the table city, when the last four digits of the code are "0000", it must be provincial. When the last two digits of the code are "00" and the last four digits are not "0000, it must be a municipal level. When the last two digits of the code are not "00", the county level is used. In this way, the judgment of provinces, cities, and counties will be clear at a glance. Then, based on the Municipal Code, the provincial and municipal levels will be traced back to the different levels of cities and counties, the province-level + city-level + county-level solution is on the paper by using this simple recursive idea.

The solution is as follows:

-- Test data with ta as (select '000000' as code, 'beijing' city, '000000' Ccodeunionselect n' 110000 ', n'xicheng district ', N '000000' unionselect n'000000', N 'chongwen region', N '000000' unionselect n'000000', N 'hunan ', N '000000' unionselect n'000000 ', N 'changsha City ', n' 430100 'unionselect n' 430101', n' Wangcheng County ', n' 430100') select * into City from taselect * from City; -- Solution with taas (-- provincial select code, city, Ccode, city content from City where right (code, 4) = '000000'), tb as (-- municipal select B. code, B. city, B. ccode,. city + ',' + B. city as content from ta a, City B where left (. ccode, 2) = left (B. ccode, 2) and right (B. code, 2) = '00' and right (B. code, 4) <> '20140901'), tc as (select c. code, c. city, c. ccode, B. content + ',' + c. city content from tb B, City c where left (B. ccode, 4) = left (c. ccode, 4) and right (c. code, 2) <> '00') select * from taunionselect * from tbunionselect * from tc

Through the above questions, I often think about new things and understand some of the SQL knowledge. Of course, there are many methods and variants, such as the number of tasks in Table B in question 2. For more information, see!