select s.name provinceName,
s.id id,
(select max(zcpc) zcpc
from xjstat.tm_tdyy_hz_YEAR
where oper_time between 2009 AND 2009
and salon_id = a.salon_id) zcpc,
(select count(1)
from (select count(1) num, salon_id
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
group by hosid, salon_id) t
where t.num >= 1
and t.salon_id = a.salon_id) PC_TIMES,
sum(DLCS1) DLCS1,
(select count(1)
from (select count(1) num, salon_id
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
group by hosid, salon_id) t
where t.num >= 365
and t.salon_id = a.salon_id) EVERY_TIMES,
pchl
from xjuser.ts_organizations s
left join xjstat.tm_tdyy_hz_YEAR a on a.province_id = s.id
and (a.oper_time between 2009 AND 2009 )
where s.orgclass = 2
group by s.name, a.salon_id, pchl, s.id
s.id id,
(select max(zcpc) zcpc
from xjstat.tm_tdyy_hz_YEAR
where oper_time between 2009 AND 2009
and salon_id = a.salon_id) zcpc,
(select count(1)
from (select count(1) num, salon_id
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
group by hosid, salon_id) t
where t.num >= 1
and t.salon_id = a.salon_id) PC_TIMES,
sum(DLCS1) DLCS1,
(select count(1)
from (select count(1) num, salon_id
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
group by hosid, salon_id) t
where t.num >= 365
and t.salon_id = a.salon_id) EVERY_TIMES,
pchl
from xjuser.ts_organizations s
left join xjstat.tm_tdyy_hz_YEAR a on a.province_id = s.id
and (a.oper_time between 2009 AND 2009 )
where s.orgclass = 2
group by s.name, a.salon_id, pchl, s.id
解决方案 »
- Tuxedo 10gR3 for MS Windows Server 2008 w/MS VS 2008 x86-64 64-bit在哪里可以下载
- oracle CDC中dbms_cdc_subscribe.extend_window引发的问题
- ora-06512,ora-04088错误
- 一个统计的SQl语句
- xml在oracle上解析请求解答! 100分
- 这样的SQL语句能写出来吗
- 还是跟踪SQL语句的问题,跟踪时语句怎么没有全部查出来呢?
- oracle8.1.7起动时出错(在线等等)
- 求购ERWIN的操作指南(最好是中文版的)__在线等待
- Oracle 存储过程中的临时表数据自动清空
- 存储过程问题!
- 帮忙看看这是怎么加密的 --在线等
from (select count(1) num, salon_id
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
group by hosid, salon_id) t
where t.num >= 1
这里的t.num>=1没有意义,这个num至少为1..
s.id id,
(select max(zcpc) zcpc
from xjstat.tm_tdyy_hz_YEAR
where oper_time between 2009 AND 2009
and salon_id = a.salon_id) zcpc,
(select count(count(1))
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
and salon_id = a.salon_id
group by hosid, salon_id
) PC_TIMES,
sum(DLCS1) DLCS1,
(select count(case when count(1)>=365 then 1 end)
from xjstat.tm_tdyy_hz_hostid_daily
where oper_time between 20090101 AND 20091231
and salon_id = a.salon_id
group by hosid, salon_id ) EVERY_TIMES,
pchl
from xjuser.ts_organizations s
left join xjstat.tm_tdyy_hz_YEAR a on a.province_id = s.id
and (a.oper_time between 2009 AND 2009 )
where s.orgclass = 2
group by s.name, a.salon_id, pchl, s.id
放在where条件的最后一个。
吧oper_time between 2009 AND 2009 想办法用=号代替。参考下。
S.NAME AS PROVINCENAME,
S.ID AS ID,
MAX(ZCPC) AS ZCPC,
MAX(PC_TIMES) AS PC_TIMES,
SUM(DLCS1) AS DLCS1,
MAX(EVERY_TIMES) AS EVERY_TIMES,
PCHL
FROM XJUSER.TS_ORGANIZATIONS S
LEFT JOIN XJSTAT.TM_TDYY_HZ_YEAR A ON A.PROVINCE_ID=S.ID AND A.OPER_TIME BETWEEN 2009 AND 2009
LEFT JOIN
(
SELECT SALON_ID,
MAX(ZCPC) AS ZCPC,
COUNT(PC_TIMES_SALON_ID) AS PC_TIMES,
COUNT(EVERY_TIMES) AS EVERY_TIMES
FROM
(
SELECT SALON_ID,
(CASE WHEN COUNT(1)>=1 THEN SALON_ID END) AS PC_TIMES_SALON_ID,
(CASE WHEN COUNT(1)>=1 THEN SALON_ID END) AS EVERY_TIMES_SALON_ID,
NULL ZCPC
FROM XJSTAT.TM_TDYY_HZ_HOSTID_DAILY
WHERE OPER_TIME BETWEEN 20090101 AND 20091231
GROUP BY SALON_ID,HOSID
HAVING COUNT(1)>=1
UNION ALL
SELECT SALON_ID,
NULL,
NULL,
MAX(ZCPC) ZCPC
FROM XJSTAT.TM_TDYY_HZ_YEAR WHERE OPER_TIME BETWEEN 2009 AND 2009
)
GROUP BY SALON_ID
) D ON A.SALON_ID=D.SALON_ID
WHERE S.ORGCLASS = 2
GROUP BY S.NAME, A.SALON_ID,PCHL,S.ID方法2: 说不上比上面的方法好还是坏,实际运行情况和可用系统资源密切相关,主要的好处是执行计划的可控性强,在三个并集分别规模不大的情况下或者有足够的内存和CPU资源的情况下,会产生比较简单的执行计划,并充分利用这些资源.SELECT PROVINCENAME AS PROVINCENAME,
ID AS ID,
MAX(ZCPC) over(PARTITION BY SALON_ID) AS ZCPC,
COUNT(PC_TIMES_SALON_ID) over(PARTITION BY SALON_ID) AS PC_TIMES,
SUM(DLCS1) AS DLCS1
COUNT(EVERY_TIMES_SALON_ID) over(PARTITION BY SALON_ID) AS EVERY_TIMES,
PCHL AS PCHL
FROM
(
SELECT SALON_ID,
NULL AS PROVINCENAME,
NULL AS ID,
MAX(ZCPC) AS ZCPC,
(CASE WHEN COUNT(1)>=1 THEN SALON_ID END) AS PC_TIMES_SALON_ID,
NULL AS DLCS1,
(CASE WHEN COUNT(1)>=1 THEN SALON_ID END) AS EVERY_TIMES_SALON_ID,
NULL AS PCHL
FROM XJSTAT.TM_TDYY_HZ_HOSTID_DAILY
WHERE OPER_TIME BETWEEN 20090101 AND 20091231
GROUP BY SALON_ID,HOSID
HAVING COUNT(1)>=1
UNION ALL
SELECT SALON_ID,
NULL AS PROVINCENAME,
NULL AS ID,
MAX(ZCPC) AS ZCPC,
NULL AS PC_TIMES_SALON_ID,
NULL AS DLCS1,
NULL AS EVERY_TIMES_SALON_ID,
NULL AS PCHL
FROM XJSTAT.TM_TDYY_HZ_YEAR WHERE OPER_TIME BETWEEN 2009 AND 2009
GROUP BY SALON_ID
UNION ALL
SELECT
A.SALON_ID,
S.NAME AS PROVINCENAME,
S.ID AS ID,
NULL AS ZCPC,
NULL AS PC_TIMES_SALON_ID,
SUM(DLCS1) AS DLCS1,
NULL AS EVERY_TIMES_SALON_ID,
PCHL AS PCHL
FROM XJUSER.TS_ORGANIZATIONS S
LEFT JOIN XJSTAT.TM_TDYY_HZ_YEAR A ON A.PROVINCE_ID=S.ID AND A.OPER_TIME BETWEEN 2009 AND 2009
WHERE S.ORGCLASS = 2
GROUP BY A.SALON_ID,S.NAME,PCHL,S.ID
)
GROUP BY SALON_ID
having S.NAME is not null or PCHL is not null or S.ID is not null