A few small questions about SQL

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.

The implementation results should be shown as follows:

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:

With TA as (select ' Receive ' as tag, ' as Payee ', ' the ' as ' Amount ', ' 375 ', ' + ', ' + ', ' + ', ' + ', ' + ', ' + '), ' ($ ') Select payee, sum (case when Mark = ' receive ' then amount else 0 end) As collection total,       sum (case when Mark = ' back ' then amount else 0 end) as refund total From Ta       GROUP by payee

Topic two : Table A (left) employee Information table, where ID is the employee number, name is the employee name, Table B (right) is the Employee Task Assignment table, where ID is the employee number (corresponding to the ID in table a), task is the job number.

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

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 CHARINDEXis required to determine whether or not to appear.

The solution is as follows:

--Create test data with TAAS (SELECT ' As ID ', ' job1 ' as taskunion select ' I/A ', ' job3 ' union select ' 2,3 ', ' job2 ' union select ' 3,4,5 ', ' job4 '), TB as  (SELECT ' 1 ' as ID, ' Zhang San ' as nameunion select ' 2 ', ' King two ' union select ' 3 ', ' John Doe ' Union select ' 4 ', ' Li Ming ' union SEL ECT ' 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 is the administrative area code (six digits, the first two represent the provincial level, the middle two represents the municipal, the last two represent the county, no consideration of XX00XX City is the town name, and Ccode is the provincial or municipal administrative code of the city.

The following results are required:  

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 ' ccodeunionselect n ' 110200 ', n ' Xicheng ', n ' 110200 ' unionselect n ' 110300 ', n ' Chongwen District ', n ' 110300 ' unionselect n ' 430000 ', n ' Hunan province ', n ' 430000 ' unionselect n ' 430100 ', n ' Changsha ', n ' 430100 ' Unionselect N ' 430101 ', n ' Wangcheng County ', n ' 430100 ') select * into the city from Taselect * from city;--Solution with Taas (--Provincial select Code,city,ccode,city Content from town 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) <> ' xx ') SELECT * from Taunionselect * from Tbunionselect * 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!

An explanation of several small questions about SQL