在不同的条件下计算所有相同的列，得到最大的一列。
在不同的条件下计算所有相同的列，得到最大的一列。
提问于 2017-10-16 06:33:53
我有一个问题，如何在不同的条件下计算所有同一列。例如，我有一套地方，每个地方都有这样的时间。
+---------+----------------------+
|pTimeslot|pTimeslotPlace |
|09:30:00 |shifen waterfall park |
|12:30:00 |shifen waterfall park |
|09:30:00 |shifen water fall park|
|09:30:00 |Taipei Ryo Hotel |
|17:30:00 |Taipei Ryo Hotel |
|09:30:00 |shifen water fall park|
+---------+----------------------+我想计算特定时隙中的所有位置(早上、下午、晚上之类的)，我已经尝试了以下代码：
SELECT pTimeslotPlace,
SUM(pTimeslot >= '07:00:00' AND pTimeslot <= '11:59:00') Morning,
SUM(pTimeslot >= '12:00:00' AND pTimeslot <= '16:59:00') Afternoon,
SUM(pTimeslot >= '17:00:00' AND pTimeslot <= '18:59:00') Evening,
SUM(pTimeslot >= '19:00:00' AND pTimeslot <= '03:59:00') Night,
SUM(pTimeslot >= '04:00:00' AND pTimeslot <= '06:59:00') EarlyMorning
FROM planTime
WHERE pTimeslotPlace = 'shifen waterfall park';其结果是：
+---------------------+--------+---------+--------+-------+------------+
|pTimeslotPlace |Morning |Afternoon|Evening |Night |EarlyMorning|
|shifen waterfall park|3 |1 |0 |0 |0 |
+---------------------+--------+---------+--------+-------+------------+问题是：
- 如果没有一个地方，我怎么能在所有的“pTimeslotPlace”中得到这样的结果呢？
- 我怎样才能得到最多的早晨，下午，晚上，夜晚或EarlyMorning？在这个例子中，我只想得到晨曦，因为它是最大的。
谢谢
Stack Overflow用户
回答已采纳
发布于 2017-10-16 07:10:30
Select pTimeslotPlace,
Case Greatest(Morning, Afternoon, Evening, Night, EarlyMorning)
WHEN Morning then 'MORNING'
WHEN Afternoon then 'Afternoon'
WHEN Evening then 'Evening'
WHEN Night then 'Night'
WHEN EarlyMorning THEN 'EarlyMorning'
END As GreatestTime
From (
SELECT pTimeslotPlace,
SUM(pTimeslot >= '07:00:00' AND pTimeslot <= '11:59:00') Morning,
SUM(pTimeslot >= '12:00:00' AND pTimeslot <= '16:59:00') Afternoon,
SUM(pTimeslot >= '17:00:00' AND pTimeslot <= '18:59:00') Evening,
SUM(pTimeslot >= '19:00:00' AND pTimeslot <= '03:59:00') Night,
SUM(pTimeslot >= '04:00:00' AND pTimeslot <= '06:59:00') EarlyMorning
FROM planTime
group by pTimeslotPlace )页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/46764205
复制相关文章
相似问题
腾讯云开发者
