WITH cte
AS (SELECt storeid,
numemployees,
( numemployees * 100 ) / SUM(numemployees) OVER (PARTITION BY 1)
AS
percentofstores
FROM stores)
SELECt *
FROM cte
WHERe percentofstores >= 30
ORDER BY numemployees desc
工作演示
不使用SUM / OVER的替代方法
SELECt s.storeid, s.numemployees FROM (SELECt SUM(numemployees) AS [tots] FROM stores) AS t, stores s WHERe CAST(numemployees AS DECIMAL(15, 5)) / tots >= .3 ORDER BY s.numemployees desc
工作演示
请注意，在第二个版本中，我决定不除以100。这需要强制转换为十进制，否则将隐式转换为int，导致不返回任何记录
同样，我也不太清楚您是否需要这样做，但是您可以将其添加
TOP 1到两个查询中，并将结果限制为商店数量最多的商店，即超过30％的商店
更新
根据您的评论，听起来可以解释凯文（Kevin）
您需要从员工人数最多的商店开始，一直到直到您拥有至少30％的行
这很困难，因为它需要一个运行百分比，并且它有一个装箱问题，但这确实起作用。请注意，我还包括了其他两个测试用例（百分比完全相等，并且刚好在前两个合计的百分比之内）
工作演示
DECLARE @percent DECIMAL (20, 16)SET @percent = 0.3--Other test values--SET @percent = 0.6992547128452433--SET @percent = 0.6992547128452434;WITH sums AS (SELECt DISTINCT s.storeid, s.numemployees, s.numemployees + Coalesce(SUM(s2.numemployees) OVER ( PARTITION BY s.numemployees), 0) runningsum FROM stores s LEFT JOIN stores s2 ON s.numemployees < s2.numemployees), percents AS (SELECt storeid, numemployees, runningsum, CAST(runningsum AS DECIMAL(15, 5)) / tots.total running_percent, Row_number() OVER (ORDER BY runningsum, storeid ) rn FROM sums, (SELECt SUM(numemployees) total FROM stores) AS tots) SELECt p.storeID, p.numemployees, p.running_percent, p.running_percent, p.rn FROM percents p CROSS JOIN (SELECt MAX(rn) rn FROM percents WHERe running_percent = @percent) exactpercent LEFT JOIN (SELECt MAX(rn) rn FROM percents WHERe running_percent <= @percent) underpercent ON p.rn <= underpercent.rn OR ( exactpercent.rn IS NULL AND p.rn <= underpercent.rn + 1 ) WHERe underpercent.rn is not null or p.rn = 1
