none
如何把加了Case的某列,作为变量使用? RRS feed

  • 问题

  • 合同表(ContractTable)中有合同起始日(LeaseDateFrom)和合同终止日(LeaseDateTo)。一般来说合同期限整年整年的,那么LeaseDateFrom和 LeaseDateTo之间的日期相差1年、2年、3年。。。。。。

    现在出现了特殊情况,例如:LeaseDateFrom是2017-5-1,LeaseDataTo是2018-12-31,相差不是整年。

    我希望如果两个日期相差不是整年的时候,给LeaseDateFrom加一个Case,变成新的一列“time1”,让这个time1增加到和LeaseDateTo保持相差是整年的关系。

    也就是我把LeaseDateFrom (2017-5-1)用time1代替。time1的值经过Case的运算后为2017-1-1,这样time1和 LeaseDateTo之间相差正好又是整年了,之后就用time1取代LeaseDateFrom进行运算。

    ============问题================

    因为我的SQL语句中含有海量的这种time1,如果全部都傻傻的每次都用Case判断一次,代码需要写几千行。

    因此,我觉得应该把time1先以变量的形式,声明在SQL语句的最开头。

    然而,图1中的time1并不能把象变量或字段那样直接在 SQL中使用,否则就会出现错误提示:“列名time1无效”。请问该如何处理呢?

    =============代码=====================

    select 
    (case 
    when DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))%12 =0
    then LeaseDateFrom
    else DATEADD(YEAR,-(DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))/12+1),DATEADD
    
    (DAY,1,LeaseDateTo)) 
    end) as time1
    
    ,LeaseDateFrom 
    ,LeaseDateTo
    ,DATEDIFF (MONTH,time1,DATEADD(day,1,LeaseDateTo)) as 'time1到LeaseDateTo的月份'
    ,DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo)) as 'LeaseDateFrom到LeaseDateTo的月份'
    
    from ContractTable

    ============图1================


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。

    2017年12月27日 0:41

全部回复

  • 几个办法:

    1.将case内容都作为参数;(一般不推荐,可读性差)

    2.在外面再用一个select语句包含这个子查询,在外面的select中使用time1就不会报错了;

    3.添加一个计算列,直接使用这个计算列也可以;


    family as water

    2017年12月27日 0:50
  • 字段名或表名做参数,需要用到参数查询

    参考下这个DEMO

    USE tempdb;
    GO

    SET NOCOUNT ON;

    -- Drops demo table if exists
    IF (EXISTS (SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'dbo'
        AND  TABLE_NAME = 'myTable'))
    BEGIN
        DROP TABLE myTable;
    END

    -- Creates demo table
    CREATE TABLE myTable(
        [Year] INT,
        Month1 INT,
        Month2 INT,
        Month3 INT,
        Month4 INT,
        Month5 INT,
        Month6 INT,
        Month7 INT,
        Month8 INT,
        Month9 INT,
        Month10 INT,
        Month11 INT,
        Month12 INT
    )

    -- Inserts data into demo table
    INSERT INTO myTable VALUES (2011, 7, 1, 6, 2, 9, 12, 32, 12, 10, 0, 0, 5);
    INSERT INTO myTable VALUES (2012, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
    INSERT INTO myTable VALUES (2013, 11, 12, 13, 14, 15, 16, 17, 18, 19, 110, 111, 112);



    -- This is the year your want to return data:
    DECLARE @Year INT = 2013;
    DECLARE @strYear VARCHAR(4) = CAST(@Year AS VARCHAR(4));

    -- Creates the select statement
    DECLARE @SQLString NVARCHAR(MAX);
    SET @SQLString = N'SELECT Month1 AS [JAN ' + @strYear + '], Month2 AS [FEV ' + @strYear + '], Month3 AS [MAR ' + @strYear + '], Month4 AS [APR ' + @strYear + '], Month5 AS [MAY ' + @strYear + '], Month6 AS [JUN ' + @strYear + '], Month7 AS [JUL ' + @strYear + '], Month8 AS [AGO ' + @strYear + '], Month9 AS [SET ' + @strYear + '], Month10 AS [OCT ' + @strYear + '], Month11 AS [NOV ' + @strYear + '], Month12 AS [DEC ' + @strYear + '] FROM dbo.myTable WHERE [Year]=@Year';

    -- Executes select statement for the selected @Year
    EXECUTE sp_executesql @SQLString, N'@Year INT', @Year

    来自

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cf1ff4f2-a57d-492b-b57f-515aac79e9c4/how-to-get-dynamic-column-names-in-select-statement?forum=transactsql

    You have to use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/




    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2017年12月27日 1:04
  • 几个办法:

    1.将case内容都作为参数;(一般不推荐,可读性差)

    2.在外面再用一个select语句包含这个子查询,在外面的select中使用time1就不会报错了;

    3.添加一个计算列,直接使用这个计算列也可以;


    family as water


    select * ,
    ,DATEDIFF (MONTH,time1,DATEADD(day,1,LeaseDateTo)) as 'time1到LeaseDateTo的月份'
    ,DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo)) as 'LeaseDateFrom到LeaseDateTo的月份'
    from 
    (
    select 
    (case 
    when DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))%12 =0
    then LeaseDateFrom
    else DATEADD(YEAR,-(DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))/12+1),DATEADD
    
    (DAY,1,LeaseDateTo)) 
    end) as time1
    
    ,LeaseDateFrom 
    ,LeaseDateTo
    
    from ContractTable
    ) t
    

    补充一下,类似这样

    family as water

    2017年12月27日 5:12
  • 谢谢。第二种方法看懂了,想问问第三种方法是如何实现的?

    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。

    2017年12月27日 12:53
  • 字段名或表名做参数,需要用到参数查询

    参考下这个DEMO

    USE tempdb;
    GO

    SET NOCOUNT ON;

    -- Drops demo table if exists
    IF (EXISTS (SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'dbo'
        AND  TABLE_NAME = 'myTable'))
    BEGIN
        DROP TABLE myTable;
    END

    -- Creates demo table
    CREATE TABLE myTable(
        [Year] INT,
        Month1 INT,
        Month2 INT,
        Month3 INT,
        Month4 INT,
        Month5 INT,
        Month6 INT,
        Month7 INT,
        Month8 INT,
        Month9 INT,
        Month10 INT,
        Month11 INT,
        Month12 INT
    )

    -- Inserts data into demo table
    INSERT INTO myTable VALUES (2011, 7, 1, 6, 2, 9, 12, 32, 12, 10, 0, 0, 5);
    INSERT INTO myTable VALUES (2012, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
    INSERT INTO myTable VALUES (2013, 11, 12, 13, 14, 15, 16, 17, 18, 19, 110, 111, 112);



    -- This is the year your want to return data:
    DECLARE @Year INT = 2013;
    DECLARE @strYear VARCHAR(4) = CAST(@Year AS VARCHAR(4));

    -- Creates the select statement
    DECLARE @SQLString NVARCHAR(MAX);
    SET @SQLString = N'SELECT Month1 AS [JAN ' + @strYear + '], Month2 AS [FEV ' + @strYear + '], Month3 AS [MAR ' + @strYear + '], Month4 AS [APR ' + @strYear + '], Month5 AS [MAY ' + @strYear + '], Month6 AS [JUN ' + @strYear + '], Month7 AS [JUL ' + @strYear + '], Month8 AS [AGO ' + @strYear + '], Month9 AS [SET ' + @strYear + '], Month10 AS [OCT ' + @strYear + '], Month11 AS [NOV ' + @strYear + '], Month12 AS [DEC ' + @strYear + '] FROM dbo.myTable WHERE [Year]=@Year';

    -- Executes select statement for the selected @Year
    EXECUTE sp_executesql @SQLString, N'@Year INT', @Year

    来自

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cf1ff4f2-a57d-492b-b57f-515aac79e9c4/how-to-get-dynamic-column-names-in-select-statement?forum=transactsql

    You have to use dynamic SQL:

    http://www.sqlusa.com/bestpractices/dynamicsql/




    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    这个答案并不符合我的要求。

    这个答案只是把已知的month“丰富”了一下而已。而我需要的是对“丰富”后的month,象普通的数据库列那样可以进行交互运算。



    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。

    2017年12月28日 0:51
  • alter table ContractTable
    add time1 as case 
    when DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))%12 =0
    then LeaseDateFrom
    else DATEADD(YEAR,-(DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo))/12+1),DATEADD
    
    (DAY,1,LeaseDateTo)) 
    end
    
    
    go
    
    select * ,
    ,DATEDIFF (MONTH,time1,DATEADD(day,1,LeaseDateTo)) as 'time1到LeaseDateTo的月份'
    ,DATEDIFF (MONTH,LeaseDateFrom,DATEADD(day,1,LeaseDateTo)) as 'LeaseDateFrom到LeaseDateTo的月份'
    from 
    ContractTable

    差不多这样,详细可以看看计算列的相关资料:https://docs.microsoft.com/zh-cn/sql/relational-databases/tables/specify-computed-columns-in-a-table

    family as water

    2017年12月28日 2:09