如何来写这个一对多查询语句？

Question

Create Table [CNode_Test](
ID BigInt IDENTITY(1,1) Not Null,
T_LbID Int,
T_LgID Int
)
Alter Table [CNode_Test] Add Constraint PK_cnodeTest_ID Primary Key Clustered(ID)
Alter Table [CNode_Test] Add Constraint DF_cnodeTest_LbId Default 0 For T_LbID
Alter Table [CNode_Test] Add Constraint DF_cnodeTest_LgId Default 0 For T_LgIDInsert Into [CNode_Test]
 (T_LbID,T_LgID)
 Select 1,2 Union All
 Select 1,9 Union All
 Select 1,1 Union All
 Select 2,1 Union All
 Select 2,11 Union All
 Select 3,9 Union All
 Select 3,11 Union All
 Select 3,1 Union All
 Select 3,6 Union All
 Select 4,9 Union All
 Select 4,11Create Table [CNode_TestLb](
ID Int IDENTITY(1,1) Not Null,
TL_Name nVarChar(20)
)
Alter Table [CNode_TestLb] Add Constraint PK_cnodeTestLb_ID Primary Key Clustered(ID)
Alter Table [CNode_TestLb] Add constraint DF_cnodeTestLb_Name Default '' For TL_Name
Insert Into [CNode_TestLb]
 (TL_Name)
Select 'LB1' Union All
Select 'LB2' Union All
Select 'LB3' Union All
Select 'LB4'
Create Table [CNode_TestLg](
ID Int IDENTITY(1,1) Not Null,
TG_Name nVarChar(20)
)
Alter Table [CNode_TestLg] Add Constraint PK_cnodeTestLg_ID Primary Key Clustered(ID)
Alter Table [CNode_TestLg] Add Constraint DF_cnodeTestLg_Name Default '' For TG_Name
Insert Into [CNode_TestLg]
(TG_Name)
Select 'LG1' Union All
Select 'LG2' Union All
Select 'LG3' Union All
Select 'LG4' Union All
Select 'LG5' Union All
Select 'LG6' Union All
Select 'LG7' Union All
Select 'LG8' Union All
Select 'LG9' Union All
Select 'LG10' Union All
Select 'LG11' --------------------------------------
[CNode_TestLb]与[CNode_TestLg]表是通过[CNode_Test]表相关联的，一对多的关联表。比如LB1对应着多个LG记录
--------------------------------------
如：
 LB1 |----- LG1
     |----- LG2
     |----- LG9如何查询即与LG9有关系且与LG1或LG11有关系的记录

Answer 1

select * from CNode_Test t where
exists (select 1 from CNode_Test where T_LbID=t.T_LbID and T_LgID=9)
and exists (select 1 from CNode_Test where T_LbID=t.T_LbID and (T_LgID=1 or T_LgID=11))

Answer 2

SELECT CASE WHEN ROW=1 THEN TL_NAME ELSE '' END TL_NAME,TG_NAME
FROM (
SELECT N.TL_NAME,G.TG_NAME,ROW_NUMBER()OVER(PARTITION BY TL_NAME ORDER BY GETDATE())ROW
FROM [CNODE_TEST] C
LEFT JOIN [CNODE_TESTLB] N ON C.T_LBID=N.ID
LEFT JOIN [CNODE_TESTLG] G ON C.T_LGID=G.ID
)MTL_NAME              TG_NAME
-------------------- --------------------
LB1                  LG2
                     LG9
                     LG1
LB2                  LG1
                     LG11
LB3                  LG9
                     LG11
                     LG1
                     LG6
LB4                  LG9
                     LG11(11 row(s) affected)

Answer 3

整理一下：
SELECT CASE WHEN ROW=1 THEN TL_NAME ELSE '' END TL_NAME,TG_NAME
FROM (
SELECT N.TL_NAME,G.TG_NAME,ROW_NUMBER()OVER(PARTITION BY TL_NAME ORDER BY GETDATE())ROW
FROM (
select * from CNode_Test t where
exists (select 1 from CNode_Test where T_LbID=t.T_LbID and T_LgID=9)
and exists (select 1 from CNode_Test where T_LbID=t.T_LbID and (T_LgID=1 or T_LgID=11))
 )C
LEFT JOIN [CNODE_TESTLB] N ON C.T_LBID=N.ID
LEFT JOIN [CNODE_TESTLG] G ON C.T_LGID=G.ID
and exists (select 1 from CNode_Test where T_LbID=c.T_LbID and (T_LgID=1 or T_LgID=11))
)MTL_NAME              TG_NAME
-------------------- --------------------
LB1                  LG2
                     LG9
                     LG1
LB3                  LG9
                     LG11
                     LG1
                     LG6
LB4                  LG9
                     LG11(9 row(s) affected)

Answer 4

@zsh0809
这个结果不对，不应该包括LB2
 
[CNode_TestLb]这个表中的记录必须与LG9这个记录关联且关联到LG1或LG11

Answer 5

想要的查询结果:
--------------
ID TL_Name
1  LB1
3  LB3
4  LB4
--------------
因为LB2没有关联到LG9这个点

Answer 6

需要TG_NAME的话就参考8#
SELECT T_LbID  ID,TL_NAME
FROM (
SELECT c.T_LbID,N.TL_NAME,G.TG_NAME,ROW_NUMBER()OVER(PARTITION BY TL_NAME ORDER BY GETDATE())ROW
FROM (
select * from CNode_Test t where
exists (select 1 from CNode_Test where T_LbID=t.T_LbID and T_LgID=9)
and exists (select 1 from CNode_Test where T_LbID=t.T_LbID and (T_LgID=1 or T_LgID=11))
 )C
LEFT JOIN [CNODE_TESTLB] N ON C.T_LBID=N.ID
LEFT JOIN [CNODE_TESTLG] G ON C.T_LGID=G.ID
and exists (select 1 from CNode_Test where T_LbID=c.T_LbID and (T_LgID=1 or T_LgID=11))
)M
WHERE ROW=1ID          TL_NAME
----------- --------------------
1           LB1
3           LB3
4           LB4(3 row(s) affected)

Answer 7

-- having
select a.ID,max(a.TL_Name)TL_Name from CNode_TestLb a join CNode_Test b on a.ID=b.T_LbID group by a.ID
having sum(case T_LgID when 9 then 9 when 1 then 1 when 11 then 2 else 0 end) >= 10-- exists
select * from CNode_TestLb t where
exists (select 1 from CNode_Test where T_LbID=t.ID and T_LgID=9)
and exists (select 1 from CNode_Test where T_LbID=t.ID and (T_LgID=1 or T_LgID=11))