select *
from 销售明细 a
where exists(select 1 from 销售明细 where 销售单 < a.销售单 having sum(销售金额) <=1000)
from 销售明细 a
where exists(select 1 from 销售明细 where 销售单 < a.销售单 having sum(销售金额) <=1000)
解决方案 »
- 存储过程中top后面不能放变量的问题
- 如何编写以下的asp语句
- 连接sqlserver超时问题?
- 如何在子查询中使用序号?
- EXEC master..xp_cmdshell 'bcp master.dbo.sysobjects out c:\temp1.xls -c -S"oa" -U"sa" -P""' 出错如何处理?
- SQL Server 2000怎样设置才能不区分字段名称的大小写?急!
- 怎么实现这个问题?
- 两个不同数据库的表之间能建立关联吗?
- 安装SQL SERVER遇到的问题!!!!!
- 小白问个简单的问题
- xp2上安装sql server 2000 出现错误!!!
- 查询小问题 (在线等)
insert @t
select '0001','2006-1-1',100 union all
select '0002','2006-1-5',150 union all
select '0008','2006-1-6',130 union all
select '0004','2006-1-9',200 union all
select '0007','2006-1-14',350 union all
select '0003','2006-1-28',160 union all
select '0005','2006-1-18',300 union all
select '0006','2006-1-21',100 union all
select '0009','2006-1-13',600 union all
select '0011','2006-1-10',100
select *
from @t a
where exists(select 1 from @t where 销售单 <= a.销售单 having sum(销售金额) <=1000)/*
销售单 销售日期 销售金额
---- ------------------------------------------------------ -----------
0001 2006-01-01 00:00:00.000 100
0002 2006-01-05 00:00:00.000 150
0004 2006-01-09 00:00:00.000 200
0003 2006-01-28 00:00:00.000 160
0005 2006-01-18 00:00:00.000 300*/
if object_id('销售明细') is not null drop table 销售明细
go
select '0001' as 销售单, cast('2006-1-1' as datetime) as 销售日期, 100 as 销售金额
into 销售明细
union select '0002', '2006-1-5', 150
union select '0008', '2006-1-6', 130
union select '0004', '2006-1-9', 200
union select '0007', '2006-1-14', 350
union select '0003', '2006-1-28', 160
union select '0005', '2006-1-18', 300
union select '0006', '2006-1-21', 100
union select '0009', '2006-1-13', 600
union select '0011', '2006-1-10', 100select * from 销售明细
/*
销售单 销售日期 销售金额
0001 2006-01-01 00:00:00.000 100
0002 2006-01-05 00:00:00.000 150
0003 2006-01-28 00:00:00.000 160
0004 2006-01-09 00:00:00.000 200
0005 2006-01-18 00:00:00.000 300
0006 2006-01-21 00:00:00.000 100
0007 2006-01-14 00:00:00.000 350
0008 2006-01-06 00:00:00.000 130
0009 2006-01-13 00:00:00.000 600
0011 2006-01-10 00:00:00.000 100
*/
select *
from 销售明细 a
where exists(select 1 from 销售明细 where 销售单 <= a.销售单 having sum(销售金额) <=1000)
/*
销售单 销售日期 销售金额
0001 2006-01-01 00:00:00.000 100
0002 2006-01-05 00:00:00.000 150
0003 2006-01-28 00:00:00.000 160
0004 2006-01-09 00:00:00.000 200
0005 2006-01-18 00:00:00.000 300
*/drop table 销售明细
------------------------------
0001 2006-1-1 A 100
0001 2006-1-1 B 100
0001 2006-1-1 C 100
0002 2006-1-5 B 150
0008 2006-1-6 A 130
0004 2006-1-9 B 200
0004 2006-1-9 C 200
0007 2006-1-14 A 350
0003 2006-1-28 B 160
0005 2006-1-18 C 300
0006 2006-1-21 B 100
0009 2006-1-13 B 600
0011 2006-1-10 C 100现想得到"根据销售单号或销售日期排序后, 返回累计销售总金额<=1000的所有记录"销售单号与销售日期只作为排序的方法, 目的是想得到累计金额在一个指定值内的所有记录高手搭救~~~ 不够分再开贴~~
go
select '0001' as 销售单, cast('2006-1-1' as datetime) as 销售日期, 'A' as 货品, 100 as 销售金额
into 销售明细
union select '0001', '2006-1-1', 'B', 100
union select '0001', '2006-1-1', 'C', 100
union select '0002', '2006-1-5', 'B', 150
union select '0008', '2006-1-6', 'A', 130
union select '0004', '2006-1-9', 'B', 200
union select '0004', '2006-1-9', 'C', 200
union select '0007', '2006-1-14', 'A', 350
union select '0003', '2006-1-28', 'B', 160
union select '0005', '2006-1-18', 'C', 300
union select '0006', '2006-1-21', 'B', 100
union select '0009', '2006-1-13', 'B', 600
union select '0011', '2006-1-10', 'C', 100select * from 销售明细
/*
销售单 销售日期 货品 销售金额
0001 2006-01-01 00:00:00.000 A 100
0001 2006-01-01 00:00:00.000 B 100
0001 2006-01-01 00:00:00.000 C 100
0002 2006-01-05 00:00:00.000 B 150
0003 2006-01-28 00:00:00.000 B 160
0004 2006-01-09 00:00:00.000 B 200
0004 2006-01-09 00:00:00.000 C 200
0005 2006-01-18 00:00:00.000 C 300
0006 2006-01-21 00:00:00.000 B 100
0007 2006-01-14 00:00:00.000 A 350
0008 2006-01-06 00:00:00.000 A 130
0009 2006-01-13 00:00:00.000 B 600
0011 2006-01-10 00:00:00.000 C 100
*/
go
if object_id('tempdb..#') is not null drop table #
declare @a varchar(4), @b datetime, @c varchar(1), @d int, @sum int
select @a as 销售单, @b as 销售日期, @c as 货品, @d as 销售金额 into # where 1 = 0
set @sum = 0
declare Test cursor for select * from 销售明细
open test
fetch next from test into @a, @b, @c, @d
while @@fetch_status = 0
begin
if @sum + @d > 1000 break
insert into # values(@a, @b, @c, @d)
set @sum = @sum + @d
fetch next from test into @a, @b, @c, @d
end
close test
deallocate testselect * from #
/*
销售单 销售日期 货品 销售金额
0001 2006-01-01 00:00:00.000 A 100
0001 2006-01-01 00:00:00.000 B 100
0001 2006-01-01 00:00:00.000 C 100
0002 2006-01-05 00:00:00.000 B 150
0003 2006-01-28 00:00:00.000 B 160
0004 2006-01-09 00:00:00.000 B 200*/
drop table #drop table 销售明细