当之前使用相同的DateDiff没有问题时，列名无效

Question

SELECT 
firstname, lastname, datetake, DateReturn,
DATEDIFF(DAY, DateTake, DateReturn) AS Delay
FROM 
dbo.ReaderCard, dbo.Orders, dbo.Literature
WHERE 
dbo.ReaderCard.CardID = dbo.Orders.CardID
AND dbo.Literature.ItemID = dbo.Orders.ItemID
IF DATEDIFF(DAY, DateTake, DateReturn) > 60
SELECT (DATEDIFF(DAY, DateTake, DateReturn) * 1.5) AS FineSum
FROM dbo.Orders

我一直得到一个错误&;无效的列名&;在datetake和datereturn上的IF(不是IF中的选择)，我不知道如何修复它。请给我点东西……

Answer 1

我认为你只是在SELECT中的CASE表达式之后，你需要在同一语句中做，因为SQL Server在IF语句中没有内存，你可能在其他一些语句中引用:

SELECT firstname, lastname, datetake, DateReturn
, Delay = CASE WHEN DATEDIFF(DAY,DateTake,DateReturn) > 60
THEN 1.5 ELSE 1.0 END * DATEDIFF(DAY,DateTake,DateReturn)
FROM dbo.ReaderCard, dbo.Orders, dbo.Literature
WHERE dbo.ReaderCard.CardID = dbo.Orders.CardID
AND dbo.Literature.ItemID = dbo.Orders.ItemID;

但是你也应该使用正确的INNER JOIN语法，这种FROM tbl, tbl, tbl形式在90年代已经过时了，这是有充分理由的:

-- Prefix all these columns with the alias!
SELECT firstname, lastname, datetake, DateReturn
, Delay = CASE WHEN DATEDIFF(DAY,DateTake,DateReturn) > 60
THEN 1.5 ELSE 1.0 END * DATEDIFF(DAY,DateTake,DateReturn)
FROM dbo.ReaderCard AS rc
INNER JOIN dbo.Orders AS o
ON rc.CardID = o.CardID
INNER JOIN dbo.Literature AS l
ON l.ItemID = o.ItemID;