SQL 按“喜欢”分组

发布于 2024-11-09 02:04:11 字数 375 浏览 1 评论 0原文

我有一个疑问，我在哪里生成每月的客户联系活动。我们有几个类别（电子邮件发出、电子邮件输入、电话拨入、电话拨出等），有 8 种不同的“类型”结果。我需要有两组 - 一组用于所有“电子邮件”，一组用于所有“电话”。目前，我有一个 WHERE TYPE LIKE '%Email%' 和 TYPE LIKE '%Call%'。但是，我无法按这两个“LIKE”语句进行分组。有谁知道我怎样才能最好地实现这一目标？

我将查询简化为以下示例：

SELECT     TYPE
FROM         dbo.HISTORY
WHERE     (TYPE LIKE '%email%') OR
                      (TYPE LIKE '%call%')

原文

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?

I simplified the query down to this for the example:

SELECT     TYPE
FROM         dbo.HISTORY
WHERE     (TYPE LIKE '%email%') OR
                      (TYPE LIKE '%call%')

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌逼全场 2024-11-16 02:04:11

这应该可行：

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

尽管如此，我的建议是有一个类型代码表，其中包含另一列，用于说明每种类型是否被视为电子邮件或电话。这样您就不会依赖于遵循特定格式的类型名称，而这种格式肯定会在以后被遗忘。然后您可以轻松地对此进行分组：

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

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

回复收藏 0 原文

断舍离 2024-11-16 02:04:11

选择
H型FROM
dbo.历史
内连接 dbo.History_Types HT ON
HT.history_type_code = H.history_type_code AND
HT.history_type_category IN ('电子邮件', '通话')
按 HT.history_type_category 分组

将过滤语句放在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

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?

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

回复收藏 0 原文

~没有更多了~