...终于明白需求了... (GetDate()处需要截断到日,你说会自己处理,我就不处理了)SELECT ThemeId, CASE WHEN CityId=2 THEN '上海' WHEN CityId=1 THEN '北京' WHEN CityId=32 THEN '广州' WHEN CityId=30 THEN '深圳' WHEN CityId=37 THEN '丽江' ELSE '' END CityName, BeginDate, CASE WHEN BeginDate>GETDATE() THEN '未上线' WHEN BeginDate=(select MAX(BeginDate) from GHP_GroupTheme g2 where BeginDate<=GETDATE() and g1.CityId=g2.CityId ) THEN '已上线' ELSE '已下线' END GroupThemeStatus FROM GHP_GroupTheme g1 (NOLOCK)
SELECT ThemeId, CASE WHEN CityId=2 THEN '上海' WHEN CityId=1 THEN '北京' WHEN CityId=32 THEN '广州' WHEN CityId=30 THEN '深圳' WHEN CityId=37 THEN '丽江' ELSE '' END CityName, BeginDate, CASE WHEN BeginDate>GETDATE() THEN '未上线' when themeld=(select MAX(themeld) from GHP_GroupTheme as g2 where g1.cityid=g2.cityid and g2.begindate<=GETDATE()) ELSE '已下线' END GroupThemeStatus FROM GHP_GroupTheme(NOLOCK) as g1 试试
SELECT ThemeId, CASE WHEN CityId=2 THEN '上海' WHEN CityId=1 THEN '北京' WHEN CityId=32 THEN '广州' WHEN CityId=30 THEN '深圳' WHEN CityId=37 THEN '丽江' ELSE '' END CityName, BeginDate, CASE WHEN BeginDate>GETDATE() THEN '未上线' WHEN not exists(select * from GHP_GroupTheme where cityid=t.cityid and themeID>t.themeID) and BeginDate <=GETDATE() THEN '已上线' ELSE '已下线' END GroupThemeStatus FROM GHP_GroupTheme(NOLOCK) t
建议不要用CASE WHEN 来判断 直接用IF ..ELSE来判断。
SELECT ThemeId, CASE WHEN CityId=2 THEN '上海' WHEN CityId=1 THEN '北京' WHEN CityId=32 THEN '广州' WHEN CityId=30 THEN '深圳' WHEN CityId=37 THEN '丽江' ELSE '' END CityName, BeginDate, CASE WHEN BeginDate>GETDATE() THEN '未上线' when themeld=(select MAX(themeld) from GHP_GroupTheme as g2 where g1.cityid=g2.cityid and g2.begindate<=GETDATE()) then '已上线' ELSE '已下线' END GroupThemeStatus FROM GHP_GroupTheme(NOLOCK) as g1
SELECT ThemeId, CASE WHEN CityId=2 THEN '上海' WHEN CityId=1 THEN '北京' WHEN CityId=32 THEN '广州' WHEN CityId=30 THEN '深圳' WHEN CityId=37 THEN '丽江' ELSE '' END CityName, BeginDate, CASE WHEN BeginDate>GETDATE() THEN '未上线' WHEN ThemeId=(select max(ThemeId) from GHP_GroupTheme as b where b.CityId=a.CityId ) THEN '已上线' ELSE '已下线' END GroupThemeStatus FROM GHP_GroupTheme(NOLOCK) as a
在 CASE 中做 SELECT MAX(),实在看不下去了。 DECLARE @今天 DateTime SET @今天 = '2014-08-21' --发帖的日子 ; WITH GroupTheme (ThemeId, CityID, BeginDate) AS ( /* 这里对 GHP_GroupTheme 进行 GROUP BY,形成下面的结果*/ SELECT 15,1,'2014-08-20' UNION ALL SELECT 22,1,'2014-08-20' UNION ALL SELECT 2,1,'2014-08-22' UNION ALL SELECT 12,1,'2014-08-27' UNION ALL SELECT 27,1,'2014-08-29' UNION ALL SELECT 7,1,'2014-09-06' UNION ALL SELECT 14,2,'2014-08-20' UNION ALL SELECT 12,2,'2014-08-20' UNION ALL SELECT 31,2,'2014-08-21' UNION ALL SELECT 1,2,'2014-08-22' UNION ALL SELECT 11,2,'2014-08-27' UNION ALL SELECT 26,2,'2014-08-29' ), CityInfo (CityID, [Name]) AS ( -- 这个最好做成字典表,以应对添加城市的情况 SELECT 1,N'北京' UNION ALL SELECT 2,N'上海' ), CityMax (CityID, ThemeId) AS ( SELECT CityID, MAX(ThemeId) FROM GroupTheme WHERE BeginDate <= @今天 GROUP BY CityID ) SELECT g.ThemeId, c.Name, g.BeginDate, CASE WHEN g.BeginDate > @今天 THEN N'未上线' WHEN g.ThemeId = cm.ThemeId THEN N'已上线' ELSE N'已下线' END GroupThemeStatus FROM GroupTheme g JOIN CityInfo c ON c.CityID = g.CityID JOIN CityMax cm ON cm.CityID = g.CityID ORDER BY c.Name,g.BeginDate,g.ThemeId ThemeId Name BeginDate GroupThemeStatus ----------- ---- ---------- ---------------- 12 上海 2014-08-20 已下线 14 上海 2014-08-20 已下线 31 上海 2014-08-21 已上线 1 上海 2014-08-22 未上线 11 上海 2014-08-27 未上线 26 上海 2014-08-29 未上线 15 北京 2014-08-20 已下线 22 北京 2014-08-20 已上线 2 北京 2014-08-22 未上线 12 北京 2014-08-27 未上线 27 北京 2014-08-29 未上线 7 北京 2014-09-06 未上线
ThemeId,
CASE
WHEN CityId=2 THEN '上海'
WHEN CityId=1 THEN '北京'
WHEN CityId=32 THEN '广州'
WHEN CityId=30 THEN '深圳'
WHEN CityId=37 THEN '丽江'
ELSE ''
END CityName,
BeginDate,
CASE
WHEN BeginDate>GETDATE() THEN '未上线'
WHEN BeginDate=(select MAX(BeginDate) from GHP_GroupTheme g2 where BeginDate<=GETDATE() and g1.CityId=g2.CityId ) THEN '已上线'
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme g1 (NOLOCK)
ThemeId CityName BeginDate GroupThemeStatus (No column name)
15 北京 2014-08-20 00:00:00.000 已下线 31
22 北京 2014-08-20 00:00:00.000 已下线 31
2 北京 2014-08-22 00:00:00.000 未上线 31
12 北京 2014-08-27 00:00:00.000 未上线 31
27 北京 2014-08-29 00:00:00.000 未上线 31
7 北京 2014-09-06 00:00:00.000 未上线 31
这是查询出的结果片段,其中第第二条应该是“已上线”
15 北京 2014-08-20 00:00:00.000 已下线 31
22 北京 2014-08-20 00:00:00.000 已下线 31
2 北京 2014-08-22 00:00:00.000 未上线 31
12 北京 2014-08-27 00:00:00.000 未上线 31
27 北京 2014-08-29 00:00:00.000 未上线 31
7 北京 2014-09-06 00:00:00.000 未上线 31
SELECT
ThemeId,
CASE
WHEN CityId=2 THEN '上海'
WHEN CityId=1 THEN '北京'
WHEN CityId=32 THEN '广州'
WHEN CityId=30 THEN '深圳'
WHEN CityId=37 THEN '丽江'
ELSE ''
END CityName,
BeginDate,
CASE
WHEN BeginDate>GETDATE() THEN '未上线'
when themeld=(select MAX(themeld) from GHP_GroupTheme as g2 where g1.cityid=g2.cityid
and g2.begindate<=GETDATE())
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme(NOLOCK) as g1 试试
SELECT
ThemeId,
CASE
WHEN CityId=2 THEN '上海'
WHEN CityId=1 THEN '北京'
WHEN CityId=32 THEN '广州'
WHEN CityId=30 THEN '深圳'
WHEN CityId=37 THEN '丽江'
ELSE ''
END CityName,
BeginDate,
CASE
WHEN BeginDate>GETDATE() THEN '未上线'
WHEN not exists(select * from GHP_GroupTheme where cityid=t.cityid and themeID>t.themeID) and BeginDate <=GETDATE() THEN '已上线'
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme(NOLOCK) t
ThemeId,
CASE
WHEN CityId=2 THEN '上海'
WHEN CityId=1 THEN '北京'
WHEN CityId=32 THEN '广州'
WHEN CityId=30 THEN '深圳'
WHEN CityId=37 THEN '丽江'
ELSE ''
END CityName,
BeginDate,
CASE
WHEN BeginDate>GETDATE() THEN '未上线'
when themeld=(select MAX(themeld) from GHP_GroupTheme as g2 where g1.cityid=g2.cityid
and g2.begindate<=GETDATE()) then '已上线'
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme(NOLOCK) as g1
ThemeId,
CASE
WHEN CityId=2 THEN '上海'
WHEN CityId=1 THEN '北京'
WHEN CityId=32 THEN '广州'
WHEN CityId=30 THEN '深圳'
WHEN CityId=37 THEN '丽江'
ELSE ''
END CityName,
BeginDate,
CASE
WHEN BeginDate>GETDATE() THEN '未上线'
WHEN ThemeId=(select max(ThemeId) from GHP_GroupTheme as b where b.CityId=a.CityId ) THEN '已上线'
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme(NOLOCK) as a
DECLARE @今天 DateTime
SET @今天 = '2014-08-21' --发帖的日子
;
WITH GroupTheme (ThemeId, CityID, BeginDate)
AS (
/* 这里对 GHP_GroupTheme 进行 GROUP BY,形成下面的结果*/
SELECT 15,1,'2014-08-20' UNION ALL
SELECT 22,1,'2014-08-20' UNION ALL
SELECT 2,1,'2014-08-22' UNION ALL
SELECT 12,1,'2014-08-27' UNION ALL
SELECT 27,1,'2014-08-29' UNION ALL
SELECT 7,1,'2014-09-06' UNION ALL
SELECT 14,2,'2014-08-20' UNION ALL
SELECT 12,2,'2014-08-20' UNION ALL
SELECT 31,2,'2014-08-21' UNION ALL
SELECT 1,2,'2014-08-22' UNION ALL
SELECT 11,2,'2014-08-27' UNION ALL
SELECT 26,2,'2014-08-29'
),
CityInfo (CityID, [Name])
AS ( -- 这个最好做成字典表,以应对添加城市的情况
SELECT 1,N'北京' UNION ALL
SELECT 2,N'上海'
),
CityMax (CityID, ThemeId)
AS (
SELECT CityID, MAX(ThemeId)
FROM GroupTheme
WHERE BeginDate <= @今天
GROUP BY CityID
)
SELECT g.ThemeId,
c.Name,
g.BeginDate,
CASE WHEN g.BeginDate > @今天 THEN N'未上线'
WHEN g.ThemeId = cm.ThemeId THEN N'已上线'
ELSE N'已下线'
END GroupThemeStatus
FROM GroupTheme g
JOIN CityInfo c
ON c.CityID = g.CityID
JOIN CityMax cm
ON cm.CityID = g.CityID
ORDER BY
c.Name,g.BeginDate,g.ThemeId
ThemeId Name BeginDate GroupThemeStatus
----------- ---- ---------- ----------------
12 上海 2014-08-20 已下线
14 上海 2014-08-20 已下线
31 上海 2014-08-21 已上线
1 上海 2014-08-22 未上线
11 上海 2014-08-27 未上线
26 上海 2014-08-29 未上线
15 北京 2014-08-20 已下线
22 北京 2014-08-20 已上线
2 北京 2014-08-22 未上线
12 北京 2014-08-27 未上线
27 北京 2014-08-29 未上线
7 北京 2014-09-06 未上线