create table gk ( id int identity, sl int, gk nvarchar(20) ) insert into gk select 100,'a' union all select 200,'a' union all select 300,'a' union all select 400,'a' union all select 100,'b' union all select 200,'b'select sum(sl),gk from gk group by gk having sum(sl)<500 是不是这个意思?
300 A 4 400 A create table gk ( id int identity, sl int, gk nvarchar(20) ) insert into gk select 100,'a' union all select 200,'a' union all select 300,'a' union all select 400,'a' union all select 100,'b' union all select 200,'b'select gk 顾客名,sum(sl) 总数 from gk group by gk having sum(sl)<500顾客名 总数 -------------------- ----------- b 300(1 行受影响)
select sum(产品数量) as ss, 顾客名称 from 表 group by 顾客名称 having sum(产品数量)<500 order by ss
declare @a table ( id int identity, sl int, gk nvarchar(20) ) insert into @a select 100,'a' union all select 200,'a' union all select 300,'a' union all select 400,'a' select * from ( select *,(select sum(sl) from @a b where b.id<=a.id) as counts from @a a )x where counts<=500
create table tb(编号 int, 产品数量 int, 顾客名称 varchar(10)) insert into tb values(1 , 100 , 'A') insert into tb values(2 , 200 , 'A') insert into tb values(3 , 300 , 'A') insert into tb values(4 , 400 , 'A') goselect 编号 , 产品数量 , 顾客名称 from ( select t.* , 产品数量合计 = (select sum(产品数量) 产品数量 from tb where 编号 <= t.编号) from tb t ) m where 产品数量合计 <= 500drop table tb/* 编号 产品数量 顾客名称 ----------- ----------- ---------- 1 100 A 2 200 A(所影响的行数为 2 行) */
楼上的不对呀!如果数据如下的时候会显示1、2、4行数据,我只要求显示前几行的和小于500。 编号 产品数量 顾客名称 1 100 A 2 200 A 3 300 A 4 100 A
create table tb(编号 int, 产品数量 int, 顾客名称 varchar(10)) insert into tb values(1 , 100 , 'A') insert into tb values(2 , 200 , 'A') insert into tb values(3 , 300 , 'A') insert into tb values(4 , 100 , 'A') goselect 编号 , 产品数量 , 顾客名称 from ( select t.* , 产品数量合计 = (select sum(产品数量) 产品数量 from tb where 编号 <= t.编号) from tb t ) m where 产品数量合计 <= 500drop table tb/* 编号 产品数量 顾客名称 ----------- ----------- ---------- 1 100 A 2 200 A(所影响的行数为 2 行) */
select * from @a a where (select sum(sl) from @a b where a.id>=b.id)<500
select * from 表 where 产品数量 < 500 order by 编号
SELECT * FROM TableName WHERE 产品ID IN ( SELECT 产品ID FROM ( Select 产品ID,Sum(产品数量) As Num FROM TableName Group by 产品ID ) A WHERE A.Num < 500 )
declare @a table ( id int identity, sl int, gk nvarchar(20) ) insert into @a select 100,'a' union all select 200,'a' union all select 300,'a' union all select 400,'a' select * from ( select *,(select sum(sl) from @a b where b.id<=a.id) as counts from @a a )x where counts<=500
create table gk
(
id int identity,
sl int,
gk nvarchar(20)
)
insert into gk
select 100,'a'
union all
select 200,'a'
union all
select 300,'a'
union all
select 400,'a'
union all
select 100,'b'
union all
select 200,'b'select sum(sl),gk from gk
group by gk
having sum(sl)<500
是不是这个意思?
4 400 A
create table gk
(
id int identity,
sl int,
gk nvarchar(20)
)
insert into gk
select 100,'a'
union all
select 200,'a'
union all
select 300,'a'
union all
select 400,'a'
union all
select 100,'b'
union all
select 200,'b'select gk 顾客名,sum(sl) 总数 from gk
group by gk
having sum(sl)<500顾客名 总数
-------------------- -----------
b 300(1 行受影响)
from 表
group by 顾客名称
having sum(产品数量)<500
order by ss
declare @a table
(
id int identity,
sl int,
gk nvarchar(20)
)
insert into @a
select 100,'a'
union all
select 200,'a'
union all
select 300,'a'
union all
select 400,'a' select * from
(
select *,(select sum(sl) from @a b where b.id<=a.id) as counts from @a a
)x
where counts<=500
insert into tb values(1 , 100 , 'A')
insert into tb values(2 , 200 , 'A')
insert into tb values(3 , 300 , 'A')
insert into tb values(4 , 400 , 'A')
goselect 编号 , 产品数量 , 顾客名称 from
(
select t.* , 产品数量合计 = (select sum(产品数量) 产品数量 from tb where 编号 <= t.编号) from tb t
) m
where 产品数量合计 <= 500drop table tb/*
编号 产品数量 顾客名称
----------- ----------- ----------
1 100 A
2 200 A(所影响的行数为 2 行)
*/
编号 产品数量 顾客名称
1 100 A
2 200 A
3 300 A
4 100 A
create table tb(编号 int, 产品数量 int, 顾客名称 varchar(10))
insert into tb values(1 , 100 , 'A')
insert into tb values(2 , 200 , 'A')
insert into tb values(3 , 300 , 'A')
insert into tb values(4 , 100 , 'A')
goselect 编号 , 产品数量 , 顾客名称 from
(
select t.* , 产品数量合计 = (select sum(产品数量) 产品数量 from tb where 编号 <= t.编号) from tb t
) m
where 产品数量合计 <= 500drop table tb/*
编号 产品数量 顾客名称
----------- ----------- ----------
1 100 A
2 200 A(所影响的行数为 2 行)
*/
select * from @a a
where (select sum(sl) from @a b where a.id>=b.id)<500
SELECT 产品ID FROM (
Select 产品ID,Sum(产品数量) As Num FROM TableName Group by 产品ID
) A WHERE A.Num < 500
)
(
id int identity,
sl int,
gk nvarchar(20)
)
insert into @a
select 100,'a'
union all
select 200,'a'
union all
select 300,'a'
union all
select 400,'a' select * from
(
select *,(select sum(sl) from @a b where b.id<=a.id) as counts from @a a
)x
where counts<=500