一些还算有点儿意思的sql(2010-10-19)

有时候还能写些有点儿意思的sql,还需要想上几分钟才能写出来,这样工作才有点儿意思,不然的话,真的要疯掉了.
今天写的两个还算有点儿意思的sql:

1.上周客户对某个商家留言超过10条,并且这个商家在上周对客户上周的留言都回复了的商家信息(这里只取出商家ID就可以了)
上周:上周一~~~刚刚过去的周日
表结构usermessage
inputdate:客户留言或者是商家回复留言的时间
type = 0:客户留言,companyid是针对哪个商家的留言,id:留言的ID
type = 2:商家针对客户留言的回复,companyid是这个商家的id,fid是针对哪个留言进行的回复

select distinct d1.companyid
  from usermessage d1
 where type = 0
   and d1.inputdate >= TRUNC(next_day(sysdate, 2)) - 14
   and d1.inputdate < TRUNC(next_day(sysdate, 1)) - 6
   and not exists
 (select 1
          from usermessage d3
         where d3.type = 0
           and d3.inputdate >= TRUNC(next_day(sysdate, 2)) - 14
           and d3.inputdate < TRUNC(next_day(sysdate, 1)) - 6
           and d3.companyid = d1.companyid
           and d3.id not in
         (select d4.fid
                  from usermessage d4
                 where d4.type = 2
                   and d4.inputdate >= TRUNC(next_day(sysdate, 2)) - 14
                   and d4.inputdate < TRUNC(next_day(sysdate, 1)) - 6
                   and d4.calstatus = 0
                   and d4.companyid = d3.companyid and d4.fid is not null))
   and d1.companyid in
       (select d5.companyid
          from usermessage d5
         where type = 0
           and d5.inputdate >= TRUNC(next_day(sysdate, 2)) - 14
           and d5.inputdate < TRUNC(next_day(sysdate, 1)) - 6
         group by d5.companyid
        having count(1) >= 10);

2.按小类(比如说手机)pv降序排序,小类内部按品牌(比如说诺基亚)pv降序排序:

select c.id,max(c.name) name,sum(nvl(p.pageview,0)) pv,max(c.type) type,max(c.fatherid) fatherid
from catalog c,catalogrelateproduct cp,product p
where c.id=cp.catalogid and c.type in(2,3) and c.status=1 and cp.productid=p.id and c.siteinfoid=1 
group by c.id

type=2  表示是小类
type=3  表示是品牌,它的fatherid就是小类的ID
比如说 诺基亚N8 这款产品,在catalogrelateproduct表中对应诺基亚这个品牌会有一条记录,对应手机这个小类也会有一条记录.
这里的pv对小类来说就是小类的pv,对品牌来说就是品牌的pv

这样上面的语句可以得到小类和品牌的pv.
为了实现上面的要求,实际上就是要得到品牌行对应的小类的PV,
然后order by catalog_pv desc,pv desc
但为了避免某两个小类的pv值相同时导致的这两个小类,品牌没有区分开来的问题,就要求
order by catalog_pv,catalog_id,pv desc
因为只有两级目录,如果从小类开始遍历的话,对于品牌行来说,catalog_pv实际上就是根行的pv,也就是connect_by_root pv
catalog_id实际上就是connect_by_root id
这样就可以实现为:

with tmp as
(
   select name,level lev,pv,connect_by_root pv catalog_pv,connect_by_root id catalog_id
   from
   (
       select c.id,max(c.name) name,sum(nvl(p.pageview,0)) pv,max(c.type) type,max(c.fatherid) fatherid
         from catalog c,catalogrelateproduct cp,product p
        where c.id=cp.catalogid and c.type in(2,3) and c.status=1 and cp.productid=p.id and c.siteinfoid=1 
      group by c.id
   )
  start with type=2
  connect by fatherid=prior id
)
select case lev when 1 then name when 2 then '---------'||name end name,pv
from tmp
order by catalog_pv desc,catalog_id,pv desc;

显示出来大致是这样的结果:

手机                                        1168875
---------诺基亚                              432553
---------三星                                108573
---------HTC                                  76253
---------索尼爱立信                           61675
---------多普达                               47955
---------摩托罗拉                             43180
---------OPPO                                 37944
---------LG                                   36475
.................
笔记本                                       333131
---------戴尔                                 65433
---------华硕                                 53025
---------联想                                 46538
---------宏碁                                 35495
---------ThinkPad                             28810
---------索尼                                 23014
---------HP                                   21078
.................
数码相机                                     262898
---------三星                                107882
---------佳能                                 46862
---------索尼                                 34058
---------尼康                                 25403
---------富士                                 10251
---------松下                                  9746
---------卡西欧                                5783
---------奥林巴斯                              5535
..................
MP4/MP5                                       77366
---------爱国者                               10820
---------纽曼                                  9743
---------昂达                                  7565
.................
台式机                                        59567
---------联想                                 26655
---------戴尔                                  7851
---------惠普(HP)                              6724
---------清华同方                              2562
---------方正                                  2319
---------海尔                                  2317
---------宏碁                                  1907
---------ThinkCentre                           1636
---------神舟                                  1549
---------苹果                                  1545
---------华硕                                  1111
---------索尼                                   633
---------长城                                   391
---------明基                                   279
.................
上网本                                        57892
.................