多表case选择问题，急

我是三峡移民 2017-03-17 09:27:23 
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,

ccv.Reading_Date as L_Reading_Date,meter.FactorySN,auto.Communication_No,con.Concentrator_No,rep.Repeater_No,ccv.This_Reading_Value,case when ccv.This_Reading_Value='1' then '2' end

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-03-01'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

这样写不会报错，但是我想的是把ccv.This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end
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,

ccv.Reading_Date as L_Reading_Date,meter.FactorySN,auto.Communication_No,con.Concentrator_No,rep.Repeater_No,ccv.This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end

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-03-01'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

这样写会报错，求助
...全文
280 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
唐诗三百首 2017-03-17
  • 打赏
  • 举报
 回复
引用 4 楼 qq_34082025 的回复:
我已经这样做了，但是我想知道那样写为什么会错
原写法的问题是, 查询返回时,列的别名不能是"表名(别名).字段名"的形式.
二月十六 版主 2017-03-17
  • 打赏
  • 举报
 回复
引用 4 楼 qq_34082025 的回复:
[quote=引用 3 楼 sinat_28984567 的回复:] 
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 ,
        ccv.Reading_Date AS L_Reading_Date ,
        meter.FactorySN ,
        auto.Communication_No ,
        con.Concentrator_No ,
        rep.Repeater_No ,        
        (CASE WHEN ccv.This_Reading_Value = '1' THEN '2' ELSE ccv.This_Reading_Value
        END) AS This_Reading_Value
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-03-01'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
我已经这样做了，但是我想知道那样写为什么会错[/quote] 报什么错？
唐诗三百首 2017-03-17
  • 打赏
  • 举报
 回复
try this, 

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,
ccv.Reading_Date as L_Reading_Date,meter.FactorySN,auto.Communication_No,con.Concentrator_No,rep.Repeater_No,
This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end
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-03-01'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
卖水果的net 版主 2017-03-17
  • 打赏
  • 举报
 回复
引用 5 楼 qq_34082025 的回复:
[quote=引用 1 楼 wmxcn2000 的回复:] 
ccv.This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end

-- 把前面的 ccv 去掉，点也去掉

This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end
去掉不行哦，去掉就是其他字段的值的，不一定是ccv这个表[/quote] XX = case when ...end XX 是一个新列名，后面是计算表达式；必须去掉，表达式中不能去掉；
我是三峡移民 2017-03-17
  • 打赏
  • 举报
 回复
引用 1 楼 wmxcn2000 的回复:
ccv.This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end

-- 把前面的 ccv 去掉，点也去掉

This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end
去掉不行哦，去掉就是其他字段的值的，不一定是ccv这个表
我是三峡移民 2017-03-17
  • 打赏
  • 举报
 回复
引用 3 楼 sinat_28984567 的回复:
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 ,
        ccv.Reading_Date AS L_Reading_Date ,
        meter.FactorySN ,
        auto.Communication_No ,
        con.Concentrator_No ,
        rep.Repeater_No ,        
        (CASE WHEN ccv.This_Reading_Value = '1' THEN '2' ELSE ccv.This_Reading_Value
        END) AS This_Reading_Value
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-03-01'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
我已经这样做了，但是我想知道那样写为什么会错
二月十六 版主 2017-03-17
  • 打赏
  • 举报
 回复 
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 ,
        ccv.Reading_Date AS L_Reading_Date ,
        meter.FactorySN ,
        auto.Communication_No ,
        con.Concentrator_No ,
        rep.Repeater_No ,        
        (CASE WHEN ccv.This_Reading_Value = '1' THEN '2' ELSE ccv.This_Reading_Value
        END) AS This_Reading_Value
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-03-01'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
二月十六 版主 2017-03-17
  • 打赏
  • 举报
 回复
额，你想要啥效果，等于1的时候，让它显示2？其他的时候显示本身的值？
卖水果的net 版主 2017-03-17
  • 打赏
  • 举报
 回复 
ccv.This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end

-- 把前面的 ccv 去掉，点也去掉

This_Reading_Value=case when ccv.This_Reading_Value='1' then '2' end
SAP ABAP新语法
1. DATA and FIELD-SYMBOLS declarations 内联声明2. Table Expressions3. Conversion Operator CONV4. Value Operator VALUE5. FOR operator6. LET operator7. Reduction operator REDUCE8. Conditional operators COND and SWITCH9. Corresponding Operator10. Strings11. Loop at Group By12. Classes/Methods13. Filter14. ABAP SQL  -- 聚合表达式15. ABAP SQL  -- 嵌入表达式16. ABAP SQL  -- 条件语句 CASE17. ABAP SQL  -- NULL VALUE18. ABAP SQL  -- 类型转换 CAST19. ABAP SQL  -- 内表20. ABAP SQL  -- JOIN表连接21. ABAP SQL  -- WHERE条件
京东面试题：Java中的Switch中的case后可否使用String？为什么？
京东面试题：Java中的Switch中的case后的量可否使用String，为什么？这个问题在Java开发中经常会遇到，也是面试中经常被问到的问题。本文将详细解答这个问题，帮助你更好地理解Java中的Switch语句。
三大结构之选择结构
选择结构 if单选泽结构：很多时候需要去判断一个东西是否可行，然后我们才去执行，这样一个过程在程序中用if语句来表示 语法： if(布尔表达式){ //如果布尔表达式为ture将执行的语句 } if双语句选择结构 急找人开发这样的需求，用一个if就搞不定了，我们需要有两个判断，需要一个双选择结构，所以就有了if-else结构 if(布尔表达式){ //如果布尔表达式的值为ture }else{ //如果布尔表达式的值为flase } if多选择结构 我们发现刚才的代码不符合实际情况，真实
java重新循环_Java中的while循环结构 用户重新输入的代码问题！急急急！！
Java中的while循环结构用户重新输入的代码问题！急急急！！需求描述：进入系统主菜单后,提示用户输入数字,然后根据选择进入相应的功能模块,如果用户输入错误,可以重复输入,直到输入正...Java中的while循环结构 用户重新输入的代码问题！急急急！！需求描述：进入系统主菜单后,提示用户输入数字,然后根据选择进入相应的功能模块,如果用户输入错误,可以重复输入,直到输入正确,执行相应的操作后退出...
选择结构 if and switch
在解决实际问题时，经常有遇到需要根据某个条件是否满足来决定执行指定操作的情况，或者从给定的两种或多种操作中选择其中一个。这个时候我们就可以利用选择结构if 或者switch程序设计思路来解决问题
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章