select xxx,max(col1),max(col2)..... 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,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)a group by xx
建议楼主以后在设计表时,每个字段都不能为空,设置默认值 省得以后写sql去处理null的情况,更重要一点是如果按某字段 is null 查询时将会行扫瞄,影响查询速度
用聚合函数就可以了 MAX ,SUM,MIN,AVG等等。
用 isnull(XX,0),如果结果是null返回0
用你的方法我是这么写的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,但是结果还是一样是不是我哪写错了 2014-07-11 0 0 0 0 97560.9756097561 2014-07-11 0 876 8130.08130081301 76 0
我改成不允许为NULL,默认值也改为0,但是查询结果还是会有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_mbzdate不用group by
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 说错了,只要group by date,用我这个
类似这样---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-07-21 11:00:52 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([date] datetime,[zt_bll] int,[zt_mbz] int,[xz_bll] int,[xz_mbz] int,[hck_bll] numeric(15,10)) insert [huang] select '2014-07-19',0,null,null,null,97560.9756097561 union all select '2014-07-19',0,876,1000000,76,null --------------开始查询--------------------------select [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 [huang] a GROUP BY [date] ----------------结果---------------------------- /* date zt_bll zt_mbz xz_bll xz_mbz hck_bll ----------------------- ----------- ----------- ----------- ----------- --------------------------------------- 2014-07-19 00:00:00.000 0 876 1000000 76 97560.9756097561 */
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,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)a
group by xx
省得以后写sql去处理null的情况,更重要一点是如果按某字段 is null 查询时将会行扫瞄,影响查询速度
MAX ,SUM,MIN,AVG等等。
用你的方法我是这么写的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,但是结果还是一样是不是我哪写错了
2014-07-11 0 0 0 0 97560.9756097561
2014-07-11 0 876 8130.08130081301 76 0
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_mbzdate不用group by
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
说错了,只要group by date,用我这个
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-21 11:00:52
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([date] datetime,[zt_bll] int,[zt_mbz] int,[xz_bll] int,[xz_mbz] int,[hck_bll] numeric(15,10))
insert [huang]
select '2014-07-19',0,null,null,null,97560.9756097561 union all
select '2014-07-19',0,876,1000000,76,null
--------------开始查询--------------------------select [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 [huang] a
GROUP BY [date]
----------------结果----------------------------
/*
date zt_bll zt_mbz xz_bll xz_mbz hck_bll
----------------------- ----------- ----------- ----------- ----------- ---------------------------------------
2014-07-19 00:00:00.000 0 876 1000000 76 97560.9756097561
*/