SQL 组按“喜欢”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6101404/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Group by "Like"
提问by Jennifer Hart
I have a query where I generate our monthly customer contact activity. We have several categories (email out, email in, phone call in, phone call out, etc.) There are 8 distinct "type" results. I need to have two groups-one for all "email" and one for all "phone". Currently, I have a WHERE TYPE LIKE '%Email%'and TYPE LIKE '%Call%'. However, I am not able to group by these two "LIKE" statements. Does anyone know how I can best achieve this?
我有一个查询，用于生成我们每月的客户联系活动。我们有几个类别（电子邮件输出、电子邮件输入、电话拨入、电话拨出等）。有 8 种不同的“类型”结果。我需要有两组 - 一组用于所有“电子邮件”，一组用于所有“电话”。目前，我有一个 WHERE TYPE LIKE '%Email%' 和 TYPE LIKE '%Call%'。但是，我无法按这两个“LIKE”语句进行分组。有谁知道我如何才能最好地实现这一目标？
I simplified the query down to this for the example:
对于示例，我将查询简化为：
SELECT TYPE
FROM dbo.HISTORY
WHERE (TYPE LIKE '%email%') OR
(TYPE LIKE '%call%')
回答by Tom H
This should work:
这应该有效：
SELECT
TYPE
FROM
dbo.HISTORY
WHERE
(TYPE LIKE '%email%') OR (TYPE LIKE '%call%')
GROUP BY
CASE
WHEN type LIKE '%email%' THEN 'email'
WHEN type LIKE '%call%' THEN 'call'
ELSE NULL
END
Although, my advice would be to have a type code table with another column that tells whether each type is considered an email or call. Then you're not reliant on the type name following a specific format which is sure to be forgotten down the road. You can then easily group on that:
虽然，我的建议是有一个带有另一列的类型代码表，用于说明每种类型是电子邮件还是电话。那么您就不会依赖于遵循特定格式的类型名称，这种格式肯定会被遗忘。然后，您可以轻松地对其进行分组：
SELECT
H.type
FROM
dbo.History
INNER JOIN dbo.History_Types HT ON
HT.history_type_code = H.history_type_code AND
HT.history_type_category IN ('Email', 'Call')
GROUP BY
HT.history_type_category
回答by test
SELECT H.type FROM dbo.History INNER JOIN dbo.History_Types HT ON HT.history_type_code = H.history_type_code AND HT.history_type_category IN ('Email', 'Call')GROUP BY HT.history_type_category
SELECT H.type FROM dbo.History INNER JOIN dbo.History_Types HT ON HT.history_type_code = H.history_type_code AND HT.history_type_category IN ('Email', 'Call')GROUP BY HT.history_type_category
Wouldn't it be better to put the filter statement into the where clause?
将过滤语句放入 where 子句中不是更好吗？
SELECT
H.type FROM
dbo.History
INNER JOIN dbo.History_Types HT ON
HT.history_type_code = H.history_type_code
WHERE **HT.history_type_category IN ('Email', 'Call')**
GROUP BY HT.history_type_category
