好久没更新blog了。赶紧更新下。

换了家新公司，折腾了2天报表，熟悉下业务。把SQL和Oracle的 嵌套语句又复习了下。

SQL:

1.这个里面的语句技术点是在case when 和 hash表两列相乘.

SELECT TICKET_USER.USERNAME as USERNAME, 
count(case C_PNR_STATUS when 'EE' then C_PNR_STATUS end) as STATUS_EE, 
 count(case C_PNR_STATUS when 'CL'then C_PNR_STATUS when 'GZ' then C_PNR_STATUS when 'TZ' then C_PNR_STATUS end) as STATUS_CL, 
 count(case C_PNR_STATUS when 'CC' then C_PNR_STATUS end) as STATUS_CC,  
 count(case C_PNR_STATUS when 'ET' then C_PNR_STATUS end) as STATUS_ET, 
 a.PNRPASSENGER_COUNT TICKET_COUNT, b.PNRSEGMENTCOUNT as PNRSEGMENTCOUNT 
 FROM TICKET_PNR_INFO, TICKET_USER ,
(select TICKET_PNR_INFO.C_USER_ID,count(1) as PNRPASSENGER_COUNT
from TICKET_PNRPASSENGER_INFO,TICKET_PNR_INFO
where TICKET_PNRPASSENGER_INFO.C_ORDERID=TICKET_PNR_INFO.C_ORDERID
and TICKET_PNRPASSENGER_INFO.C_TKT_STATUS='RR'
group by TICKET_PNR_INFO.C_USER_ID) a,
(select d.USERNAME,sum(a.PNRPASSENGER_COUNT*b.PNRSEGMENT_COUNT) PNRSEGMENTCOUNT from
(select C_ORDERID,count(1) as PNRPASSENGER_COUNT from TICKET_PNRPASSENGER_INFO
where c_tkt_status='RR'group by C_ORDERID) a,
(select C_ORDERID,count(1) as PNRSEGMENT_COUNT from TICKET_PNRSEGMENT_INFO
group by C_ORDERID) b,TICKET_PNR_INFO c,TICKET_USER d
where a.C_ORDERID=b.C_ORDERID and a.c_orderid=c.c_orderid
and c.C_USER_ID=d.USERID group by d.USERNAME) b  
 WHERE TICKET_USER.USERID=TICKET_PNR_INFO.C_USER_ID and a.C_USER_ID=TICKET_PNR_INFO.C_USER_ID
 and b.USERNAME=TICKET_USER.USERNAME 
--and C_CREATE_DATE >=  '2008-08-01'   and C_CREATE_DATE <= '2008-10-1'    
 GROUP BY TICKET_USER.USERNAME,a.PNRPASSENGER_COUNT,b.PNRSEGMENTCOUNT

2.这里面有个取日期判断单日还是双日的。

select distinct
case b.datetype when '0' then '单日' when '1' then '双日' end as datetype,
a.count_sum,a.STATUS_EE,a.STATUS_CL,a.STATUS_CC,a.STATUS_ET,c.PNRPASSENGER_COUNT 
from (select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype,
count(case C_PNR_STATUS when 'EE' then C_PNR_STATUS end) as STATUS_EE,
count(case C_PNR_STATUS when 'CL'then C_PNR_STATUS when 'GZ' then C_PNR_STATUS when 'TZ' then C_PNR_STATUS end) as STATUS_CL,
count(case C_PNR_STATUS when 'CC' then C_PNR_STATUS end) as STATUS_CC,
count(case C_PNR_STATUS when 'ET' then C_PNR_STATUS end) as STATUS_ET,
count(1) AS count_sum 
from TICKET_PNR_INFO
--where TICKET_PNR_INFO.C_CREATE_DATE >='2008-07-01' and TICKET_PNR_INFO.C_CREATE_DATE <='2008-09-01'
group by CONVERT(varchar,(day(C_CREATE_DATE) % 2),10)) a,
(select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype from TICKET_PNR_INFO ) b,
(select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype,count(1) as PNRPASSENGER_COUNT
from TICKET_PNRPASSENGER_INFO,TICKET_PNR_INFO
where TICKET_PNRPASSENGER_INFO.C_ORDERID=TICKET_PNR_INFO.C_ORDERID
and TICKET_PNRPASSENGER_INFO.C_TKT_STATUS='RR'
group by CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) ) c
where a.datetype=b.datetype and a.datetype=c.datetype

oracle：

这里面有个取日期判断单日还是双日的。

select distinct case b.datetype when '0' then '单日' when '1' then '双日' end as datetype,
a.count_sum,a.STATUS_EE,a.STATUS_CL,a.STATUS_CC,a.STATUS_ET,c.PNRPASSENGER_COUNT  from
(select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype,
count(case C_PNR_STATUS when 'EE' then C_PNR_STATUS end) as STATUS_EE,
count(case C_PNR_STATUS when 'CL'then C_PNR_STATUS when 'GZ' then C_PNR_STATUS when 'TZ' then C_PNR_STATUS end) as STATUS_CL,
count(case C_PNR_STATUS when 'CC' then C_PNR_STATUS end) as STATUS_CC,
count(case C_PNR_STATUS when 'ET' then C_PNR_STATUS end) as STATUS_ET,
count(1) AS count_sum from TICKET_PNR_INFO
--where TICKET_PNR_INFO.C_CREATE_DATE >='2008-05-01 00:00:00' and TICKET_PNR_INFO.C_CREATE_DATE <='2008-09-11 00:00:00' 
group by CONVERT(varchar,(day(C_CREATE_DATE) % 2),10)) a, 
(select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype from TICKET_PNR_INFO ) b,
(select CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) as datetype,count(1) as PNRPASSENGER_COUNT
from TICKET_PNRPASSENGER_INFO,TICKET_PNR_INFO  where TICKET_PNRPASSENGER_INFO.C_ORDERID=TICKET_PNR_INFO.C_ORDERID
and TICKET_PNRPASSENGER_INFO.C_TKT_STATUS='RR'  group by CONVERT(varchar,(day(C_CREATE_DATE) % 2),10) ) c
where a.datetype=b.datetype and a.datetype=c.datetype

转载请讲究点文明，请注明出处：http://www.cnblogs.com/xudongyh/