全连接遇到空值怎么取消NULL
全连接遇到空值如何取消NULL
select ISNULL(LH_ZT.date,convert(varchar(10),getdate()- 1,120))AS date ,
isnull(SUM(LH_ZT.lh_fq)/ SUM(LH_ZT.lh_cl)*1000000,0) as zt_bll,MBZ_LH.zt_mbz ,
SUM(LH_XZ.lh_fq)/SUM(LH_XZ.lh_cl)*1000000 as xz_bll,MBZ_LH.xz_mbz as xz_mbz,
SUM(LH_HCK.lh_fq)/SUM(LH_HCK.lh_cl)*1000000 as hck_bll
from LH_ZT full JOIN LH_XZ ON LH_XZ.date = LH_ZT.date
full join MBZ_LH ON MBZ_LH.id=MBZ_LH.id
full join LH_HCK ON LH_HCK.date=LH_ZT.date
where LH_ZT.date=convert(varchar(10),getdate()- 1,120)
OR LH_XZ.date=convert(varchar(10),getdate()- 1,120)
OR LH_HCK.date=convert(varchar(10),getdate()- 1,120)
group by LH_ZT.date, MBZ_LH.zt_mbz,MBZ_LH.xz_mbz
查询结果是
2014-07-19 97560.9756097561 876 1000000 76 97560.9756097561
当有空值时结果是
2014-07-19 0 NULL NULL NULL 97560.9756097561
2014-07-19 0 876 1000000 76 NULL
如何将下面结果的NULL值取消掉，合并成上面的一条结果，该如何做？请大神指教。
------解决方案--------------------
SELECT a.date AS date ,
MAX(a.zt_bll) AS zt_bll ,
MAX(a.zt_mbz) AS zt_mbz ,
MAX(a.xz_bll) AS xz_bll ,
MAX(a.xz_mbz) AS xz_mbz ,
MAX(a.hck_bll) AS hck_bll
FROM ( SELECT ISNULL(LH_ZT.date,
CONVERT(VARCHAR(10), GETDATE() - 1, 120)) AS date ,
ISNULL(SUM(LH_ZT.lh_fq) / SUM(LH_ZT.lh_cl) * 1000000, 0) AS zt_bll ,
ISNULL(MBZ_LH.zt_mbz, 0) AS zt_mbz ,
ISNULL(SUM(LH_XZ.lh_fq) / SUM(LH_XZ.lh_cl) * 1000000, 0) AS xz_bll ,
ISNULL(MBZ_LH.xz_mbz, 0) AS xz_mbz ,
ISNULL(SUM(LH_HCK.lh_fq) / SUM(LH_HCK.lh_cl) * 1000000, 0) AS hck_bll
FROM LH_ZT
FULL JOIN LH_XZ ON LH_XZ.date = LH_ZT.date
FULL JOIN MBZ_LH ON MBZ_LH.id = MBZ_LH.id
FULL JOIN LH_HCK ON LH_HCK.date = LH_ZT.date
WHERE LH_ZT.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
OR LH_XZ.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
OR LH_HCK.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
GROUP BY LH_ZT.date ,
MBZ_LH.zt_mbz ,
MBZ_LH.xz_mbz
) a
GROUP BY --a.date ,
a.zt_mbz ,
a.xz_mbz
date不用group by
select ISNULL(LH_ZT.date,convert(varchar(10),getdate()- 1,120))AS date ,
isnull(SUM(LH_ZT.lh_fq)/ SUM(LH_ZT.lh_cl)*1000000,0) as zt_bll,MBZ_LH.zt_mbz ,
SUM(LH_XZ.lh_fq)/SUM(LH_XZ.lh_cl)*1000000 as xz_bll,MBZ_LH.xz_mbz as xz_mbz,
SUM(LH_HCK.lh_fq)/SUM(LH_HCK.lh_cl)*1000000 as hck_bll
from LH_ZT full JOIN LH_XZ ON LH_XZ.date = LH_ZT.date
full join MBZ_LH ON MBZ_LH.id=MBZ_LH.id
full join LH_HCK ON LH_HCK.date=LH_ZT.date
where LH_ZT.date=convert(varchar(10),getdate()- 1,120)
OR LH_XZ.date=convert(varchar(10),getdate()- 1,120)
OR LH_HCK.date=convert(varchar(10),getdate()- 1,120)
group by LH_ZT.date, MBZ_LH.zt_mbz,MBZ_LH.xz_mbz
查询结果是
2014-07-19 97560.9756097561 876 1000000 76 97560.9756097561
当有空值时结果是
2014-07-19 0 NULL NULL NULL 97560.9756097561
2014-07-19 0 876 1000000 76 NULL
如何将下面结果的NULL值取消掉，合并成上面的一条结果，该如何做？请大神指教。
------解决方案--------------------
SELECT a.date AS date ,
MAX(a.zt_bll) AS zt_bll ,
MAX(a.zt_mbz) AS zt_mbz ,
MAX(a.xz_bll) AS xz_bll ,
MAX(a.xz_mbz) AS xz_mbz ,
MAX(a.hck_bll) AS hck_bll
FROM ( SELECT ISNULL(LH_ZT.date,
CONVERT(VARCHAR(10), GETDATE() - 1, 120)) AS date ,
ISNULL(SUM(LH_ZT.lh_fq) / SUM(LH_ZT.lh_cl) * 1000000, 0) AS zt_bll ,
ISNULL(MBZ_LH.zt_mbz, 0) AS zt_mbz ,
ISNULL(SUM(LH_XZ.lh_fq) / SUM(LH_XZ.lh_cl) * 1000000, 0) AS xz_bll ,
ISNULL(MBZ_LH.xz_mbz, 0) AS xz_mbz ,
ISNULL(SUM(LH_HCK.lh_fq) / SUM(LH_HCK.lh_cl) * 1000000, 0) AS hck_bll
FROM LH_ZT
FULL JOIN LH_XZ ON LH_XZ.date = LH_ZT.date
FULL JOIN MBZ_LH ON MBZ_LH.id = MBZ_LH.id
FULL JOIN LH_HCK ON LH_HCK.date = LH_ZT.date
WHERE LH_ZT.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
OR LH_XZ.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
OR LH_HCK.date = CONVERT(VARCHAR(10), GETDATE() - 1, 120)
GROUP BY LH_ZT.date ,
MBZ_LH.zt_mbz ,
MBZ_LH.xz_mbz
) a
GROUP BY --a.date ,
a.zt_mbz ,
a.xz_mbz
date不用group by