sql-server – MSSQL – 在SELECT语句中定义列名,然后在WHERE子

发布时间：2020-12-12 06:36:23 所属栏目：MsSql教程来源：网络整理

导读：有没有办法在SELECT语句中使用WHERE子句定义列名？这是我的t-sql代码, SELECT CONVERT(DATETIME,( CASE WHEN Operator = 'T' THEN (SUBSTRING(SendDate,7,4)+'-'+SUBSTRING(SendDate,4,2)+'-'+ SUBSTRING(SendDate,1,2) + SUBSTRING(SendDate,11,9)) ELSE Re

有没有办法在SELECT语句中使用WHERE子句定义列名？
这是我的t-sql代码,

SELECT CONVERT(DATETIME,(
    CASE WHEN Operator = 'T' THEN 
        (SUBSTRING(SendDate,7,4)+'-'+SUBSTRING(SendDate,4,2)+'-'+ 
        SUBSTRING(SendDate,1,2) + SUBSTRING(SendDate,11,9)) 
    ELSE 
        RecivedSMS.SendDate 
    END)) AS DefinedDate 
WHERE DATEPART(wk,DefinedDate) =  52

亲爱的,非常感谢你的帮助,现在已经.

编辑部分发布

CREATE TABLE #TEMP
(
DateField DATETIME,NumberField VARCHAR(10),Sender VARCHAR(255)
)

INSERT #TEMP
SELECT DISTINCT(
CONVERT(DATETIME,( 
                    CASE WHEN Operator = 'T' 
                    THEN (  SUBSTRING( SendDate,4 ) + '-'
                            + SUBSTRING( SendDate,2 ) + '-'
                            + SUBSTRING( SendDate,2 )
                            + SUBSTRING( SendDate,9 )
                         ) 
                    ELSE SendDate 
                    END))) AS Table1.DateField,SUBSTRING(Table1.Message,7) AS NumberField

FROM Table1
INNER JOIN 
Table2 ON Table1.Sender = Table2.PhoneNumber,(
          SELECT  CONVERT(DATETIME,9 )
                         ) 
                    ELSE SendDate 
                    END)) AS DefinedDate 
          FROM    Table1 WHERE Table1.Operator IS NOT NULL
        ) q  
WHERE Operator IS NOT NULL AND SUBSTRING(Table1.Message,6) = 'TREE ST'  AND DATEPART( wk,q.DefinedDate ) =  52 AND DATEPART(year,q.DefinedDate ) = 2010


SELECT *  FROM #TEMP ORDER BY NumberField

DROP TABLE #TEMP

解决方法

不是直接的,但你可以将它包装在一个subselect中.

SQL语句

SELECT  *
    FROM    (
              SELECT  CONVERT(DATETIME,( 
                        CASE WHEN Operator = 'T' 
                        THEN (  SUBSTRING( SendDate,4 ) + '-'
                                + SUBSTRING( SendDate,2 ) + '-'
                                + SUBSTRING( SendDate,2 )
                                + SUBSTRING( SendDate,9 )
                             ) 
                        ELSE RecivedSMS.SendDate 
                        END)) AS DefinedDate 
              FROM    YourTable
            ) q          
    WHERE   DATEPART( wk,DefinedDate ) =  52

更新

我相信以下内容相当于你写的内容：

SELECT  q.DefinedDate,q.NumberField
FROM    (
          SELECT  Sender,NumberField = SUBSTRING(Table1.Message,7),Operator,CONVERT(
                      DATETIME,( 
                          CASE WHEN Operator = 'T' 
                          THEN SUBSTRING( SendDate,4 ) + '-'
                               + SUBSTRING( SendDate,2 ) + '-'
                               + SUBSTRING( SendDate,2 )
                               + SUBSTRING( SendDate,9 )
                          ELSE SendDate 
                          END )) AS DefinedDate 
          FROM    Table1 WHERE Table1.Operator IS NOT NULL
        ) q  
        INNER JOIN Table2 ON Table2.PhoneNumber = Tabl1.Sender
WHERE   q.NumberField LIKE 'TREE ST%'  
        AND DATEPART( wk,q.DefinedDate ) =  52 
        AND DATEPART( year,q.DefinedDate ) = 2010

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!