数据表:
t_product
fid famount fname fnumber fdate我现在想统计
2011-1-1至2011-1-31之间 所有商品合计为1000的所有商品信息
这个SQL改怎么写。
重点是要先汇总所有商品得到所有商品的汇总金额,并且根据这个汇总金额查询满足所有记录商品金额合计不小于为这个汇总金额的所有商品信息。
t_product
fid famount fname fnumber fdate我现在想统计
2011-1-1至2011-1-31之间 所有商品合计为1000的所有商品信息
这个SQL改怎么写。
重点是要先汇总所有商品得到所有商品的汇总金额,并且根据这个汇总金额查询满足所有记录商品金额合计不小于为这个汇总金额的所有商品信息。
--按1楼日期那种算法!select *
from tb t
where (select sum(famount) from tb where fdate between '2011-1-1' and t.fdate) <= 1000
and convert(varchar(10),fdate,120) between '2011-01-01' and '2011-01-31'
and fdate <='2011-1-31' group by fname and sum(famount)=1000
(
select fname,sum(famount) as famount from tb group by fname
)
select
*
from
f a,tb b
where
a.famount<1000 and a.fbname=b.fname
and
convert(varchar(10),b.fdate,120) between '2011-01-01' and '2011-01-31'
fid famount fname fnumber fdate
1 200 茶 001 2011-01-01
2 300 陶瓷 002 2011-01-09
3 90.30 书 003 2011-01-10
4 231 书架 004 2011-01-13
5 110 风扇 005 2011-01-17
6 220 鞋柜 006 2011-01-20现在需要查询指定一个金额,并查询满足该金额条件的数据,如下:
1、查询汇总金额为500的商品信息,结果如下:
fid famount fname fnumber fdate
1 200 茶 001 2011-01-01
2 300 陶瓷 002 2011-01-092、查询商品汇总金额为330的商品信息,结果如下:
fid famount fname fnumber fdate
5 110 风扇 005 2011-01-17
6 220 鞋柜 006 2011-01-20
1 200 茶 001 2011-01-01
2 300 陶瓷 002 2011-01-09
3 90.30 书 003 2011-01-10
4 231 书架 004 2011-01-13
5 110 风扇 005 2011-01-17
6 220 鞋柜 006 2011-01-20
7 280 电灯 005 2011-01-17
8 220 书桌 006 2011-01-20那么查询汇总金额为500的商品信息,结果如下:
fid famount fname fnumber fdate
1 200 茶 001 2011-01-01
2 300 陶瓷 002 2011-01-09
和
fid famount fname fnumber fdate
7 280 电灯 005 2011-01-17
8 220 书桌 006 2011-01-20
create table tb(fid int,famount decimal(18,2),fname varchar(10),fnumber varchar(10),fdate datetime)
insert into tb values(1 ,200 ,'茶' ,'001' ,'2011-01-01')
insert into tb values(2 ,300 ,'陶瓷' ,'002' ,'2011-01-09')
insert into tb values(3 ,90.30 ,'书' ,'003' ,'2011-01-10')
insert into tb values(4 ,231 ,'书架' ,'004' ,'2011-01-13')
insert into tb values(5 ,110 ,'风扇' ,'005' ,'2011-01-17')
insert into tb values(6 ,220 ,'鞋柜' ,'006' ,'2011-01-20')
godeclare @cnt as int
set @cnt = 500select m.* from tb m, tb n where m.famount + n.famount = 500 order by m.fiddrop table tb/*
fid famount fname fnumber fdate
----------- -------------------- ---------- ---------- ------------------------------------------------------
1 200.00 茶 001 2011-01-01 00:00:00.000
2 300.00 陶瓷 002 2011-01-09 00:00:00.000(所影响的行数为 2 行)
*/
create table tb(fid int,famount decimal(18,2),fname varchar(10),fnumber varchar(10),fdate datetime)
insert into tb values(1 ,200 ,'茶' ,'001' ,'2011-01-01')
insert into tb values(2 ,300 ,'陶瓷' ,'002' ,'2011-01-09')
insert into tb values(3 ,90.30 ,'书' ,'003' ,'2011-01-10')
insert into tb values(4 ,231 ,'书架' ,'004' ,'2011-01-13')
insert into tb values(5 ,110 ,'风扇' ,'005' ,'2011-01-17')
insert into tb values(6 ,220 ,'鞋柜' ,'006' ,'2011-01-20')
godeclare @cnt as int
set @cnt = 500select m.* from
(
select t.* , newfamount = (select sum(famount) from tb where famount <= t.famount) from tb t
) m where newfamount <=
(
select top 1 newfamount from
(
select t.* , newfamount = (select sum(famount) from tb where famount <= t.famount) from tb t
) m
where newfamount >= @cnt
order by newfamount
)drop table tb/*
fid famount fname fnumber fdate newfamount
----------- -------------------- ---------- ---------- ------------------------------------------------------ ----------------------------------------
1 200.00 茶 001 2011-01-01 00:00:00.000 400.30
3 90.30 书 003 2011-01-10 00:00:00.000 90.30
5 110.00 风扇 005 2011-01-17 00:00:00.000 200.30
6 220.00 鞋柜 006 2011-01-20 00:00:00.000 620.30(所影响的行数为 4 行)
*/