The create table [ceshi]
(id int, parentid int, code nvarchar (50))

Insert into [ceshi]
Select 1, 0, 'a' union all
Select 2, 1, 'b' union all
Select 3, 1, 'c' union all
Select 4, 2, 'd' union all
Select 5, 2, 'e' union all
Select 6, 2, 'f' union all
The select 7, 3, 'g' union all
The select 8, 3, 'h' union all
The select 9, 4, 'I' union all
The select 10, 5, 'j' union all
Select 11, 9, 'k' union all
The select 12, 7, 'l' union all
The select 13, 7, 'm' union all
The select 14, 8, 'n' union all
The select 15, 13, 'o' union all
Select 16, 14, and 'p'



This is about data
P_id is superior node
I have G id should be found out. A, C, G, L, M, O
C id should find out A, C, G, L, M, O, H, N, P.

How should this query,
Use WITH the AS
?There are tens of thousands of data,
Will slow,
Consult,

CodePudding user response:

The create table [ceshi]
(id int, parentid int, code nvarchar (50), longcode (200))
Insert into [ceshi]
Select 1, 0, 'a', '1' union all
Select 2, 1, 'b', '1.2' union all
Select 3, 1, the 'c', '1.3' union all
Select 4, 2, 'd', '1' union all
Select 5, 2, 'e', '1.2.5' union all
Select 6, 2, 'f', '1.2.6' union all
The select 7, 3, 'g', '1.3.7' union all
The select 8, 3, 'h', '1.3.8' union all
The select 9, 4, 'I', '1.2.4.9' union all
The select 10, 5, 'j', '1.2.5.10' union all
Select 11, 9, 'k' and '1.2.4.9.11' union all
The select 12, 7, 'l', '1.3.7.12' union all
The select 13, 7, 'm', '1.3.7.13' union all
The select 14, 8, 'n', '1.3.8.14' union all
The select 15, 13, 'o', '1.3.7.13.15' union all
The select 16, 14, the 'p', '1.3.8.14.15'

Add a column length of code, to save his supervisor ID, this check is more convenient,

CodePudding user response:

Tens of thousands of data CET statement can't be slow, plus a list of paths to check the lower is convenient, superior to use more, as are dealt with CET

CodePudding user response:

reference 1 floor horse monkey shochu response: 123

create table [ceshi]
(id int, parentid int, code nvarchar (50), longcode (200))
Insert into [ceshi]
Select 1, 0, 'a', '1' union all
Select 2, 1, 'b', '1.2' union all
Select 3, 1, the 'c', '1.3' union all
Select 4, 2, 'd', '1' union all
Select 5, 2, 'e', '1.2.5' union all
Select 6, 2, 'f', '1.2.6' union all
The select 7, 3, 'g', '1.3.7' union all
The select 8, 3, 'h', '1.3.8' union all
The select 9, 4, 'I', '1.2.4.9' union all
The select 10, 5, 'j', '1.2.5.10' union all
Select 11, 9, 'k' and '1.2.4.9.11' union all
The select 12, 7, 'l', '1.3.7.12' union all
The select 13, 7, 'm', '1.3.7.13' union all
The select 14, 8, 'n', '1.3.8.14' union all
The select 15, 13, 'o', '1.3.7.13.15' union all
The select 16, 14, the 'p', '1.3.8.14.15'

Add a column length of code, to save his supervisor ID, it is more convenient,

You this kind of design apart from operators do not conform to the requirements of the paradigm, but look at the result set, there is no practical significance, at the time of the query is certainly not you want to show this ID string, but into the value of the corresponding transformation of the process is time-consuming, secondly, the data line as long as there is a change, the associated information need to be updated, such as the middle add a node, corresponding to the online route needs to be updated,

CodePudding user response:

reference leo_lesley reply: 3/f

Quote: refer to 1st floor horse monkey shochu 123 reply:

The create table [ceshi]
(id int, parentid int, code nvarchar (50), longcode (200))
Insert into [ceshi]
Select 1, 0, 'a', '1' union all
Select 2, 1, 'b', '1.2' union all
Select 3, 1, the 'c', '1.3' union all
Select 4, 2, 'd', '1' union all
Select 5, 2, 'e', '1.2.5' union all
Select 6, 2, 'f', '1.2.6' union all
The select 7, 3, 'g', '1.3.7' union all
The select 8, 3, 'h', '1.3.8' union all
The select 9, 4, 'I', '1.2.4.9' union all
The select 10, 5, 'j', '1.2.5.10' union all
Select 11, 9, 'k' and '1.2.4.9.11' union all
The select 12, 7, 'l', '1.3.7.12' union all
The select 13, 7, 'm', '1.3.7.13' union all
The select 14, 8, 'n', '1.3.8.14' union all
The select 15, 13, 'o', '1.3.7.13.15' union all
The select 16, 14, the 'p', '1.3.8.14.15'

Add a column length of code, to save his supervisor ID, it is more convenient,

You this kind of design apart from operators do not conform to the requirements of the paradigm, but look at the result set, there is no practical significance, at the time of the query is certainly not you want to show this ID string, but into the value of the corresponding transformation of the process is time-consuming, secondly, the data line as long as there is a change, the associated information need to be updated, such as the middle add a node, corresponding to the online route needs to be updated,

Now I want to sit on a menu access related functions,

Menu is such structure,

Permissions, I think.. I design no problem, now is the query, and I write SQL is too slow,

CodePudding user response:

RETURNS the @ t_level TABLE (id UNIQUEIDENTIFIER)
AS
The BEGIN

DECLARE @ aaa TABLE (sid UNIQUEIDENTIFIER);

; WITH CTET
AS
(
SELECT * FROM dbo. Emp_pm_td_classification WHERE the exists (
Select the sid from @ aaa s WHERE emp_pm_td_classification. Sid=s.s id
)
UNION ALL
SELECT a. * FROM dbo. Emp_pm_td_classification AS a INNER JOIN CTET AS b ON b.s id=Amy polumbo arentid)
Insert into @ t_level SELECT sid FROM CTET


; With t as
(select the sid, parentid from emp_pm_td_classification where
The exists (
Select the sid from @ aaa s WHERE emp_pm_td_classification. Sid=s.s id
)
Union all
The select b.s id, p. arentid
The from t a
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull