有这么这一张表,是统计每个月肉类品价格的,而且是分地区的。有以下几个字段,
meet_type:肉的种类,包括鸡鸭鱼猪狗牛羊等
place:地区
price:价格
p_date:时间,每周一记录
现在需要用一句sql语句查找出价格上涨最快的肉类前三名,时间区间为A月-B月,记住是上涨最快的,不是上涨最大的。
meet_type:肉的种类,包括鸡鸭鱼猪狗牛羊等
place:地区
price:价格
p_date:时间,每周一记录
现在需要用一句sql语句查找出价格上涨最快的肉类前三名,时间区间为A月-B月,记住是上涨最快的,不是上涨最大的。
create table t1
(
diqu varchar(10),
roulei varchar(10),
jiage money,
riqi datetime
)
insert into t1
select '北京', '猪肉', 15.20 ,'2011-05-01' union all
select '北京', '鱼肉', 8.90 ,'2011-05-01' union all
select '北京', '鸡肉', 21.50 ,'2011-05-01' union all
select '北京', '牛肉', 30.80 ,'2011-05-01' union all
select '北京', '羊肉', 28.80 ,'2011-05-01' union all
select '天津', '猪肉', 13.20 ,'2011-05-01' union all
select '天津', '鱼肉', 7.60 ,'2011-05-01' union all
select '天津', '鸡肉', 18.50 ,'2011-05-01' union all
select '天津', '牛肉', 28.30 ,'2011-05-01' union all
select '天津', '羊肉', 25.20 ,'2011-05-01' union all
select '上海', '猪肉', 16.20 ,'2011-05-01' union all
select '上海', '鱼肉', 10.90 ,'2011-05-01' union all
select '上海', '鸡肉', 23.50 ,'2011-05-01' union all
select '上海', '牛肉', 32.80 ,'2011-05-01' union all
select '上海', '羊肉', 31.80 ,'2011-05-01' union all
select '北京', '猪肉', 18.20 ,'2011-05-08' union all
select '北京', '鱼肉', 11.90 ,'2011-05-08' union all
select '北京', '鸡肉', 23.50 ,'2011-05-08' union all
select '北京', '牛肉', 29.80 ,'2011-05-08' union all
select '北京', '羊肉', 26.80 ,'2011-05-08' union all
select '天津', '猪肉', 15.20 ,'2011-05-08' union all
select '天津', '鱼肉', 13.60 ,'2011-05-08' union all
select '天津', '鸡肉', 20.50 ,'2011-05-08' union all
select '天津', '牛肉', 30.30 ,'2011-05-08' union all
select '天津', '羊肉', 27.20 ,'2011-05-08' union all
select '上海', '猪肉', 18.20 ,'2011-05-08' union all
select '上海', '鱼肉', 13.90 ,'2011-05-08' union all
select '上海', '鸡肉', 22.50 ,'2011-05-08' union all
select '上海', '牛肉', 31.80 ,'2011-05-08' union all
select '上海', '羊肉', 33.80 ,'2011-05-08'
select * from t1;with aaa as
(select a.diqu as diqu,a.roulei as roulei,a.jiage as jiage1,b.jiage as jiage2 from
(select * from t1 where riqi='2011-05-01' ) as a
inner join (select * from t1 where riqi='2011-05-08') as b on a.diqu=b.diqu and a.roulei=b.roulei
)
,bbb as
(select *,jiage2-jiage1 as jiagecha from aaa)
,ccc as
(select ROW_NUMBER() over(partition by diqu order by jiagecha desc) as rowindex,* from bbb)
select * from ccc where rowindex <4各地区肉类上涨幅度最快前三
将2楼的语句整理一下如下:
select rowindex,* from (
SELECT row_number() over(partition by diqu order by jiacha desc) as rowindex,* from
(
Select a.diqu,a.roulei,a.jiage as jiage1,b.jiage as jiage2,b.jiage-a.jiage as jiacha from
(select * from t1 where riqi='2011-05-01') as a INNER JOIN
(select * from t1 where riqi='2011-05-08') as b on a.diqu=b.diqu and a.roulei=b.roulei
) as aaa
) as bbb where rowindex<=3对于习惯了SQL2000的童鞋来说,新的关键词是 ROW_NUMBER ,这个可以自己查一下SQL2005的联机丛书。
select top 3 a.*,b.riqi,b.jiage,(b.jiage-a.jiage)上涨幅度
from t1 a,t1 b
where a.riqi='2011-05-01' and b.riqi='2011-05-08'
and a.diqu=b.diqu and a.roulei=b.roulei
order by b.jiage-a.jiage desc/*
diqu roulei jiage riqi riqi jiage 上涨幅度
---------- ---------- --------------------- ----------------------- ----------------------- --------------------- ---------------------
天津 鱼肉 7.60 2011-05-01 00:00:00.000 2011-05-08 00:00:00.000 13.60 6.00
北京 鱼肉 8.90 2011-05-01 00:00:00.000 2011-05-08 00:00:00.000 11.90 3.00
北京 猪肉 15.20 2011-05-01 00:00:00.000 2011-05-08 00:00:00.000 18.20 3.00(3 行受影响)*/select * from t1 where riqi='2011-05-01' select * from t1 where riqi='2011-05-08'and '2011-05-08'
and not exists
(select 1 from t1 as tb where tb.diqu=t1.diqu and tb.roulei=t1.roulei
and tb.riqi between '2011-05-01' and '2011-05-08'
and tb.riqi<t1.riqi)select * from t1 where riqi between '2011-05-01' and '2011-05-08'
and not exists
(select 1 from t1 as tb where tb.diqu=t1.diqu and tb.roulei=t1.roulei
and tb.riqi between '2011-05-01' and '2011-05-08'
and tb.riqi>t1.riqi)
(
diqu varchar(10),
roulei varchar(10),
jiage money,
riqi datetime
)
insert into t1
select '北京', '猪肉', 15.20 ,'2011-05-01' union all
select '北京', '鱼肉', 8.90 ,'2011-05-01' union all
select '北京', '鸡肉', 21.50 ,'2011-05-01' union all
select '北京', '牛肉', 30.80 ,'2011-05-01' union all
select '北京', '羊肉', 28.80 ,'2011-05-01' union all
select '天津', '猪肉', 13.20 ,'2011-05-01' union all
select '天津', '鱼肉', 7.60 ,'2011-05-01' union all
select '天津', '鸡肉', 18.50 ,'2011-05-01' union all
select '天津', '牛肉', 28.30 ,'2011-05-01' union all
select '天津', '羊肉', 25.20 ,'2011-05-01' union all
select '上海', '猪肉', 16.20 ,'2011-05-01' union all
select '上海', '鱼肉', 10.90 ,'2011-05-01' union all
select '上海', '鸡肉', 23.50 ,'2011-05-01' union all
select '上海', '牛肉', 32.80 ,'2011-05-01' union all
select '上海', '羊肉', 31.80 ,'2011-05-01' union all
select '北京', '猪肉', 18.20 ,'2011-05-08' union all
select '北京', '鱼肉', 11.90 ,'2011-05-08' union all
select '北京', '鸡肉', 23.50 ,'2011-05-08' union all
select '北京', '牛肉', 29.80 ,'2011-05-08' union all
select '北京', '羊肉', 26.80 ,'2011-05-08' union all
select '天津', '猪肉', 15.20 ,'2011-05-08' union all
select '天津', '鱼肉', 13.60 ,'2011-05-08' union all
select '天津', '鸡肉', 20.50 ,'2011-05-08' union all
select '天津', '牛肉', 30.30 ,'2011-05-08' union all
select '天津', '羊肉', 27.20 ,'2011-05-08' union all
select '上海', '猪肉', 18.20 ,'2011-05-08' union all
select '上海', '鱼肉', 13.90 ,'2011-05-08' union all
select '上海', '鸡肉', 22.50 ,'2011-05-08' union all
select '上海', '牛肉', 31.80 ,'2011-05-08' union all
select '上海', '羊肉', 33.80 ,'2011-05-08'--每个地区的前三名
select diqu,roulei,ltrim(jiage)+'-'+ltrim(jiage1),convert(varchar(100),riqi,23)+'~'+convert(varchar(100),riqi1,23)
from
(select a.*,b.riqi as riqi1,b.jiage as jiage1,id=row_number() over (partition by a.diqu order by b.jiage-a.jiage desc)
from t1 a,t1 b
where a.riqi='2011-05-01' and b.riqi='2011-05-08'
and a.diqu=b.diqu and a.roulei=b.roulei) tb
where id<=3/*
diqu roulei
---------- ---------- -------------------------------------------------
北京 猪肉 15.20-18.20 2011-05-01~2011-05-08
北京 鱼肉 8.90-11.90 2011-05-01~2011-05-08
北京 鸡肉 21.50-23.50 2011-05-01~2011-05-08
上海 鱼肉 10.90-13.90 2011-05-01~2011-05-08
上海 猪肉 16.20-18.20 2011-05-01~2011-05-08
上海 羊肉 31.80-33.80 2011-05-01~2011-05-08
天津 鱼肉 7.60-13.60 2011-05-01~2011-05-08
天津 鸡肉 18.50-20.50 2011-05-01~2011-05-08
天津 牛肉 28.30-30.30 2011-05-01~2011-05-08(9 行受影响)
*/