About several SQL Classic questions

When we get the topic, is not eager to answer, that will not be worth the candle, but the analysis of ideas, the use of what method, to achieve what purpose, but also to consider whether there is a simple method or a common method and so on, so that it will achieve inordinately effect, such inertia thinking in our high school when the time was learned, The so-called "original Aim" bar. The following topics are from the daily view, or QQ group, or interview questions, or blog park.

Title one: As shown in the table below, it is now necessary to collect and refund the total amount in accordance with the cashier's statistics.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6E/7B/wKiom1V-HFHBCT_2AABHaONqeos378.jpg "title=" Question_01 "alt=" Wkiom1v-hfhbct_2aabhaonqeos378.jpg "/>

The implementation results should be shown as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/77/wKioL1V-HjGxhw71AAA7q0C4XS8779.jpg "title=" result "alt=" Wkiol1v-hjgxhw71aaa7q0c4xs8779.jpg "/>

Analysis: The desired result (recorded as Table B) and the source data (recorded as table a) compared to a common column (payee), the difference is that the amount of table A is divided into two columns according to the tag and the payee, so this requirement can be expressed in the language: first, according to the payee Group (group by), and then when marked as " The amount is counted into the total of receipts (sum), and the amount is credited to the refund total (sum) when it is marked as "retired." When...... When ..., this is not the condition of SQL to judge it? There are not many conditional statements for counting SQL,if......else ... and case......when......then......else......end. So the problem is solved.

The solution is as follows:

--Create test data
With TA as
(select ' as tag ', ' + ' as payee, ' as Amount ')
Union
Select ' Receive ', ' 100 ', 375
Union
Select ' Retreat ', ' 100 ', 78
Union select ' Close ', ' 200 ', 74
)

Select Payee, sum (case when Mark = ' receive ' then amount else 0 end) as collection total,
sum (case when token = ' fallback ' then amount else 0 end) as refund total from TA
GROUP BY cashier

Topic Two: The following table A (left) staff information table, where the ID is the employee number, name is the employee name, Table B (right) is the Staff Task Assignment table, where the ID is the employee number (and the ID in table a corresponds to), task number.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/7B/wKiom1V-HN-RngvKAAAynvdHiHA156.jpg "style=" float: none; "title=" question_0201 "alt=" wkiom1v-hn-rngvkaaaynvdhiha156.jpg "/> 650" this.width=650; "src=" H Ttp://s3.51cto.com/wyfs02/m00/6e/77/wkiol1v-hozasr39aaawdu2qtq0770.jpg "style=" Float:none; "title=" question_0202 "alt=" Wkiol1v-hozasr39aaawdu2qtq0770.jpg "/>

The number of tasks per employee is now required. The results are shown below:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/7B/wKiom1V-HRmQ7o5fAABDfGC93AA238.jpg "title=" result "alt=" Wkiom1v-hrmq7o5faabdfgc93aa238.jpg "/>

In fact, the original question is this: only a table B, the number of tasks for each employee. Do not find a better way to achieve, do not discuss, welcome expert guidance.

Analysis: The difficulty of this problem is in table B in the ID complex representation, in fact, this contrary to the design principle of the database, the table A and table B should be the ID one by one corresponding. Since it is the problem, we can only start from the current conditions, the key to break the difficulty is to determine the ID in a B in the ID appears or not, if so how to count the number of occurrences. A function charindex is required to determine whether or not to appear.

The solution is as follows:

--Create test data
With TA
As
(SELECT ' as ID ', ' job1 ' as task
UNION SELECT ' job3 ', ' the '
UNION SELECT ' 2,3 ', ' job2 '
UNION SELECT ' 3,4,5 ', ' job4 ')
, TB as
(SELECT ' 1 ' as ID, ' Zhang San ' as name
UNION SELECT ' 2 ', ' King II '
UNION SELECT ' 3 ', ' John Doe '
UNION SELECT ' 4 ', ' Li Ming '
UNION SELECT ' 5 ', ' Harry ')

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

topic three : the original topic See blog: http://www.cnblogs.com/Lumia1020/p/4571301.html

as shown in table city below, code for the Administrative Region code (six digits, the first two representatives of the provincial level, the middle two representatives of the city, the last two representatives of the county level, regardless of the xx00xx situation ), urban name, Ccode for the city belongs to the provincial or municipal administrative codes.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/7B/wKiom1V-HUzxar6NAABuQ3fdxL8237.jpg "title=" Question_03 "alt=" Wkiom1v-huzxar6naabuq3fdxl8237.jpg "/>

The following results are required:  

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6E/77/wKioL1V-HxaiB2t7AAC3kG00tk0978.jpg "title=" result "alt=" Wkiol1v-hxaib2t7aac3kg00tk0978.jpg "/>

Analysis: The meaning of the analysis table City,code is very obvious, the required results are also obvious, if the province is the display of provinces, is the city is displayed as the Provincial + municipal level, is the county level is displayed as the Provincial + affiliated municipal + County. Seemingly can be used in the analysis of the topic of SQL conditional statement implementation, but on the other, there is a difference, here need to first Judge City belongs to the provincial level? City level? County? And then in correspondence, so it has to have a reference table, complex. Back to the results table for analysis, in fact, it is not difficult to determine the city belongs to the provinces and cities, the meaning of code has been explained, as long as the conversion statement: In the table city, when the code of the post four is "0000", it must be provincial; When code's two bits are "00" and the latter four bits are " 0000 "At the time, the city level is certainly, when the code after two not for" 00 ", for the county level. In this way, the decision of the provinces and counties at a glance, and then, according to the city-level code to follow the provincial level, and to reach the Provincial + municipal level, the county to the city, to obtain the Provincial + affiliated municipal + County, through the use of this simple recursive thinking , The solution will be leap off.

The solution is as follows:

--Test data
With TA as
(SELECT ' 110000 ' as code, ' Beijing ' city, ' 110000 ' Ccode
Union
Select n ' 110200 ', n ' Xicheng ', 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 ', n ' 430100 '
Union
Select n ' 430101 ', n ' Wangcheng County ', n ' 430100 ')

SELECT * Into City from TA

SELECT * from city;

--Solutions
With TA
As
--Provincial
Select code,city,ccode,city content from city where right (code,4) = ' 0000 '),
TB as (
--City 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) = ' xx ' 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 several small questions, often think new, warm so the part of the knowledge of SQL, of course, a lot of ways, variable, such as the number of questions in the table B of each task. Shortcomings, welcome you to guide!

This article is from the "It Learning path" blog, so be sure to keep this source http://zhangbc.blog.51cto.com/6066576/1662006

About several SQL Classic questions