select id,sl from 表 when sl>80% *( select sum(sl) from 表 )
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL DROP TABLE #TCREATE TABLE #T (ID VARCHAR(10), SL INT)INSERT INTO #T SELECT 'A',5 UNION ALL SELECT 'B',20 UNION ALL SELECT 'C',10 UNION ALL SELECT 'D',30 UNION ALL SELECT 'E',35 WITH CTE_1 AS (SELECT *,SUM(SL) OVER (PARTITION BY 1) AS TOTAL FROM #T), CTE_2 AS (SELECT *,CAST(ID AS VARCHAR) AS ID_GROUP,SL AS SL_TOTAL,1 AS LEVEL FROM CTE_1 UNION ALL SELECT A.*,CAST(ID_GROUP+A.ID AS VARCHAR),SL_TOTAL+A.SL,LEVEL+1 FROM CTE_1 A JOIN CTE_2 B ON CHARINDEX(A.ID,B.ID_GROUP)<1 AND B.ID>A.ID)SELECT * FROM CTE_2 A WHERE SL_TOTAL>=TOTAL*0.8
5条记录求和100,其中id为b,d,e 三条记录合计数为85,超过80%
from 表
when sl>80% *(
select sum(sl)
from 表
)
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #TCREATE TABLE #T
(ID VARCHAR(10),
SL INT)INSERT INTO #T
SELECT 'A',5 UNION ALL
SELECT 'B',20 UNION ALL
SELECT 'C',10 UNION ALL
SELECT 'D',30 UNION ALL
SELECT 'E',35
WITH CTE_1
AS
(SELECT *,SUM(SL) OVER (PARTITION BY 1) AS TOTAL FROM #T),
CTE_2
AS
(SELECT *,CAST(ID AS VARCHAR) AS ID_GROUP,SL AS SL_TOTAL,1 AS LEVEL
FROM CTE_1
UNION ALL
SELECT A.*,CAST(ID_GROUP+A.ID AS VARCHAR),SL_TOTAL+A.SL,LEVEL+1
FROM CTE_1 A
JOIN CTE_2 B ON CHARINDEX(A.ID,B.ID_GROUP)<1 AND B.ID>A.ID)SELECT *
FROM CTE_2 A
WHERE SL_TOTAL>=TOTAL*0.8