sql难题：排序，比较统计
本人想要实现某产品的销量统计，算法如下：
前两天的销量比较：如果前天比昨天高，则标记为正1；低，则标记为负1；
若持平，则标记为0. 并且按销量由高到低排序。
求高手一条sql语句解决（多条也行）。
已知条件：
表名：goods, 商品名：goodsname,交易时间：goodstime,销量：saleamount.
其中，商品有20种，记录估计1000条。
说明：只求sql,不用考虑其他sql语句以外的问题。
[解决办法]
--查看模拟数据
select goodsname,goodstime,saleamount from goods
/* 模拟数据
a2012-12-10 00:00:00.0005
a2012-12-10 00:00:00.0004
a2012-12-09 00:00:00.0005
a2012-12-09 00:00:00.0003
b2012-12-10 00:00:00.0005
b2012-12-10 00:00:00.0004
b2012-12-09 00:00:00.0005
b2012-12-09 00:00:00.0004
c2012-12-10 00:00:00.0006
c2012-12-10 00:00:00.0004
c2012-12-09 00:00:00.0005
c2012-12-09 00:00:00.0003
d2012-12-10 00:00:00.0005
d2012-12-10 00:00:00.0004
e2012-12-09 00:00:00.0005
e2012-12-09 00:00:00.0003
*/
-- 统计 goodsname 商品名称， Comparison 比较结果， mountYesterday 昨天销售量， mountBeforeYesterday 前天销售量
select goodsname
,case when isnull(mountBeforeYesterday,0)> isnull(mountYesterday,0) then 1 when isnull(mountBeforeYesterday,0)< isnull(mountYesterday,0) then -1 else 0 end Comparison
,isnull(mountYesterday,0) mountYesterday,isnull(mountBeforeYesterday,0) mountBeforeYesterday
from
(select distinct goodsname from goods) t
left join (
select sum(a.saleamount) mountYesterday,a.goodsname gnYesterday
from goods a
where convert(varchar(10),a.goodstime,23) =convert(char(10), dateadd(day,-1,getdate()),23)
group by a.goodsname
) t1 on t1.gnYesterday=t.goodsname
left join (
select sum(b.saleamount) mountBeforeYesterday,b.goodsname gnBeforeYesterday
from goods b
where convert(varchar(10),b.goodstime,23) =convert(char(10), dateadd(day,-2,getdate()),23)
group by b.goodsname
) t2 on t2.gnBeforeYesterday=t.goodsname
/* 统计结果
a-198
b099
c-1108
d-190
e108
*/
[解决办法]
--上个忘排序了，呵呵
--注意，测试数据 12月11日有效
--查看模拟数据
select goodsname,goodstime,saleamount from goods
/* 模拟数据
a 2012-12-10 00:00:00.000 5
a 2012-12-10 00:00:00.000 4
a 2012-12-09 00:00:00.000 5
a 2012-12-09 00:00:00.000 3
b 2012-12-10 00:00:00.000 5
b 2012-12-10 00:00:00.000 4
b 2012-12-09 00:00:00.000 5
b 2012-12-09 00:00:00.000 4
c 2012-12-10 00:00:00.000 6
c 2012-12-10 00:00:00.000 4
c 2012-12-09 00:00:00.000 5
c 2012-12-09 00:00:00.000 3
d 2012-12-10 00:00:00.000 5
d 2012-12-10 00:00:00.000 4
e 2012-12-09 00:00:00.000 5
e 2012-12-09 00:00:00.000 3
*/
-- 统计 goodsname 商品名称， Comparison 比较结果， mountYesterday 昨天销售量， mountBeforeYesterday 前天销售量,total 销售量
select goodsname
,case when isnull(mountBeforeYesterday,0)> isnull(mountYesterday,0) then 1 when isnull(mountBeforeYesterday,0)< isnull(mountYesterday,0) then -1 else 0 end Comparison
,isnull(mountYesterday,0) mountYesterday,isnull(mountBeforeYesterday,0) mountBeforeYesterday
,total
from
(select goodsname,sum(saleamount) total from goods group by goodsname) t
left join (
select sum(a.saleamount) mountYesterday,a.goodsname gnYesterday
from goods a
where convert(varchar(10),a.goodstime,23) =convert(char(10), dateadd(day,-1,getdate()),23)
group by a.goodsname
) t1 on t1.gnYesterday=t.goodsname
left join (
select sum(b.saleamount) mountBeforeYesterday,b.goodsname gnBeforeYesterday
from goods b
where convert(varchar(10),b.goodstime,23) =convert(char(10), dateadd(day,-2,getdate()),23)
group by b.goodsname
) t2 on t2.gnBeforeYesterday=t.goodsname
order by t.total desc
/* 统计结果
b09918
c-110818
a-19817
d-1909
e1088
*/
[解决办法]
模拟数据
select * from goods;
1 a 20121201 9
2 a 20121202 11
3 a 20121203 8
4 a 20121204 7
5 a 20121205 7
6 a 20121206 9
7 a 20121207 17
8 a 20121208 9
9 a 20121209 21
为了看得清楚一些，我查询了几个冗余的字段
select t1.goodname,
t1.goodtime,
t1.saleamount today,
t2.saleamount yesterday,
t1.saleamount - t2.saleamount "t1-t2",
sign(t1.saleamount - t2.saleamount) flag
from goods t1, goods t2
where t1.goodtime = t2.goodtime(+) + 1
结果如下所示：
GOODNAME GOODTIMETODAYYESTERDAYt1-t2FLAG
a 201212019
a 20121202119 21
a 20121203811 -3-1
a 2012120478 -1-1
a 2012120577 00
a 2012120697 21
a 20121207179 81
a 20121208917 -8-1
a 20121209219 121
[解决办法]
with a1 as (select trunc(goodstime,'DD') as date_id, -- 前天的销量
goodsname,
sum(salemount) as salemount
from goods
where goodstime >= trunc(sysdate-2,'DD')
and goodstime < trunc(sysdate-1,'DD')
group by goodsname),
a2 as (select trunc(goodstime,'DD') as date_id, -- 昨天的销量
goodsname,
sum(salemount) as salemount
from goods
where goodstime >= trunc(sysdate-1,'DD')
and goodstime < trunc(sysdate,'DD')
group by goodsname),
a3 as (select nvl(a1.date_id,a2.date_id-1) as qt_date_id, -- 前天日期
nvl(a1.goodsname,a2.goodsname) as goodsname,-- 商品名
nvl(a1.salemount,0) as qt_salemount, -- 前天销量
nvl(a2.date_id,a1.date_id+1) as zt_date_id, -- 昨天日期
nvl(a2.salemount,0) as zt_salemount -- 昨天销量
from a1 full join a2 on a1.goodsname=a2.goodsname)
select a3.goodsname,
(case when a3.qt_salemount>a3.zt_salemount then 1 -- 如果前天比昨天高，则标记为1
when a3.qt_salemount<a3.zt_salemount then -1 -- 如果前天比昨天低，则标记为-1
else 0 -- 否则(销量一样)则标记为0
end) as sale_sign, -- 销售增幅
a3.qt_salemount+a3.zt_salemount as two_salemount -- 两天销量总和
from a3
order by a3.qt_salemount+a3.zt_salemount desc;
-- 按 近两天各商品销量和 降序排序