SQL server genuflect is begged the statement which practice (complex)-CodePudding

My SQL language is not very good, so want to exercise, which eldest brother or sister, and such questions? I want to practice

CodePudding user response:

 
- 1. The build table 
The CREATE TABLE # T 
(
Id INT, 
[name] VARCHAR (10), 
[for] INT, 
[left] INT, 
[right] INT 
) 
- 2. Insert the data 
INSERT INTO # T VALUES (1, 'a little', 0, 3, 2) 
INSERT INTO # T VALUES (4, 'smiled, 2, 8, 9) 
INSERT INTO # T VALUES (5, 'kiki, 2, 13, 0) 
INSERT INTO # T VALUES (2, 'girl', 1, 5, 4) 
INSERT INTO # T VALUES (3, "xiaoxiao", 1, 6, 7) 
INSERT INTO # T VALUES (6, "bing bing", 3, 10, 11) 
INSERT INTO # T VALUES (7, 'ice ice, 5, 0, 12) 
INSERT INTO # T VALUES (8, 'the princess', 4, 0, 0) 
INSERT INTO # T VALUES (9, 'xiao yu xiao yu', 4, 0, 0) 
INSERT INTO # T VALUES (10, "SAN SAN, 6, 0, 0) 
INSERT INTO # T VALUES (11, "shan", 6, 0, 0) 
INSERT INTO # T VALUES (12, 'slightly' 7, 0, 0) 
INSERT INTO # T VALUES (13, 'Vivian', 5, 0, 0)

According to the above data, query below

CodePudding user response:

Has the product table product (id int identity, cid int, name nvarchar (50), pubtime datetime)
Table with enterprise company (cid int identity, the name nvarchar (50))
There are service table company_service (sid int identity, cid int, service_type id, s_begin date, s_end date)

According to the service level service_type arranged in reverse chronological order of each enterprise products

For example, there are products in a enterprise, a1, a2, a3, level 6, b enterprise, with products b1, b2, level 4, c enterprise, with products c1, c2, c3, c4 and c5

Finally sorted for
A1, b1, c1, a2, b2, c2, a3, c3, c4 and c5

CodePudding user response:

Enter a number (8), get the following results:

CodePudding user response:

Please write a stored procedure, realize the input of Chinese characters, print the following image

CodePudding user response:

Please write a stored procedure, the input of a greater than 2 odd number (such as 11), and get the following figure (namely diagonal and equal)

Don't know the complexity of the not enough, arguably the not hard, will not difficult to

The exec sp_MSJZ 11

CodePudding user response:

Yang hui triangle? The rabbit series, Yang multiplication tables, etc.

reference sleet reply: 3/f

enter a number (8), get the following results:

CodePudding user response:

The original poster don't forget to put the final SQL statement posted, under study is also good to the others

CodePudding user response:

The

reference 1/f, sleet response:

 
- 1. The build table 
The CREATE TABLE # T 
(
Id INT, 
[name] VARCHAR (10), 
[for] INT, 
[left] INT, 
[right] INT 
) 
- 2. Insert the data 
INSERT INTO # T VALUES (1, 'a little', 0, 3, 2) 
INSERT INTO # T VALUES (4, 'smiled, 2, 8, 9) 
INSERT INTO # T VALUES (5, 'kiki, 2, 13, 0) 
INSERT INTO # T VALUES (2, 'girl', 1, 5, 4) 
INSERT INTO # T VALUES (3, "xiaoxiao", 1, 6, 7) 
INSERT INTO # T VALUES (6, "bing bing", 3, 10, 11) 
INSERT INTO # T VALUES (7, 'ice ice, 5, 0, 12) 
INSERT INTO # T VALUES (8, 'the princess', 4, 0, 0) 
INSERT INTO # T VALUES (9, 'xiao yu xiao yu', 4, 0, 0) 
INSERT INTO # T VALUES (10, "SAN SAN, 6, 0, 0) 
INSERT INTO # T VALUES (11, "shan", 6, 0, 0) 
INSERT INTO # T VALUES (12, 'slightly' 7, 0, 0) 
INSERT INTO # T VALUES (13, 'Vivian', 5, 0, 0)

According to the above data, query below

It doesn't show,,,, half a day to guess to understand why the left and right,,,

 with t as (
Select id, name, [for] f, l [left], [right] r the from # t 
), t1 as (
Select a. *, isnull (c.i d, isnull (b.i d, 0)) as the parent from t a 
Left the join t b on Anderson, d=b.l 
Left the join t c on Anderson, d=c.r 
), t2 as (
Select *, 0 as lv, 0 as loc from t1 where the parent=0 
Union all 
Select a. *, lv + 1, (case when Anderson, d=b.l then - 1 * power (2, 2 - lv) else power (2, 2 - lv) end) + b.l oc from t1 a, t2 b where Amy polumbo arent=b.i d 
), t3 as (
The select lv + 1 as id, loc + abs ((select min (loc) from t2)) + 1 as the loc, name the from t2 
) 
The select isnull ([1], ' ') as [1], the isnull ([2], ' ') as [2], isnull ([3], ' ') as [3], isnull ([4], ' ') as [4], isnull ([5], ' ') as [5], isnull ([6], ' ') as [6], isnull ([7], ' ') as [7], isnull ([8], ' ') as [8] 
[9], [10] [11], [12], [13], [14], [15] 
The from t3 
The pivot (
Max (name) for loc (in [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]) 
) p

Problem is that there is a small question, if I don't know how many final data, then the transformation must use the exec here, not in a statement, the other is that if you don't know how many layers, the amount of the initial power also has a problem in the t2!

Give an answer, let me under reference

CodePudding user response:

 
- this is a function of permutations and combinations to create calculation, then the statement in calculating the value of each point and location, and ranks by converting the results 
Declare @ A int=8; 
Declare @ w nVarchar (3000)='
Declare @ s nVarchar (3000)='
Declare @ SQL nvarchar (4000)='
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull