sql难题：排序，比较统计

Question

本人想要实现某产品的销量统计，算法如下： 前两天的销量比较：如果前天比昨天高，则标记为正1；低，则标记为负1； 若持平，则标记为0. 并且按销量由高到低排序。 求高手一条sql语句解决（多条也行）。 已知条件： 表名：goods, 商品名：goodsname,交易时间：goodstime,销量：saleamount. 其中，商品有20种，记录估计1000条。 说明：只求sql,不用考虑其他sql语句以外的问题。

Answer 1

来点数据，给个预想结果既然是SQL难题，分还这么少。兴致不是很高啊。

Answer 2

--查看模拟数据
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 前天销售量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 -1 9 8
b 0 9 9
c -1 10 8
d -1 9 0
e 1 0 8*/

Answer 3

--上个忘排序了，呵呵
--注意，测试数据 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
/* 统计结果
 
b 0 9 9 18
c -1 10 8 18
a -1 9 8 17
d -1 9 0 9
e 1 0 8 8
 
*/ 

Answer 4

模拟数据
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至于按降序排列，就不说了

Answer 5

模拟数据
 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 GOODTIME TODAY YESTERDAY t1-t2 FLAG
a  20121201 9
a  20121202 11 9          2 1
a  20121203 8 11          -3 -1
a  20121204 7 8          -1 -1
a  20121205 7 7          0 0
a  20121206 9 7          2 1
a  20121207 17 9          8 1
a  20121208 9 17          -8 -1
a  20121209 21 9          12 1

Answer 6

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;-- 按 近两天各商品销量和 降序排序