Detailed explanation of several questions about SQL (SQL advanced)

Time:2021-4-16

When we get the questions, we don’t rush to answer them, which will not be worth the loss. Instead, we need to analyze our thinking, what methods to use, what purpose to achieve, and think about whether there are simple methods or general methods. Only in this way can we achieve the effect of taking one as ten. In fact, this kind of habitual thinking was learned as early as when we were in high school, so-called “never changing” Leave it alone. The following questions come from daily experience, or QQ group, or interview questions, or blog Garden.

Topic 1: as shown in the table below, we need to count the total amount of collection and refund according to the cashier.

The implementation results should be shown as follows:

Analysis: the desired result (denoted as table b) has the same column (payee) as the source data (denoted as table a). The difference is that the amount in table a is divided into two columns according to the tag and payee, so this requirement can be expressed in language: first, group by payee, Then, when marked as “received”, the amount is included in the total collection (sum); when marked as “refund”, the amount is included in the total refund (sum). When When This is the conditional judgment of SQL? There are not many conditional statements in SQL else…… And case when…… then…… else…… end。 In this way, the problem can be solved.

The solution is as follows:

with ta as
(select 'collect' as tag, '100' as cashier, 150 as amount)
 union
 Select 'receive', '100', 375
 union
 Select 'back', '100', 78
 Union select 'receive', '200', 74
)

Select the payee, sum (case when Mark ='received 'then amount else 0 end) as the total amount of collection,
 Sum (case when Mark ='refund 'then amount else 0 end) as refund total from Ta
 Group by cashier

Topic 2: table a (left) is the employee information table, where id is the employee job number and name is the employee name; table B (right) is the employee task allocation table, where id is the employee job number (corresponding to the ID in table a), and task is the task number.

The number of tasks per employee is required. The results are as follows

In fact, the original question is like this: there is only one table B to find the number of tasks for each employee. There is no better way to achieve it. We don’t want to discuss it. You are welcome to give some advice.

Analysis: the difficulty of this problem lies in the complex representation of ID in table B. in fact, this is contrary to the design principle of database, and the ID of table a and table B should correspond one by one. Since it is a problem, we can only start from the current conditions. The key to break through the difficulties is to judge whether the ID in a appears in B, and if so, how to count the number of occurrences. The function CHARINDEX is used to judge whether it appears or not.

The solution is as follows:

--Create test data
WITH TA
AS
(SELECT '1,2' AS ID,'job1' AS task
UNION SELECT '1,2,3','job3'
UNION SELECT '2,3','job2'
UNION SELECT '3,4,5','job4')
,TB AS 
(select '1' as ID, 'Zhang San' as name)
Union select '2','wang Er '
Union select '3','li Si '
Union select '4','li Ming '
Union select '5','wang Wu ')

SELECT B.ID,B.name,COUNT(1) AS TASKS
 FROM TA A,TB B
 WHERE CHARINDEX(B.ID,A.ID)>0
 GROUP BY B.ID,B.name
 order by B.ID

Title 3: see this article for the original titlehttps://www.jb51.net/article/67885.htm

As shown in the following table City, 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, regardless of xx00xx), city is the city name, and ccode is the provincial or municipal administrative region code of the city.

The results are as follows:

Analysis: the meaning of city and code in the analysis table is very obvious, and the required results are also very obvious. If it is a province, it is a province; if it is a city, it is a province + city; if it is a county, it is a province + city + county. It seems that it can be implemented with the SQL conditional statement mentioned in the analysis of topic 1, but on second thought, there is still a difference. Here, we need to judge whether city belongs to the provincial level first? Municipal? county-level? Then in the corresponding, so there must be a reference table, complex. Back to the result table for analysis, it is not difficult to determine whether a city belongs to a province, city or county. The meaning of the code has been explained, as long as the expression is changed: in the table City, when the last four digits of the code are “0000”, it must be a province; when the last two digits of the code are “00”, and the last four digits are not “0000”, it must be a city; when the last two digits of the code are not “00”, it must be a county. In this way, the judgment of province, city and county is clear at a glance. Then, according to the city level code, we can trace the provincial level, and get the provincial level + city level, and the county level can trace the city level, and get the provincial level + city level + county level. By using this simple recursive idea, the solution will be on the paper.

The solution is as follows:

--Test data
with ta as
(select '110000' as code, 'Beijing' City, '110000' ccode
union
Select n'110200 ', n'xicheng district', n'110200 '
union
Select n'110300 ', n'chongwen district', n'110300 '
union
Select n'430000 ', n'hunan province', n'430000 '
union
Select n'430100 ', n'changsha city', n'430100 '
union
Select n'430101 ', n' Wangcheng County ', n'430100')

select * into City from ta

select * from City;

--Solutions
with ta
as(
--Provincial level
select code,city,Ccode,city content from City where right(code,4)='0000'),
tb as(
--Municipal level
select b.code,b.city,b.Ccode,a.city+','+b.city as content from ta a,City b where left(a.Ccode,2)=left(b.Ccode,2)
and right(b.code,2)='00' and right(b.code,4)<>'0000'),
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 ta
union
select * from tb
union
select * from tc

Through the above questions, I often think about new things and review some knowledge of SQL. Of course, there are many methods and variations, such as the number of people in each task in table B of Topic 2. Deficiencies, welcome your advice!