select substr(t2.create_time,1,10) as registtime,t6.province_id as registprovince ,NVL(count(t2.id),0) as registnum
from t_cas_userst t2 , t_city t6 where t2.local_id=t6.id
group by t6.province_id, substr(t2.create_time,1,10)
这个 是查询出一段数据，有 时间，省份ID， 数量1.
select substr(t3.trade_time,1,10) as trade_time,t6.province_id as orderProvince,count(t3.id) as ordernum from t_cas_trade t3
, t_city t6 where t3.local_id=t6.id
group by t6.province_id , substr(t3.trade_time,1,10)这个 是又查询出一段时间， 也有 时间，省份ID，数量2.从这两段数据 查询出一个整合数据，但是没有绝对的关联关系，要么是 省份ID， 要么是时间
应该查询出的字段为： 时间，省份ID，数量1，数量2 （数量1 数量2 空 补0）
我用的 是 full join 查询到一定量的时候，就死掉了。卡死在哪里， 然后我尝试建立一张临时表，先查一段数据进临时表，再查询所有的数据，这个时候显示正常，我靠。急死了，求指点
select * from (
select substr(t3.trade_time,1,10) as trade_time,t6.province_id as orderProvince,count(t3.id) as ordernum from t_cas_trade t3
, t_city t6 where t3.local_id=t6.id
group by t6.province_id , substr(t3.trade_time,1,10)) orderinfo
full join
(select substr(t2.create_time,1,10) as registtime,t6.province_id as registprovince ,NVL(count(t2.id),0) as registnum
from t_cas_userst t2 , t_city t6 where t2.local_id=t6.id
group by t6.province_id, substr(t2.create_time,1,10)) registinfo
on orderinfo.orderProvince= registinfo.registprovince and orderinfo.trade_time=registinfo.registtime这样就死掉了。。查询到一半。
求指点 超急的。
2
3 ------------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
5 ------------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 2140K| 155M| | 13M (5)| 46:04:35 |
7 | 1 | VIEW | | 2140K| 155M| | 13M (5)| 46:04:35 |
8 | 2 | UNION-ALL | | | | | | |
9 |* 3 | HASH JOIN RIGHT OUTER| | 2085K| 151M| 2696K| 2318 (4)| 00:00:28 |
10 | 4 | VIEW | | 55114 | 2045K| | 788 (3)| 00:00:10 |
11 | 5 | HASH GROUP BY | | 55114 | 1776K| 4776K| 788 (3)| 00:00:10 |
12 |* 6 | HASH JOIN | | 55114 | 1776K| | 286 (3)| 00:00:04 |
13 | 7 | TABLE ACCESS FULL| T_CITY | 374 | 2992 | | 3 (0)| 00:00:01 |
14 | 8 | TABLE ACCESS FULL| T_CAS_USERST | 55114 | 1345K| | 282 (3)| 00:00:04 |
15 | 9 | VIEW | | 95970 | 3561K| | 1131 (3)| 00:00:14 |
16 | 10 | HASH GROUP BY | | 95970 | 3092K| 8312K| 1131 (3)| 00:00:14 |
17 |* 11 | HASH JOIN | | 95970 | 3092K| | 260 (4)| 00:00:04 |
18 | 12 | TABLE ACCESS FULL| T_CITY | 374 | 2992 | | 3 (0)| 00:00:01 |
19 | 13 | TABLE ACCESS FULL| T_CAS_TRADE | 95970 | 2343K| | 255 (3)| 00:00:04 |
20 | 14 | HASH GROUP BY | | 55114 | 1776K| 4776K| 13M (5)| 46:04:07 |
21 |* 15 | FILTER | | | | | | |
22 |* 16 | HASH JOIN | | 55114 | 1776K| | 286 (3)| 00:00:04 |
23 | 17 | TABLE ACCESS FULL | T_CITY | 374 | 2992 | | 3 (0)| 00:00:01 |
24 | 18 | TABLE ACCESS FULL | T_CAS_USERST | 55114 | 1345K| | 282 (3)| 00:00:04 |
25 | 19 | HASH GROUP BY | | 38 | 1254 | | 264 (5)| 00:00:04 |
26 |* 20 | HASH JOIN | | 38 | 1254 | | 263 (5)| 00:00:04 |
27 |* 21 | TABLE ACCESS FULL| T_CITY | 12 | 96 | | 3 (0)| 00:00:01 |
28 |* 22 | TABLE ACCESS FULL| T_CAS_TRADE | 960 | 24000 | | 259 (5)| 00:00:04 |
29 ------------------------------------------------------------------------------------------------
30
31 Predicate Information (identified by operation id):
32 ---------------------------------------------------
33
34 3 - access("ORDERINFO"."TRADE_TIME"="REGISTINFO"."REGISTTIME"(+) AND
35 "ORDERINFO"."ORDERPROVINCE"="REGISTINFO"."REGISTPROVINCE"(+))
36 6 - access("T2"."LOCAL_ID"="T6"."ID")
37 11 - access("T3"."LOCAL_ID"="T6"."ID")
38 15 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "T_CITY" "T6","T_CAS_TRADE" "T3"
39 WHERE "T3"."LOCAL_ID"="T6"."ID" AND SUBSTR("T3"."TRADE_TIME",1,10)=SUBSTR(:B1,1,10) AND
40 "T6"."PROVINCE_ID"=:B2 GROUP BY "T6"."PROVINCE_ID",SUBSTR("T3"."TRADE_TIME",1,10)))
41 16 - access("T2"."LOCAL_ID"="T6"."ID")
42 20 - access("T3"."LOCAL_ID"="T6"."ID")
43 21 - filter("T6"."PROVINCE_ID"=:B1)
44 22 - filter(SUBSTR("T3"."TRADE_TIME",1,10)=SUBSTR(:B1,1,10))我表示看不懂的呀。。
这三个字段加上索引