34,587
社区成员
发帖
与我相关
我的任务
分享
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 MAX(BeginDate) <=GETDATE() THEN '已上线'
ELSE '已下线'
END GroupThemeStatus
FROM
GHP_GroupTheme(NOLOCK)
GROUP BY
CityId,ThemeId,BeginDate
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 未上线
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 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
这是查询出的结果片段,其中第第二条应该是“已上线”
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)