问一下,我在oracle里面使用左联接有连接的时候,我设计到多张表,问一下我该怎么写sql语句啊
下面是我写的sql语句,好像不正确
select distinct tbl_report_setwater.f_index setwater_Index,to_char(jc.f_date,'yyyy-mm-dd') dtime,b.f_name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.dmxz_id)As DMXZ_Name, (SELECT F_Name FROM TBL_QX WHERE TBL_QX.F_ID = b.QX_ID)As QX_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.HX_ID)As HX_Name, (SELECT F_Name FROM TBL_RIVER WHERE TBL_RIVER.F_ID = b.RIVER_ID)As RIVER_Name,
(SELECT F_Name FROM TBL_LAKE WHERE TBL_LAKE.F_ID = b.LAKE_ID)As LAKE_Name,
(SELECT F_Name FROM TBL_RESERVOIR WHERE TBL_RESERVOIR.F_ID = b.RESERVOIR_ID)As RESERVOIR_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CQJ_ID)As CQJ_Name, (SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RQJ_ID)As RQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CSJ_ID)As CSJ_Name, (SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RSJ_ID)As RSJ_Name,
b.f_id b_id,jc.f_id c_id,b.F_STANDARD_DF ,round(da.f_data,var.f_decimal) f_data,da.variable_id from tbl_dbscd cd,tbl_brokenface b,tbl_dbssgjc jc,
tbl_dbssgjc_data da,tbl_variable_water var,tbl_report_setwaterdm dm,tbl_report_setwater where cd.brokenface_id=b.f_id and jc.cd_id(+)=cd.f_id
and da.bb_id=jc.f_id and var.f_code=da.variable_id and dm.dm_id=b.f_id and dm.setwater_id=tbl_report_setwater.f_id
and jc.f_date>=to_date('2008-01-01 ','yyyy-mm-dd') and jc.f_date<=to_date('2008-12-31' ,'yyyy-mm-dd') And dm.config_id=130 and var.f_code
in ( 'XFW','ZS','ZDSS','HZD','NO2N','ZDLS','LSY','CODCr','BOD5')
我想查询出来满足条件的tbl_dbscd表中数据。但是我上面的sql语句写的好像不正确,麻烦高手给指点一下。
下面是我写的sql语句,好像不正确
select distinct tbl_report_setwater.f_index setwater_Index,to_char(jc.f_date,'yyyy-mm-dd') dtime,b.f_name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.dmxz_id)As DMXZ_Name, (SELECT F_Name FROM TBL_QX WHERE TBL_QX.F_ID = b.QX_ID)As QX_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.HX_ID)As HX_Name, (SELECT F_Name FROM TBL_RIVER WHERE TBL_RIVER.F_ID = b.RIVER_ID)As RIVER_Name,
(SELECT F_Name FROM TBL_LAKE WHERE TBL_LAKE.F_ID = b.LAKE_ID)As LAKE_Name,
(SELECT F_Name FROM TBL_RESERVOIR WHERE TBL_RESERVOIR.F_ID = b.RESERVOIR_ID)As RESERVOIR_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CQJ_ID)As CQJ_Name, (SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RQJ_ID)As RQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CSJ_ID)As CSJ_Name, (SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RSJ_ID)As RSJ_Name,
b.f_id b_id,jc.f_id c_id,b.F_STANDARD_DF ,round(da.f_data,var.f_decimal) f_data,da.variable_id from tbl_dbscd cd,tbl_brokenface b,tbl_dbssgjc jc,
tbl_dbssgjc_data da,tbl_variable_water var,tbl_report_setwaterdm dm,tbl_report_setwater where cd.brokenface_id=b.f_id and jc.cd_id(+)=cd.f_id
and da.bb_id=jc.f_id and var.f_code=da.variable_id and dm.dm_id=b.f_id and dm.setwater_id=tbl_report_setwater.f_id
and jc.f_date>=to_date('2008-01-01 ','yyyy-mm-dd') and jc.f_date<=to_date('2008-12-31' ,'yyyy-mm-dd') And dm.config_id=130 and var.f_code
in ( 'XFW','ZS','ZDSS','HZD','NO2N','ZDLS','LSY','CODCr','BOD5')
我想查询出来满足条件的tbl_dbscd表中数据。但是我上面的sql语句写的好像不正确,麻烦高手给指点一下。
SELECT DISTINCT tbl_report_setwater.f_index setwater_index,
TO_CHAR (jc.f_date, 'yyyy-mm-dd') dtime, b.f_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.dmxz_id) AS dmxz_name,
(SELECT f_name
FROM tbl_qx
WHERE tbl_qx.f_id = b.qx_id) AS qx_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.hx_id) AS hx_name,
(SELECT f_name
FROM tbl_river
WHERE tbl_river.f_id = b.river_id) AS river_name,
(SELECT f_name
FROM tbl_lake
WHERE tbl_lake.f_id = b.lake_id) AS lake_name,
(SELECT f_name
FROM tbl_reservoir
WHERE tbl_reservoir.f_id = b.reservoir_id)
AS reservoir_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.cqj_id) AS cqj_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.rqj_id) AS rqj_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.csj_id) AS csj_name,
(SELECT f_name
FROM tbl_code
WHERE tbl_code.f_id = b.rsj_id) AS rsj_name, b.f_id b_id,
jc.f_id c_id, b.f_standard_df,
ROUND (da.f_data, var.f_decimal) f_data, da.variable_id
FROM tbl_dbscd cd,
tbl_brokenface b,
tbl_dbssgjc jc,
tbl_dbssgjc_data da,
tbl_variable_water var,
tbl_report_setwaterdm dm,
tbl_report_setwater
WHERE cd.brokenface_id = b.f_id
AND jc.cd_id(+) = cd.f_id
AND da.bb_id = jc.f_id
AND var.f_code = da.variable_id
AND dm.dm_id = b.f_id
AND dm.setwater_id = tbl_report_setwater.f_id
AND jc.f_date >= TO_DATE ('2008-01-01 ', 'yyyy-mm-dd')
AND jc.f_date <= TO_DATE ('2008-12-31', 'yyyy-mm-dd')
AND dm.config_id = 130
AND var.f_code IN
('XFW',
'ZS',
'ZDSS',
'HZD',
'NO2N',
'ZDLS',
'LSY',
'CODCr',
'BOD5'
)
我看你写的和我写的一样的啊,我现在就是想根据定制的tbl_dbscd中的f_Id,也就是dm.config_id=130这句话,根据这个config_id查出来的tbl_dbscd中的f_Id会比较多,但是我加了AND jc.f_date >= TO_DATE ('2008-01-01 ', 'yyyy-mm-dd') AND jc.f_date <= TO_DATE ('2008-12-31', 'yyyy-mm-dd')这个条件以后,我本来定制的一些点位现在都没有了。
最后的数据和不使用(+)得出来的数据是不一样的。
to_char(jc.f_date,'yyyy-mm-dd') dtime,
b.f_name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.dmxz_id) As DMXZ_Name,
(SELECT F_Name FROM TBL_QX WHERE TBL_QX.F_ID = b.QX_ID) As QX_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.HX_ID) As HX_Name,
(SELECT F_Name FROM TBL_RIVER WHERE TBL_RIVER.F_ID = b.RIVER_ID) As RIVER_Name,
(SELECT F_Name FROM TBL_LAKE WHERE TBL_LAKE.F_ID = b.LAKE_ID) As LAKE_Name,
(SELECT F_Name FROM TBL_RESERVOIR WHERE TBL_RESERVOIR.F_ID = b.RESERVOIR_ID) As RESERVOIR_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CQJ_ID) As CQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RQJ_ID) As RQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CSJ_ID) As CSJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RSJ_ID) As RSJ_Name,
b.f_id b_id,
jc.f_id c_id,
b.F_STANDARD_DF ,
round(da.f_data,var.f_decimal) f_data,
da.variable_id
from tbl_dbscd cd,
tbl_dbssgjc jc,
tbl_brokenface b,
tbl_dbssgjc_data da,
tbl_variable_water var,
tbl_report_setwaterdm dm,
tbl_report_setwater
where cd.f_id = jc.cd_id(+)
and jc.f_id = da.bb_id
and jc.f_date >= to_date('2008-01-01 ','yyyy-mm-dd')
and jc.f_date <= to_date('2008-12-31' ,'yyyy-mm-dd')
and cd.brokenface_id = b.f_id
and var.f_code = da.variable_id
and dm.dm_id = b.f_id
and dm.setwater_id = tbl_report_setwater.f_id
And dm.config_id = 130
and var.f_code in ( 'XFW','ZS','ZDSS','HZD','NO2N','ZDLS','LSY','CODCr','BOD5')
或:
select distinct tbl_report_setwater.f_index setwater_Index,
to_char(jc.f_date,'yyyy-mm-dd') dtime,
b.f_name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.dmxz_id) As DMXZ_Name,
(SELECT F_Name FROM TBL_QX WHERE TBL_QX.F_ID = b.QX_ID) As QX_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.HX_ID) As HX_Name,
(SELECT F_Name FROM TBL_RIVER WHERE TBL_RIVER.F_ID = b.RIVER_ID) As RIVER_Name,
(SELECT F_Name FROM TBL_LAKE WHERE TBL_LAKE.F_ID = b.LAKE_ID) As LAKE_Name,
(SELECT F_Name FROM TBL_RESERVOIR WHERE TBL_RESERVOIR.F_ID = b.RESERVOIR_ID) As RESERVOIR_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CQJ_ID) As CQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RQJ_ID) As RQJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.CSJ_ID) As CSJ_Name,
(SELECT F_Name FROM TBL_Code WHERE TBL_Code.F_ID = b.RSJ_ID) As RSJ_Name,
b.f_id b_id,
jc.f_id c_id,
b.F_STANDARD_DF ,
round(da.f_data,var.f_decimal) f_data,
da.variable_id
from tbl_dbscd cd left join tbl_dbssgjc jc on cd.f_id = jc.cd_id
left join tbl_brokenface b on cd.brokenface_id = b.f_id,
tbl_dbssgjc_data da,
tbl_variable_water var,
tbl_report_setwaterdm dm,
tbl_report_setwater
where jc.f_id = da.bb_id
and jc.f_date >= to_date('2008-01-01 ','yyyy-mm-dd')
and jc.f_date <= to_date('2008-12-31' ,'yyyy-mm-dd')
and var.f_code = da.variable_id
and dm.dm_id = b.f_id
and dm.setwater_id = tbl_report_setwater.f_id
And dm.config_id = 130
and var.f_code in ( 'XFW','ZS','ZDSS','HZD','NO2N','ZDLS','LSY','CODCr','BOD5')
AND jc.cd_id(+) = cd.f_id 比 AND jc.cd_id = cd.f_id
多的部分是 jc表中缺少的记录,此时jc中的记录全是null.
但你后面又用条件,限制jc
如
AND jc.f_date >= TO_DATE ('2008-01-01 ', 'yyyy-mm-dd')
AND jc.f_date <= TO_DATE ('2008-12-31', 'yyyy-mm-dd')
这个时候这些记录是不符合这个条件的,所以不会出现。解决方案:把所有单表限制条件先select成表。
表之间关联注意我前面说的东西。
那时因为你的左连接是jc.cd_id(+) = cd.f_id,之后jc.f_date >= TO_DATE ('2008-01-01 ', 'yyyy-mm-dd') AND jc.f_date <= TO_DATE ('2008-12-31', 'yyyy-mm-dd')这个条件直接会影响你的结果集。不明白LZ的问题要点在哪里,也可能是我理解问题
你的 and jc.f_date >= to_date('2008-01-01 ', 'yyyy-mm-dd')
and jc.f_date <= to_date('2008-12-31', 'yyyy-mm-dd')
把上面的外联接破坏了。f_date 这个条件可以写在left join on()里,
或者写个jc的子查询,查出jc.cd_id,然后和cd.f_id外联