今天为止 人生最长sql
select n.neco_id,
n.neco_name,
n.neco_userid_fk,
n.neco_regional,
n.neco_balancetype,
newcoop_data.nqcp,
newcoop_data.boss
from (select n.neco_id newcoopId,
sum(cc.ccbd_newuserchecknum) boss,
sum(cd.cdse_newuserchecknum) nqcp
from userinfov2.tbl_stat_coopcheckbyruledaily cc,
boss_nqcp.tbl_nqcp_coopdailysettlement_r cd,
userinfov2.tbl_cooperator c,
userinfov2.tbl_newcooperator_i n
where cc.ccbd_coopid_fk = cd.cdse_coopid_fk(+)
and cc.ccbd_date = cd.cdse_date(+)
and c.coop_id = cc.ccbd_coopid_fk
and c.coop_necoid_fk = n.neco_id
and c.coop_onlinestatus = 1
and c.coop_paytype = 2
and c.coop_balance_state = 1
and n.neco_onlinestatus = 1
and n.neco_userid_fk = 'sunchengqi'
and n.neco_regional = 0
and cc.ccbd_date between
to_date('2008-08-01 00:00:00', 'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59', 'yyyy/MM/DD hh24:mi:ss')
group by n.neco_id
union
select n.neco_id newcoopId,
sum(sm.smco_newuserchecknum) boss,
sum(ic.icds_newuserchecknum) nqcp
from USERINFOV2.TBL_STAT_SETTLEMENTBYCOUN_H sm,
boss_nqcp.TBL_NQCP_INTERCOOPDAILYSETT_R ic,
userinfov2.tbl_cooperator c,
userinfov2.tbl_newcooperator_i n
where sm.smco_coopid_fk = ic.icds_coopid_fk(+)
and sm.smco_counid_fk = ic.icds_counid_fk(+)
and sm.smco_date = ic.icds_date(+)
and c.coop_id = sm.smco_coopid_fk
and c.coop_necoid_fk = n.neco_id
and c.coop_onlinestatus = 1
and c.coop_paytype = 2
and c.coop_balance_state = 1
and n.neco_onlinestatus = 1
and n.neco_userid_fk = 'sunchengqi'
and n.neco_regional = 1
and sm.smco_date between
to_date('2008-08-01 00:00:00', 'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59', 'yyyy/MM/DD hh24:mi:ss')
and exists
(select chco.chco_counid_fk
from boss_nqcp.tbl_nqcp_channelcountry_c chco
where chco.chco_coopid_fk = c.coop_id
and chco.chco_counid_fk = sm.smco_counid_fk)
group by n.neco_id
union
select yzboss.newcoopId, yzboss.num boss, yznqcp.num nqcp
from (select yztmp.newcoopId, sum(yztmp.num) num
from (select n.neco_id newcoopId, count(ccbi.ccbi_imei) num
from userinfov2.TBL_STAT_COOPCHECKBYIMEI ccbi,
userinfov2.tbl_cooperator c,
userinfov2.tbl_newcooperator_i n
where ccbi.ccbi_coopid_fk = c.coop_id
and c.coop_necoid_fk = n.neco_id
and c.coop_onlinestatus = 1
and c.coop_paytype = 2
and c.coop_balance_state = 1
and n.neco_onlinestatus = 1
and n.neco_userid_fk = 'sunchengqi'
and n.neco_regional = 2
and ccbi.ccbi_date between
to_date('2008-08-01 00:00:00',
'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59',
'yyyy/MM/DD hh24:mi:ss')
group by n.neco_id
union
select n.neco_id newcoopId, count(idin.idin_imei) num
from CARDV2.TBL_COOP_IMEIDECRYPTINFO idin,
userinfov2.tbl_cooperator c,
userinfov2.tbl_newcooperator_i n
where idin.idin_coopid_fk = c.coop_id
and c.coop_necoid_fk = n.neco_id
and c.coop_onlinestatus = 1
and c.coop_paytype = 2
and c.coop_balance_state = 1
and n.neco_onlinestatus = 1
and n.neco_userid_fk = 'sunchengqi'
and n.neco_regional = 2
and idin.idin_date between
to_date('2008-08-01 00:00:00',
'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59',
'yyyy/MM/DD hh24:mi:ss')
group by n.neco_id
union
select n.neco_id newcoopId,
sum(ccbd.ccbd_newuserchecknum) num
from userinfov2.tbl_stat_coopcheckbyruledaily ccbd,
userinfov2.tbl_cooperator c,
userinfov2.tbl_newcooperator_i n
where ccbd.ccbd_coopid_fk = c.coop_id
and c.coop_necoid_fk = n.neco_id
and c.coop_onlinestatus = 1
and c.coop_paytype = 2
and c.coop_balance_state = 1
and n.neco_onlinestatus = 1
and n.neco_userid_fk = 'sunchengqi'
and n.neco_regional = 2
and ccbd.ccbd_date between
to_date('2008-08-01 00:00:00',
'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59',
'yyyy/MM/DD hh24:mi:ss')
group by n.neco_id) yztmp
group by yztmp.newcoopId) yzboss,
(select n2.neco_id newcoopId, sum(pids.pisa_sumnumber) num
from boss_nqcp.tbl_nqcp_preinstalldailysett_r pids,
userinfov2.tbl_cooperator c2,
userinfov2.tbl_newcooperator_i n2
where pids.pids_coopid_fk = c2.coop_id
and c2.coop_necoid_fk = n2.neco_id
and c2.coop_onlinestatus = 1
and c2.coop_paytype = 2
and c2.coop_balance_state = 1
and n2.neco_onlinestatus = 1
and n2.neco_userid_fk = 'sunchengqi'
and n2.neco_regional = 2
and pids.pisa_date between
to_date('2008-08-01 00:00:00', 'yyyy/MM/DD hh24:mi:ss') and
to_date('2008-08-31 23:59:59', 'yyyy/MM/DD hh24:mi:ss')
group by n2.neco_id) yznqcp
where yzboss.newcoopId = yznqcp.newcoopId) newcoop_data,
userinfov2.tbl_newcooperator_i n
where newcoop_data.newcoopId = n.neco_id
[解决办法]
只是长而已，很简单的sql
[解决办法]
这都算是长的,哥那不得哭啊............................
[解决办法]
正好也写了个长sql,对比下：
SELECT sum(money) totalMoney,count(rxID) recipeCount,sum(drugNum)*1.0/count(rxID) avgDrugNum, sum (case when injectionNum>0 then 1 else 0 end ) injectionRxNum,sum (case when antinum>0 then 1 else 0 end ) antiRxNum , sum (case when orallyAntiNum>0 then 1 else 0 end ) orallyAnitRxNum,sum (case when injectionantinum>0 then 1 else 0 end ) injectionAntiRxNum , sum(antiMoney) antiMoney,sum(orallyAntiMoney) orallyAntiMoney,sum(injectionAntiMoney) injectionAntiMoney,sum(antiMoney) antiMoney , sum (case when drugNum>5 then 1 else 0 end ) moreThen5,count(distinct case when antiNum>0 then serialNo else null end)*1.0/count(distinct serialNo) antiRatio, count(distinct serialNo) patientNum, count(rxID) rxCount, count(case when sameAntiNum>1 then rxID else null end) sameRxCount, sum(case when sameAntiNum>1 then money else 0 end) sameMoney, count(distinct case when sameAntiNum>1 then serialNo else null end) samePatientNum, count(case when sameOrallyAntiNum>1 then rxID else null end) sameOrallyRxCount, sum(case when sameOrallyAntiNum>1 then money else 0 end) sameOrallyMoney, count(distinct case when sameOrallyAntiNum>1 then serialNo else null end) sameOrallyPatientNum, count(case when sameInjectAntiNum>1 then rxID else null end) sameInjectRxCount, sum(case when sameInjectAntiNum>1 then money else 0 end) sameInjectMoney, count(distinct case when sameInjectAntiNum>1 then serialNo else null end) sameInjectPatientNum, count(case when orallyAntiNum>1 then rxID else null end) orallyRxCount, sum(case when orallyAntiNum>1 then money else 0 end) orallyMoney, count(distinct case when orallyAntiNum>1 then serialNo else null end) orallyPatientNum, count(case when injectionAntiNum>1 then rxID else null end) injectRxCount, sum(case when injectionAntiNum>1 then money else 0 end) injectMoney, count(distinct case when injectionAntiNum>1 then serialNo else null end) injectPatientNum, count(case when antiNum>1 then rxID else null end) rxCount1, sum(case when antiNum>1 then money else 0 end) money1, count(distinct case when antiNum>1 then serialNo else null end) patientNum1, count(case when antiNum>2 then rxID else null end) rxCount2, sum(case when antiNum>2 then money else 0 end) money2, count(distinct case when antiNum>2 then serialNo else null end) patientNum2, count(case when antiNum>3 then rxID else null end) rxCount3, sum(case when antiNum>3 then money else 0 end) money3, count(distinct case when antiNum>3 then serialNo else null end) patientNum3, count(case when antiNum>4 then rxID else null end) rxCount4, sum(case when antiNum>4 then money else 0 end) money4, count(distinct case when antiNum>4 then serialNo else null end) patientNum4, sum( antiNum)*1.0/sum( case when antiNum>0 then 1 else 0 end) avgAntiNum,sum(antiMoney)*1.0/count(distinct serialNo) avgAntiMoney, count(distinct case when antiNum>0 then serialNo else null end)*1.0/count(distinct serialNo) useAntiRation FROM Rx WHERE prescribeDate >= ? AND prescribeDate < ? AND isWestern=1
[解决办法]
不算长，以后你会遇到更长的
[解决办法]
拼拼更健康
------解决方案--------------------
还好啊，不是很长
[解决办法]
union all 下去没有最长只有更长
[解决办法]
受不了，怎么比这个东西？有些sql不长，但是看一晌都看不懂
[解决办法]
淡定淡定
[解决办法]
...
[解决办法]
楼主刚入职吧
[解决办法]
经理问你有神马特长 答曰：我SQL写的特长
