如何使用mysql根据同一表的其他两列填充一列？
如何使用mysql根据同一表的其他两列填充一列？
提问于 2019-03-27 00:52:25
我的数据如下(表名为botdata)
现在，我需要将此数据转换为Chatsession ID作为行，将Metrics作为列。必须有一个新的列Met014，只有当Met003和Met004为1时，它才应该显示1。下面是我使用的代码和我得到的结果。其他一切都很好，但是如何正确地填充Met014呢？
SELECT ChatSessionID,
COUNT(IF(Metrics = "Met001", Metrics, NULL)) AS Met001,
COUNT(IF(Metrics = "Met002", Metrics, NULL)) AS Met002,
COUNT(IF(Metrics = "Met003", Metrics, NULL)) AS Met003,
COUNT(IF(Metrics = "Met004", Metrics, NULL)) AS Met004,
COUNT(IF(Metrics = "Met005", Metrics, NULL)) AS Met005,
COUNT(IF(Metrics = "Met006", Metrics, NULL)) AS Met006,
COUNT(IF(Metrics = "Met007", Metrics, NULL)) AS Met007,
COUNT(IF(Metrics = "Met008", Metrics, NULL)) AS Met008,
COUNT(IF(Metrics = "Met009", Metrics, NULL)) AS Met009,
COUNT(IF(Metrics = "Met010", Metrics, NULL)) AS Met010,
COUNT(IF(Metrics = "Met011", Metrics, NULL)) AS Met011,
COUNT(IF(Metrics = "Met012", Metrics, NULL)) AS Met012,
COUNT(CASE WHEN Metrics="Met003" And Metrics="Met004" THEN Metrics ELSE NULL END) AS Met014
FROM
botdata b
WHERE
b.Metrics BETWEEN "Met001" AND "Met014"
GROUP BY
ChatSessionID;
回答 2
Stack Overflow用户
发布于 2019-03-27 00:58:23
我会用SUM()来表达这个问题。我认为你可以使用LEAST()做任何你想做的事情
SELECT ChatSessionID,
SUM(Metrics = 'Met001') AS Met001,
SUM(Metrics = 'Met002') AS Met002,
. . . . . . . . . . . . . . . . .,
SUM(Metrics = 'Met012') AS Met012,
LEAST(SUM(Metrics = 'Met003'), SUM(Metrics = 'Met004')) AS Met014
FROM botdata b
GROUP BY ChatSessionID;Stack Overflow用户
发布于 2019-03-27 02:54:53
如果您的列Met001到Met013只能取值1、0或NULL，请尝试如下所示：
SELECT
T.*, (Met003 + Met004) = 2 AS Met014
FROM
(SELECT
ChatSessionID,
Metrics = 'Met001' AS Met001,
Metrics = 'Met002' AS Met002,
Metrics = 'Met003' AS Met003,
Metrics = 'Met004' AS Met004,
Metrics = 'Met005' AS Met005,
Metrics = 'Met006' AS Met006,
Metrics = 'Met007' AS Met007,
Metrics = 'Met008' AS Met008,
Metrics = 'Met009' AS Met009,
Metrics = 'Met010' AS Met010,
Metrics = 'Met011' AS Met011,
Metrics = 'Met012' AS Met012,
Metrics = 'Met013' AS Met013
FROM
botdata b
WHERE
b.Metrics BETWEEN 'Met001' AND 'Met014'
GROUP BY ChatSessionID) AS T;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55362402
复制相关文章
相似问题