MSSQL - 在 SELECT 语句中定义列名,然后在 WHERE 子句中使用它
有没有办法在带有 WHERE 子句的 SELECT 语句中使用定义的列名? 这是我的 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, 7, 4 ) + '-'
+ SUBSTRING( SendDate, 4, 2 ) + '-'
+ SUBSTRING( SendDate, 1, 2 )
+ SUBSTRING( SendDate, 11, 9 )
)
ELSE SendDate
END))) AS Table1.DateField,
SUBSTRING(Table1.Message,7,7) AS NumberField
FROM Table1
INNER JOIN
Table2 ON Table1.Sender = Table2.PhoneNumber,
(
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 SendDate
END)) AS DefinedDate
FROM Table1 WHERE Table1.Operator IS NOT NULL
) q
WHERE Operator IS NOT NULL AND SUBSTRING(Table1.Message,1,6) = 'TREE ST' AND DATEPART( wk, q.DefinedDate ) = 52 AND DATEPART(year, q.DefinedDate ) = 2010
SELECT * FROM #TEMP ORDER BY NumberField
DROP TABLE #TEMP
is there any way to use defined column name in SELECT statement with WHERE clause?
Here is my t-sql codes,
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
Yours affectionately and thank you very much for your helps, already now.
Edited part of posted
CREATE TABLE #TEMP
(
DateField DATETIME,
NumberField VARCHAR(10),
Sender VARCHAR(255)
)
INSERT #TEMP
SELECT DISTINCT(
CONVERT(DATETIME, (
CASE WHEN Operator = 'T'
THEN ( SUBSTRING( SendDate, 7, 4 ) + '-'
+ SUBSTRING( SendDate, 4, 2 ) + '-'
+ SUBSTRING( SendDate, 1, 2 )
+ SUBSTRING( SendDate, 11, 9 )
)
ELSE SendDate
END))) AS Table1.DateField,
SUBSTRING(Table1.Message,7,7) AS NumberField
FROM Table1
INNER JOIN
Table2 ON Table1.Sender = Table2.PhoneNumber,
(
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 SendDate
END)) AS DefinedDate
FROM Table1 WHERE Table1.Operator IS NOT NULL
) q
WHERE Operator IS NOT NULL AND SUBSTRING(Table1.Message,1,6) = 'TREE ST' AND DATEPART( wk, q.DefinedDate ) = 52 AND DATEPART(year, q.DefinedDate ) = 2010
SELECT * FROM #TEMP ORDER BY NumberField
DROP TABLE #TEMP
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不直接,但您可以将其包装在子选择中。
SQL语句
更新
我相信以下内容与您所写的内容相同:
Not directly but you could wrap it in a subselect.
SQL Statement
update
I believe following would be equivalent to what you wrote: