SELECT * FROM tbMemberInfo a where (select count(1) from tbMemberInfo where ID_Parent = a. ID_Parent)=2 and not exists ( select 1 from tbMemberInfo where ID_Parent = a. ID_Parent and ShuLiang< a.ShuLiang )
应该是最小数量 ShuLiang
DECLARE @TB TABLE([ID] VARCHAR(1), [ID_Parent] VARCHAR(1), [ShuLiang] INT) INSERT @TB SELECT 'A', NULL, 5 UNION ALL SELECT 'B', 'A', 4 UNION ALL SELECT 'C', 'A', 3 UNION ALL SELECT 'D', 'B', 6 UNION ALL SELECT 'E', 'C', 7 UNION ALL SELECT 'F', 'C', 6SELECT A.* FROM @TB AS A JOIN ( SELECT ID_Parent , MIN([ShuLiang]) AS [ShuLiang] FROM @TB GROUP BY ID_Parent HAVING (COUNT(*) = 2) ) T ON A.[ID_Parent]=T.[ID_Parent] AND A.[ShuLiang]=T.[ShuLiang] /* ---- --------- ----------- C A 3 F C 6 */
FROM tbMemberInfo a
where (select count(1) from tbMemberInfo where ID_Parent = a. ID_Parent)=2
and not exists (
select 1 from tbMemberInfo
where ID_Parent = a. ID_Parent
and ShuLiang< a.ShuLiang
)
INSERT @TB
SELECT 'A', NULL, 5 UNION ALL
SELECT 'B', 'A', 4 UNION ALL
SELECT 'C', 'A', 3 UNION ALL
SELECT 'D', 'B', 6 UNION ALL
SELECT 'E', 'C', 7 UNION ALL
SELECT 'F', 'C', 6SELECT A.*
FROM @TB AS A JOIN (
SELECT ID_Parent , MIN([ShuLiang]) AS [ShuLiang]
FROM @TB
GROUP BY ID_Parent
HAVING (COUNT(*) = 2)
) T
ON A.[ID_Parent]=T.[ID_Parent] AND A.[ShuLiang]=T.[ShuLiang]
/*
---- --------- -----------
C A 3
F C 6
*/