假设表结构如下:
id int
Name varchar(20)
qty int统计数据时按id排序,我想找到qty累计>=某数值的最小id举例如下:
1 A 10
2 B 15
3 C 20
4 D 60
如果我想找到累计>=20的id ,则id=2
累计>=40的id ,则id=3
累计>=70的id ,则id=4
如果能同时返回累计值当然更好
id int
Name varchar(20)
qty int统计数据时按id排序,我想找到qty累计>=某数值的最小id举例如下:
1 A 10
2 B 15
3 C 20
4 D 60
如果我想找到累计>=20的id ,则id=2
累计>=40的id ,则id=3
累计>=70的id ,则id=4
如果能同时返回累计值当然更好
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-19 09:04:03
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[Name] nvarchar(1),[qty] int)
Insert tb
select 1,N'A',10 union all
select 2,N'B',15 union all
select 3,N'C',20 union all
select 4,N'D',60
Go
select top 1 id from(
select [id],(select sum([qty])from tb where id<=t.id)cnt from tb t)
t where cnt>=20
select top 1 id from(
select [id],(select sum([qty])from tb where id<=t.id)cnt from tb t)
t where cnt>=70
/*
(4 個資料列受到影響)
id
-----------
2(1 個資料列受到影響)id
-----------
4(1 個資料列受到影響)*/
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-19 09:05:16
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,Name VARCHAR(1),qty INT)
INSERT INTO [tb]
SELECT 1,'A',10 UNION ALL
SELECT 2,'B',15 UNION ALL
SELECT 3,'C',20 UNION ALL
SELECT 4,'D',60select * from [tb]select min(id) from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)K
where cnt>=20
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-19 09:04:03
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[Name] nvarchar(1),[qty] int)
Insert tb
select 1,N'A',10 union all
select 2,N'B',15 union all
select 3,N'C',20 union all
select 4,N'D',60
Go
-->=20
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=20
-->=40
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=40
-->=70
select top 1 id from tb t
where (select sum([qty])from tb where id<=t.id)>=70
/*
(4 個資料列受到影響)
id
-----------
2(1 個資料列受到影響)id
-----------
3(1 個資料列受到影響)id
-----------
4(1 個資料列受到影響)
*/
min(id)
from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where
cnt>=20
declare @a table
(id int ,
Name varchar(20) ,
qty int )
insert @a select
1, 'A' , 10 union all select
2, 'B' , 15 union all select
3, 'C' , 20 union all select
4, 'D' , 60
declare @n int
set @n=20select top 1 id from(
select id,name,qty,hz=(select sum(qty)from @a where id<=t.id) from @a t
)a
where hz>=@n
order by hz id
-----------
2(1 行受影响)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-19 09:11:57
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[qty] int)
insert [tb]
select 1,'A',10 union all
select 2,'B',15 union all
select 3,'C',20 union all
select 4,'D',60
--------------开始查询--------------------------
-----20
select
min(id)
from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where
cnt>=20
----40
select
min(id)
from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where
cnt>=40
----70
select
min(id)
from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)t
where
cnt>=70----------------结果----------------------------
/*
-----------
2(所影响的行数为 1 行)
-----------
3(所影响的行数为 1 行)
-----------
4(所影响的行数为 1 行)*/
select min(id) from
(select *,cnt=(select sum(qty) from tb where id<=t.id)from tb t)K
where cnt>=20
[/code]