select area.Area_Name,
cust.Customer_Name,
cust.Customer_Code,
meterd.*,
spec.Meter_Specifications_Name,
type.Meter_Type_Name,
cust.id as Customer_IDs,
kind.Price_Kind_Name,
kind.Big_Threshold,
kind.Small_Threshold,
dic.FieldValue as Ladder_Flag,
meter.FactorySN,
auto.Communication_No,
con.Concentrator_No,
rep.Repeater_No,
case when meter.Replace_Flag='1' then ccm.New_Meter_Initial_Value when meter.Replace_Flag!='1' then ISNULL(ccv.This_Reading_Value,meter.Meter_Initial_Value) end L_Reading_Value,
case when meter.Replace_Flag='1' then null when meter.Replace_Flag!='1' then ISNULL(ccv.Reading_Date,null) end L_Reading_Date
from CB_Customer cust
left join (select a.* from CI_MeterData a inner join (select Customer_ID , max(Reading_Time) Reading_Time from CI_MeterData where Reading_Time<'2017-02-25'group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.Reading_Time = b.Reading_Time) as meterd on cust.ID=meterd.Customer_ID
left join CB_Area area on area.ID=cust.Area_ID
left join CB_Meter meter on meter.Customer_ID=cust.ID
left join CB_MeterSpec spec on spec.ID=meter.Specifications_ID
left join CB_MeterType type on type.ID=meter.Meter_Type_ID
left join CB_UserKind kind on kind.Price_Kind_ID=meter.Price_Kind_ID
left join (select a.* from CI_CustCurVol a inner join (select Customer_ID , max(CreatedOn) CreatedOn from CI_CustCurVol group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.CreatedOn = b.CreatedOn) as ccv on cust.ID=ccv.Customer_ID
left join CB_Price_Suite Ps on kind.Price_Kind_ID=Ps.Price_Kind_ID
left join SYS_Dictionary dic on dic.FieldName = 'Ladder_Flag'and dic.FiledExplain = Ps.Ladder_Flag
left join CB_AutoMeter auto on auto.Meter_ID=meter.ID
left join CB_Concentrators con on con.ID=auto.Concentrator_ID
left join CB_Repeater rep on rep.ID=auto.Repeater_ID
left join CI_ChangMeter ccm on ccm.Customer_ID=cust.ID and meter.Replace_Flag='1'
where cust.Billing_Type='3' and cust.Delete_Flag=0 and exists(select 1 from CB_UserKind,CB_Customer s where meter.Price_Kind_ID=Price_Kind_ID and s.ID=cust.ID and (meterd.Reading_Value-L_Reading_Value<Small_Threshold))
问题在最后一句,我想把列里面的L_Reading_Value,取到where里面去判断,但是取不了,怎么取?
cust.Customer_Name,
cust.Customer_Code,
meterd.*,
spec.Meter_Specifications_Name,
type.Meter_Type_Name,
cust.id as Customer_IDs,
kind.Price_Kind_Name,
kind.Big_Threshold,
kind.Small_Threshold,
dic.FieldValue as Ladder_Flag,
meter.FactorySN,
auto.Communication_No,
con.Concentrator_No,
rep.Repeater_No,
case when meter.Replace_Flag='1' then ccm.New_Meter_Initial_Value when meter.Replace_Flag!='1' then ISNULL(ccv.This_Reading_Value,meter.Meter_Initial_Value) end L_Reading_Value,
case when meter.Replace_Flag='1' then null when meter.Replace_Flag!='1' then ISNULL(ccv.Reading_Date,null) end L_Reading_Date
from CB_Customer cust
left join (select a.* from CI_MeterData a inner join (select Customer_ID , max(Reading_Time) Reading_Time from CI_MeterData where Reading_Time<'2017-02-25'group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.Reading_Time = b.Reading_Time) as meterd on cust.ID=meterd.Customer_ID
left join CB_Area area on area.ID=cust.Area_ID
left join CB_Meter meter on meter.Customer_ID=cust.ID
left join CB_MeterSpec spec on spec.ID=meter.Specifications_ID
left join CB_MeterType type on type.ID=meter.Meter_Type_ID
left join CB_UserKind kind on kind.Price_Kind_ID=meter.Price_Kind_ID
left join (select a.* from CI_CustCurVol a inner join (select Customer_ID , max(CreatedOn) CreatedOn from CI_CustCurVol group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.CreatedOn = b.CreatedOn) as ccv on cust.ID=ccv.Customer_ID
left join CB_Price_Suite Ps on kind.Price_Kind_ID=Ps.Price_Kind_ID
left join SYS_Dictionary dic on dic.FieldName = 'Ladder_Flag'and dic.FiledExplain = Ps.Ladder_Flag
left join CB_AutoMeter auto on auto.Meter_ID=meter.ID
left join CB_Concentrators con on con.ID=auto.Concentrator_ID
left join CB_Repeater rep on rep.ID=auto.Repeater_ID
left join CI_ChangMeter ccm on ccm.Customer_ID=cust.ID and meter.Replace_Flag='1'
where cust.Billing_Type='3' and cust.Delete_Flag=0 and exists(select 1 from CB_UserKind,CB_Customer s where meter.Price_Kind_ID=Price_Kind_ID and s.ID=cust.ID and (meterd.Reading_Value-L_Reading_Value<Small_Threshold))
问题在最后一句,我想把列里面的L_Reading_Value,取到where里面去判断,但是取不了,怎么取?
from
(
select area.Area_Name,
cust.Customer_Name,
cust.Customer_Code,
meterd.*,
spec.Meter_Specifications_Name,
type.Meter_Type_Name,
cust.id as Customer_IDs,
kind.Price_Kind_Name,
kind.Big_Threshold,
kind.Small_Threshold,
dic.FieldValue as Ladder_Flag,
meter.FactorySN,
auto.Communication_No,
con.Concentrator_No,
rep.Repeater_No,
case when meter.Replace_Flag='1' then ccm.New_Meter_Initial_Value when meter.Replace_Flag!='1' then ISNULL(ccv.This_Reading_Value,meter.Meter_Initial_Value) end L_Reading_Value,
case when meter.Replace_Flag='1' then null when meter.Replace_Flag!='1' then ISNULL(ccv.Reading_Date,null) end L_Reading_Date
from CB_Customer cust
left join (select a.* from CI_MeterData a inner join (select Customer_ID , max(Reading_Time) Reading_Time from CI_MeterData where Reading_Time<'2017-02-25'group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.Reading_Time = b.Reading_Time) as meterd on cust.ID=meterd.Customer_ID
left join CB_Area area on area.ID=cust.Area_ID
left join CB_Meter meter on meter.Customer_ID=cust.ID
left join CB_MeterSpec spec on spec.ID=meter.Specifications_ID
left join CB_MeterType type on type.ID=meter.Meter_Type_ID
left join CB_UserKind kind on kind.Price_Kind_ID=meter.Price_Kind_ID
left join (select a.* from CI_CustCurVol a inner join (select Customer_ID , max(CreatedOn) CreatedOn from CI_CustCurVol group by Customer_ID) b on a.Customer_ID = b.Customer_ID and a.CreatedOn = b.CreatedOn) as ccv on cust.ID=ccv.Customer_ID
left join CB_Price_Suite Ps on kind.Price_Kind_ID=Ps.Price_Kind_ID
left join SYS_Dictionary dic on dic.FieldName = 'Ladder_Flag'and dic.FiledExplain = Ps.Ladder_Flag
left join CB_AutoMeter auto on auto.Meter_ID=meter.ID
left join CB_Concentrators con on con.ID=auto.Concentrator_ID
left join CB_Repeater rep on rep.ID=auto.Repeater_ID
left join CI_ChangMeter ccm on ccm.Customer_ID=cust.ID and meter.Replace_Flag='1'
where cust.Billing_Type='3' and cust.Delete_Flag=0 and exists(select 1 from CB_UserKind,CB_Customer s where meter.Price_Kind_ID=Price_Kind_ID and s.ID=cust.ID and (meterd.Reading_Value-L_Reading_Value<Small_Threshold)))v
where L_Reading_Value = 你的值
cust.Customer_Name ,
cust.Customer_Code ,
meterd.* ,
spec.Meter_Specifications_Name ,
type.Meter_Type_Name ,
cust.id AS Customer_IDs ,
kind.Price_Kind_Name ,
kind.Big_Threshold ,
kind.Small_Threshold ,
dic.FieldValue AS Ladder_Flag ,
meter.FactorySN ,
auto.Communication_No ,
con.Concentrator_No ,
rep.Repeater_No ,
CASE WHEN meter.Replace_Flag = '1' THEN ccm.New_Meter_Initial_Value
WHEN meter.Replace_Flag != '1'
THEN ISNULL(ccv.This_Reading_Value, meter.Meter_Initial_Value)
END L_Reading_Value ,
CASE WHEN meter.Replace_Flag = '1' THEN NULL
WHEN meter.Replace_Flag != '1'
THEN ISNULL(ccv.Reading_Date, NULL)
END L_Reading_Date
FROM CB_Customer cust
LEFT JOIN ( SELECT a.*
FROM CI_MeterData a
INNER JOIN ( SELECT Customer_ID ,
MAX(Reading_Time) Reading_Time
FROM CI_MeterData
WHERE Reading_Time < '2017-02-25'GROUP BY Customer_ID
) b ON a.Customer_ID = b.Customer_ID
AND a.Reading_Time = b.Reading_Time
) AS meterd ON cust.ID = meterd.Customer_ID
LEFT JOIN CB_Area area ON area.ID = cust.Area_ID
LEFT JOIN CB_Meter meter ON meter.Customer_ID = cust.ID
LEFT JOIN CB_MeterSpec spec ON spec.ID = meter.Specifications_ID
LEFT JOIN CB_MeterType type ON type.ID = meter.Meter_Type_ID
LEFT JOIN CB_UserKind kind ON kind.Price_Kind_ID = meter.Price_Kind_ID
LEFT JOIN ( SELECT a.*
FROM CI_CustCurVol a
INNER JOIN ( SELECT Customer_ID ,
MAX(CreatedOn) CreatedOn
FROM CI_CustCurVol
GROUP BY Customer_ID
) b ON a.Customer_ID = b.Customer_ID
AND a.CreatedOn = b.CreatedOn
) AS ccv ON cust.ID = ccv.Customer_ID
LEFT JOIN CB_Price_Suite Ps ON kind.Price_Kind_ID = Ps.Price_Kind_ID
LEFT JOIN SYS_Dictionary dic ON dic.FieldName = 'Ladder_Flag'
AND dic.FiledExplain = Ps.Ladder_Flag
LEFT JOIN CB_AutoMeter auto ON auto.Meter_ID = meter.ID
LEFT JOIN CB_Concentrators con ON con.ID = auto.Concentrator_ID
LEFT JOIN CB_Repeater rep ON rep.ID = auto.Repeater_ID
LEFT JOIN CI_ChangMeter ccm ON ccm.Customer_ID = cust.ID
AND meter.Replace_Flag = '1'
WHERE cust.Billing_Type = '3'
AND cust.Delete_Flag = 0
AND EXISTS ( SELECT 1
FROM CB_UserKind ,
CB_Customer s
WHERE meter.Price_Kind_ID = Price_Kind_ID
AND s.ID = cust.ID
AND ( meterd.Reading_Value - L_Reading_Value < Small_Threshold ) )
AND ( CASE WHEN meter.Replace_Flag = '1'
THEN ccm.New_Meter_Initial_Value
WHEN meter.Replace_Flag != '1'
THEN ISNULL(ccv.This_Reading_Value,
meter.Meter_Initial_Value)
END ) = 你的值;