--> 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 行受影响)*/
liangCK 你的算法写死了。 我希望灵活一点 占80%,而不是写死800元
--> 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小梁 于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 呢?
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
--> 生成测试数据: #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 行受影响)*/
我希望灵活一点 占80%,而不是写死800元
--> 生成测试数据: #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 行受影响)*/
--> 生成测试数据: #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
呢?
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
因此用cursor未见效率会低多少。