如何把加了Case的某列，作为变量使用？

[weixin_38062043]

合同表（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# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。

[weixin_38073873]

字段名或表名做参数，需要用到参数查询

参考下这个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

[weixin_38081265]

几个办法：
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

[weixin_38066001]

几个办法：
1.将case内容都作为参数；（一般不推荐，可读性差）
2.在外面再用一个select语句包含这个子查询，在外面的select中使用time1就不会报错了；
3.添加一个计算列，直接使用这个计算列也可以；family as water

[weixin_38096967]

谢谢。第二种方法看懂了，想问问第三种方法是如何实现的？C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。

[weixin_38128682]

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-tablefamily as water

[weixin_38102396]

字段名或表名做参数，需要用到参数查询

参考下这个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# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。