Select A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,Count(B.ID) As PoCount
From [CNode_Label] A
Right Join [CNode_Product] B
On CharIndex(','+Cast(A.ID As nVarChar(20))+',',B.PO_LabelLib)>0 And B.PO_StateNum In (5,6)
Where A.LB_StateNum In (5) And (CharIndex(',9,',A.LB_GroupIDStr)>0 And CharIndex(',1,',A.LB_GroupIDStr)>0)
Group By A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,A.LB_OrderNum Having Count(B.ID)>0
Order By PoCount Desc,A.LB_OrderNum Desc,A.ID Desc
From [CNode_Label] A
Right Join [CNode_Product] B
On CharIndex(','+Cast(A.ID As nVarChar(20))+',',B.PO_LabelLib)>0 And B.PO_StateNum In (5,6)
Where A.LB_StateNum In (5) And (CharIndex(',9,',A.LB_GroupIDStr)>0 And CharIndex(',1,',A.LB_GroupIDStr)>0)
Group By A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,A.LB_OrderNum Having Count(B.ID)>0
Order By PoCount Desc,A.LB_OrderNum Desc,A.ID Desc
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
From [CNode_Label] A Right Join [CNode_Product] B
On CharIndex(','+Cast(A.ID As nVarChar(20))+',',B.PO_LabelLib)>0
where B.PO_StateNum In (5,6) --这个条件写在on连接里是无效的
and A.LB_StateNum =5
And (CharIndex(',9,',A.LB_GroupIDStr)>0 And CharIndex(',1,',A.LB_GroupIDStr)>0)
Group By A.ID,A.LB_CnName,A.LB_EnName,A.LB_Pinyin,A.LB_OrderNum
Having Count(B.ID)>0
Order By PoCount Desc,A.LB_OrderNum Desc,A.ID Desc