A few small questions about SQL (SQL Advanced) _mssql

When we get the title, is not eager to answer, that will outweigh the gains, but the analysis of ideas, what method to achieve what purpose, but also have to think of a simple method or general methods, etc., so as to achieve the effect of Isidam, such inertia thinking in fact, early in our high school when it was learned, The so-called "Same" bar. The following topics come from everyday see, or QQ group, or interview questions, or blog park.

topic one : as shown in the table below, the total amount of receivables and refunds is now required in accordance with the cashier's statistics.

The results of the implementation should be shown as follows:

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

The solution is as follows:

With TA as
(select ' Receive ' as Mark, ' as Payee ', as Amount
 Union
 select ' Collect ', ' 375 ', ' Union select ', '
 100 ' , the
 union Select ' Collect ', ' and ', '

select payee, sum (case when Mark = ' collect ' then amount else 0 end ') as receivable total,
 sum (case whe n tag = ' Back ' then amount else 0 end) as refund total from TA
 Group by cashier

topic two : Table A (left) staff information table, where ID is employee number, name is employee's name; Table B (right) assigns a table for the employee task, where the ID is the employee number (corresponding to the ID in table a), and the task is the job number.

The number of tasks per employee is now required. The results appear as follows:

In fact, the original question is this: only a table B, ask each employee's task number. Did not find a better way to achieve, not to do the discussion, welcomed the expert advice.

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

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, ' John ' as name
UNION select ' 2 ', ' King II ' C11/>union Select ' 3 ', ' Dick '
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 title see this article: http://www.jb51.net/article/67885.htm

As shown in the following table city, code for the administrative Area code (six digits, the first two representatives of the provincial, the middle two representatives of the city level, the last two representatives of the county, regardless of the xx00xx situation), cities for the name of urban, Ccode for the city's provincial or municipal administrative Region code.

The following results are required:

Analysis: The analysis table City,code meaning is very obvious, the result that needs is also very obvious, if is the province is to show provinces, is the city is shown as the Provincial + municipal level, is the county level is shown as the Provincial + subordinate city + county level. Seemingly can use the topic of an analysis of the SQL conditional statement to achieve, but on second thought, or there is a difference, here need to judge the city belongs to the provincial level? City level? County? Then in the corresponding, so there must be a reference table, complex. Back to the results table to analyze, in fact, it is not difficult to determine the city belongs to the provincial and county problems, the meaning of code has been explained, as long as the conversion statement: In the table city, when the code after the four-bit "0000", it is certainly the provincial; when the latter two digits of the code are "00" and the latter four bits are not " 0000 "is certainly the city level when the code after two digits is not" 00 ", for the county. So the city and county of the decision on a glance, then, according to the city-level code to pursue the provincial level, and to the Provincial + city-level, county-level after the city, to get the Provincial + owned municipal +-owned county, through the use of this simple recursive thinking, the solution will be leaps.

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 ' 1 10200 '
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 ', n ' 430100 ')

select * Into city From TA

select * to City;

--Solution with
ta as
(
--Provincial
Select code,city,ccode,city content from city where right (code,4) = ' 0000 ') ,
TB as (
-municipal
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) = ' All ' 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) <> ' ")
select * FROM TA
Union
SELECT * to TB
Union
SELECT * FROM TC

Through the above several small problems, often think new, warm up some of the knowledge of SQL, of course, a lot of ways, a lot of change, such as the title of the second table B, the number of each task. Deficiencies, you are welcome to guide!