22,209
社区成员
发帖
与我相关
我的任务
分享
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_LgID
Insert 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,11
Create 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'
-- 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))
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=1
ID TL_NAME
----------- --------------------
1 LB1
3 LB3
4 LB4
(3 row(s) affected)
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))
)M
TL_NAME TG_NAME
-------------------- --------------------
LB1 LG2
LG9
LG1
LB3 LG9
LG11
LG1
LG6
LB4 LG9
LG11
(9 row(s) affected)
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
)M
TL_NAME TG_NAME
-------------------- --------------------
LB1 LG2
LG9
LG1
LB2 LG1
LG11
LB3 LG9
LG11
LG1
LG6
LB4 LG9
LG11
(11 row(s) affected)
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))