查询占总金额80%的企业

[yikun]

企业名称 金额
A 300
B 300
C 200
D 100
E 100

因为(300+300+200)/总额=80%,所以查询结果 应该是A,B,C

这个sql语句怎么写？谢谢

[hsie168518]

ABDE也是啊

[水族杰纶]

那(300+300+100+100)/总额也等於80%呢

[pt1314917]

为什么不是A，B，D，E呢，他们四个加起来也是80%啊。。。

[liangCK]

--> liangCK小梁 于2008-10-08
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (企业名称 VARCHAR(1),金额 INT)
INSERT INTO #T
SELECT 'A','300' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','200' UNION ALL
SELECT 'D','100' UNION ALL
SELECT 'E','100'

--SQL查询如下:

SELECT 企业名称,金额
FROM
(
   SELECT * ,NextMoney=(SELECT SUM(金额) FROM #T WHERE 企业名称<=t.企业名称)
   FROM #T AS t
) AS t
WHERE NextMoney<=800

/*
企业名称 金额
---- -----------
A    300
B    300
C    200

(3 行受影响)

*/

[yikun]

金额排序由大到小

[yikun]

liangCK 你的算法写死了。
我希望灵活一点 占80%，而不是写死800元

[liangCK]

--> liangCK小梁 于2008-10-08
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (企业名称 VARCHAR(1),金额 INT)
INSERT INTO #T
SELECT 'A','300' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','200' UNION ALL
SELECT 'D','100' UNION ALL
SELECT 'E','100'

--SQL查询如下:

SELECT 企业名称,金额
FROM
(
   SELECT * ,NextMoney=(SELECT SUM(金额) FROM #T WHERE 企业名称<=t.企业名称)
   FROM #T AS t,
     (SELECT SUM(金额) 总金额 FROM #T) AS b
) AS t
WHERE NextMoney*100.0/总金额<=80  --这是80%

/*
企业名称 金额
---- -----------
A    300
B    300
C    200

(3 行受影响)

*/

[liangCK]

--> liangCK小梁 于2008-10-08
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (企业名称 VARCHAR(1),金额 INT)
INSERT INTO #T
SELECT 'A','300' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','200' UNION ALL
SELECT 'D','100' UNION ALL
SELECT 'E','100'

--SQL查询如下:

;WITH CTE 
AS
(
  SELECT rid=ROW_NUMBER() OVER(ORDER BY 金额 DESC,企业名称),
         *,SUM(金额) OVER() AS 总金额
  FROM #T
)
SELECT 企业名称,金额
FROM
(
  SELECT * ,NextMoney=(SELECT SUM(金额) FROM CTE WHERE rid<=t.rid)
  FROM CTE AS t
) AS t
WHERE NextMoney*100.0/总金额<=80

/*
企业名称 金额
---- -----------
A    300
B    300
C    200

(3 行受影响)

*/

[让你望见影子的墙]

小梁的程序我执行的结果怎么是空的呢？
测试了下：
SELECT * ,NextMoney=(SELECT SUM(金额) FROM T WHERE 企业名称<=t.企业名称)
FROM T AS t,
(SELECT SUM(金额) 总金额 FROM T) AS b

结果怎么是：
A 300 1000 1000
B 300 1000 1000
C 200 1000 1000
D 100 1000 1000
E 100 1000 1000


呢？

[tim_spac]

set nocount on
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T ([Name] VARCHAR(1), [Fee] Numeric(10,2))
INSERT INTO #T
SELECT 'A','300' UNION ALL
SELECT 'B','300' UNION ALL
SELECT 'C','200' UNION ALL
SELECT 'D','100' UNION ALL
SELECT 'E','100'

declare @name varchar(32), @fee numeric(10,2)
	,@total numeric(10,2), @summary numeric(10,2)
select @total = sum([Fee]) from #T
set @summary=0
declare cur cursor for
select [Name],[Fee] from #T order by [Fee] desc
open cur
fetch next from cur into @name, @fee
while @@fetch_status!=-1 begin
	set @summary = @summary + @fee
	if @summary > @total*.8
		break
	print @name +' '+ cast(@fee as varchar)
fetch next from cur into @name, @fee
end
close cur
deallocate cur

-- A 300.00
-- B 300.00
-- C 200.00

[tim_spac]

实际应用中，数据分布通常是2/8比例，80%的收入来自20%的客户。
因此用cursor未见效率会低多少。