Q; How to write unlimited sql statements

adgulyhq1234

Have a watch
id belongToId
== =================
1 2
twenty three
3 4
4 5

How to choose the result is 1,2,3,4,5

That is, the user gives an id of 1 ... and selects all the child ids that belong to him.

116657396

reference:

-Generate test data
create table BOM (ID VARCHAR (10), PID VARCHAR (10))
insert into BOM select 'a', NULL
insert into BOM select 'b', 'a'
insert into BOM select 'c', 'a'
insert into BOM select 'd', 'b'
insert into BOM select 'e', ​​'b'
insert into BOM select 'f', 'c'
insert into BOM select 'g', 'c'
go

--Create user-defined functions
create function f_getChild (@ID VARCHAR (10))
returns varchar (8000)
as
begin
    declare @i int, @ ret varchar (8000)
    declare @t table (ID VARCHAR (10), PID VARCHAR (10), Level INT)
    set @i = 1
    insert into @t select ID, PID, @ i from BOM where PID = @ID
    
    while @@ rowcount <> 0
    begin
        set @i = @i + 1
        
        insert into @t
        select
            a.ID, a.PID, @ i
        from
            BOM a, @ t b
        where
            a.PID = b.ID and b.Level = @ i-1
    end
    
    select @ret = isnull (@ret, '') + ID from @t
    
    return @ret
end
go

--Execute query
select ID, isnull (dbo.f_getChild (ID), '') from BOM group by ID
go

-Output results
/ *
a bcdefg
b de
c fg
d
e
f
g
* /

--Delete test data
drop function f_getChild
drop table BOM

wgfxman

create table TESTTAB (
id int,
belongToId int
)
GO

insert into TESTTAB (id, belongToId)
            select 1,2
UNION ALL select 2,3
UNION ALL select 3,4
UNION ALL select 4,5
GO

DECLARE @RESULTSTR VARCHAR (4000)
DECLARE @CUR_LV INT
DECLARE @LAST_LV INT

SELECT @ CUR_LV = 1 --here is the entry
SELECT @ LAST_LV = 0
SELECT @ RESULTSTR = RTRIM (CAST (@CUR_LV AS CHAR (3)))
WHILE @LAST_LV <> @ CUR_LV
BEGIN
    SELECT @ LAST_LV = @ CUR_LV
    SELECT @ RESULTSTR = @ RESULTSTR + ',' + RTRIM (CAST (belongToId AS CHAR (3))), @ CUR_LV = belongToId
         FROM TESTTAB WHERE id = @ CUR_LV
END

SELECT @RESULTSTR

---------------------
1,2,3,4,5

(1 line affected)

wgfxman

CREATE FUNCTION GET_ALLLV (@LV INT)
returns varchar (8000)
as
BEGIN
    DECLARE @RESULTSTR VARCHAR (4000)
    DECLARE @CUR_LV INT
    DECLARE @LAST_LV INT

    SELECT @ CUR_LV = @ LV-here is the entry
    SELECT @ LAST_LV = -1
    SELECT @ RESULTSTR = RTRIM (CAST (@CUR_LV AS CHAR (3)))
    WHILE @LAST_LV <> @ CUR_LV
    BEGIN
        SELECT @ LAST_LV = @ CUR_LV
        SELECT @ RESULTSTR = @ RESULTSTR + ',' + RTRIM (CAST (belongToId AS CHAR (3))), @ CUR_LV = belongToId
             FROM TESTTAB WHERE id = @ CUR_LV
    END
    RETURN @RESULTSTR
END
GO

SELECT id, dbo.GET_ALLLV (id) FROM TESTTAB

id
--------------------------------
1 1,2,3,4,5
2 2,3,4,5
3 3,4,5
4 4,5

(4 lines affected)

adgulyhq1234

Haha. . Exactly what I want. . . . Not much at all. Not a little bit. . . .