select t1.地区编码 ,t1.地区名称,t2.天气,t2.日期,count(distinct(t2.天气) )count1, count(t3.事故编码) count2 from table1 t1,table2 t2 ,table3 t3 where t1.地区编码 =t2.地区编码(+) and t1.地区编码 =t3.地区编码 (+) group by t1.地区编码 ,t1.地区名称,t2.天气,t2.日期 类似的吧 !!
如果天气表每天都有记录的话可以用下面的sql create table t_area ( area_name varchar2(20), area_code varchar2(6) );create table t_weather ( area_code varchar2(6), vdate date, weather varchar2(10) );create table t_accident ( acc_code varchar2(8), area_code varchar2(6), acc_date date ); SELECT area_name, weather, year_time, weather_number, acc_number, acc_number / weather_number FROM (SELECT ta.area_name, tw.weather, TO_CHAR (tw.vdate, 'YYYY') AS year_time, COUNT (tw.vdate) AS weather_number, COUNT (tac.acc_code) AS acc_number FROM t_area ta, t_weather tw, t_accident tac WHERE ta.area_code = tw.area_code AND tac.area_code(+) = tw.area_code AND TO_CHAR (tw.vdate, 'YYYY') = TO_CHAR (tac.acc_date(+), 'YYYY') GROUP BY ta.area_name, tw.weather, TO_CHAR (tw.vdate, 'YYYY'))
http://topic.csdn.net/u/20100119/12/87653ccc-6c4a-49b6-a0f8-e74e63822fd5.html?81317你这个稍微复杂一下,不过连接方式类似。
from table1 t1,table2 t2 ,table3 t3
where t1.地区编码 =t2.地区编码(+)
and t1.地区编码 =t3.地区编码 (+)
group by t1.地区编码 ,t1.地区名称,t2.天气,t2.日期 类似的吧 !!
create table t_area
(
area_name varchar2(20),
area_code varchar2(6)
);create table t_weather
(
area_code varchar2(6),
vdate date,
weather varchar2(10)
);create table t_accident
(
acc_code varchar2(8),
area_code varchar2(6),
acc_date date
);
SELECT area_name, weather, year_time, weather_number, acc_number,
acc_number / weather_number
FROM (SELECT ta.area_name, tw.weather,
TO_CHAR (tw.vdate, 'YYYY') AS year_time,
COUNT (tw.vdate) AS weather_number,
COUNT (tac.acc_code) AS acc_number
FROM t_area ta, t_weather tw, t_accident tac
WHERE ta.area_code = tw.area_code
AND tac.area_code(+) = tw.area_code
AND TO_CHAR (tw.vdate, 'YYYY') = TO_CHAR (tac.acc_date(+),
'YYYY')
GROUP BY ta.area_name, tw.weather, TO_CHAR (tw.vdate, 'YYYY'))
在sql的查询分析器里能运行不?弱弱的问一下,那个(+)是什么意思呀?