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