表格式如下
日期 数量
2010.10.1 200
2010.10.2 140
....
2010.11.5 300 --->假如此时 累计“数量” 占 “总量(所有数量之和)”的9.89%
2010.11.6 700 --->假如此时 累计“数量” 占 “总量(所有数量之和)”的10.01%
....
2012.12.25 3801、如何用SQL查询出 从2010.10.1至某天,累计的“数量”刚好占到“总量(所有数量之和)”的10%以上;(即,查询出2010.11.6)
2、如何取出满足这10%的记录(即,2010.10.1至2010.11.6间的记录)
日期 数量
2010.10.1 200
2010.10.2 140
....
2010.11.5 300 --->假如此时 累计“数量” 占 “总量(所有数量之和)”的9.89%
2010.11.6 700 --->假如此时 累计“数量” 占 “总量(所有数量之和)”的10.01%
....
2012.12.25 3801、如何用SQL查询出 从2010.10.1至某天,累计的“数量”刚好占到“总量(所有数量之和)”的10%以上;(即,查询出2010.11.6)
2、如何取出满足这10%的记录(即,2010.10.1至2010.11.6间的记录)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (date datetime,nn int)
insert into #tb
select '2010.10.1',100 union all
select '2010.10.2',140 union all
select '2010.10.3',140 union all
select '2010.10.4',140 union all
select '2010.11.5',300 union all
select '2010.11.6',700 union all
select '2010.11.7',300 union all
select '2010.11.8',300 union all
select '2012.12.25',380select top 1 *
from
(
select *,bl=(select sum(nn)*100.0 from #tb where date<=t.date)/(select sum(nn) from #tb)
from #tb t
)t
where bl>10
order by bldate nn bl
----------------------- ----------- ---------------------------------------
2010-10-03 00:00:00.000 140 15.200000000000(1 行受影响)
select top 10 percent 日期,数量
from 表1
order by 日期
from
(
select *,bl=(select sum(nn)*100.0 from #tb where date<=t.date)/(select sum(nn) from #tb)
from #tb t
)t
where bl<10
date nn bl
----------------------- ----------- ---------------------------------------
2010-10-01 00:00:00.000 100 4.000000000000
2010-10-02 00:00:00.000 140 9.600000000000(2 行受影响)
将表改为:
select '2010.10.1',100 union all
select '2010.10.2',40 union all
select '2010.10.3',240 union all
select '2010.10.4',340 union all
select '2010.11.5',50 union all
select '2010.11.6',70 union all
select '2010.11.7',500 union all
select '2010.11.8',300 union all
select '2012.12.25',100再请问下,如果按“数值”排序,如何取 “数量”从小到大,累计的“数量”刚好占到“总量(所有数量之和)”的30%以上,即取出(10.2,11.5,11.6,12.25...等记录),
即是说,先排序再查询另一贴已给你结40分,请再解答下,谢谢了
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (date datetime,nn int)
insert into #tb
select '2010.10.1',100 union all
select '2010.10.2',40 union all
select '2010.10.3',240 union all
select '2010.10.4',340 union all
select '2010.11.5',50 union all
select '2010.11.6',70 union all
select '2010.11.7',500 union all
select '2010.11.8',300 union all
select '2012.12.25',100
select top 1 *
from
(
select *,bl=(select sum(nn)*100.0 from #tb where nn<=t.nn)/(select sum(nn) from #tb)
from #tb t
)t
where bl>30
order by bl/*
date nn bl
----------------------- ----------- ---------------------------------------
2010-11-05 00:00:00.000 300 56.800000000000
*/
select *
from
(
select *,bl=(select sum(nn)*100.0 from #tb where nn<=t.nn)/(select sum(nn) from #tb)
from #tb t
)t
where bl<=30
date nn bl
----------------------- ----------- ---------------------------------------
2010-10-01 00:00:00.000 100 20.689655172413
2010-10-02 00:00:00.000 40 2.298850574712
2010-11-05 00:00:00.000 50 5.172413793103
2010-11-06 00:00:00.000 70 9.195402298850
2012-12-25 00:00:00.000 100 20.689655172413(5 行受影响)这个拿到mysql 那边能不能运行,还真不清楚。
select 日期
from 表格式如下 a
where (select sum(数量) from 表格式如下 where 日期=<a.日期)>=(select sum(数量)*10% from 表格式如下)
order by 日期
limit 1
select *
from 表格式如下 a
where (select sum(数量) from 表格式如下 where 日期<a.日期)<(select sum(数量)*10% from 表格式如下)
order by 日期