WITH a1 (CityID,CityName) AS ( SELECT 1,'北京' UNION ALL SELECT 2,'上海' UNION ALL SELECT 3,'天津' ) ,a2 AS ( SELECT MAX(CityID) CityID FROM a1 ) SELECT a1.*,CASE WHEN a1.CityID=a2.CityID THEN 1 ELSE 0 END lastRecord FROM a1,a2
SELECT T1.*,ISNULL(T1.CityID/T2.CityID,0)lastRecord ,CASE WHEN T2.CityID IS NULL THEN 0 ELSE 1 END lastRecord --如果CityID不是数值,就用CASE FROM TB T1 LEFT JOIN(SELECT MAX(CityID)CityID FROM TB)T2 ON T1.CityID=T2.CityID
WITH a1 (CityID,CityName) AS
(
SELECT 1,'北京' UNION ALL
SELECT 2,'上海' UNION ALL
SELECT 3,'天津'
)
,a2 AS
(
SELECT MAX(CityID) CityID FROM a1
)
SELECT a1.*,CASE WHEN a1.CityID=a2.CityID THEN 1 ELSE 0 END lastRecord
FROM a1,a2
,CASE WHEN T2.CityID IS NULL THEN 0 ELSE 1 END lastRecord
--如果CityID不是数值,就用CASE
FROM TB T1
LEFT JOIN(SELECT MAX(CityID)CityID FROM TB)T2
ON T1.CityID=T2.CityID