mysql 查询：同一列中匹配的几个条件

发布于 2024-12-02 08:30:09 字数 702 浏览 5 评论 0原文

我的 mysql 数据库中有一个名为“events”的表：

+-----+-----------+------------------------------+------------+
| ID  | CATEGORY  | NAME                         | TYPE       |
+-----+-----------+------------------------------+------------+
| 1   | 1         | Concert                      | music      |
| 2   | 2         | Basketball match             | indoors    |
| 3   | 1         | Theather play                | outdoors   |
| 4   | 1         | Concert                      | outdoors   |
+-----+-----------+------------------------------+------------+

我需要一个查询来计算类别 1 的事件以及哪种类型是音乐以及户外活动这意味着从上表中计数应该仅为 1：存在 3 个类别为 1 的事件但只有“音乐会”具有户外和音乐类型（ID 1 和 ID 4）。

该查询会是什么？可以吗？

原文

I have this table named "events" in my mysql database:

+-----+-----------+------------------------------+------------+
| ID  | CATEGORY  | NAME                         | TYPE       |
+-----+-----------+------------------------------+------------+
| 1   | 1         | Concert                      | music      |
| 2   | 2         | Basketball match             | indoors    |
| 3   | 1         | Theather play                | outdoors   |
| 4   | 1         | Concert                      | outdoors   |
+-----+-----------+------------------------------+------------+

I need a query to count the events with category 1 and which type is music and also outdoors
Meaning that from the table above the count should be only 1: there are three events with category 1
but only "Concert" has type outdoor and music (ID 1 and ID 4).

What would be that query? Can that be done?

分享到QQ

分享到微博

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

发布评论

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

弃爱 2024-12-09 08:30:09

试试这个：

SELECT count(DISTINCT e1.name)
FROM `events` AS e1
JOIN `events` AS e2 ON e1.name = e2.name
WHERE e1.category = 1 
    AND e2.category = 1 
    AND e1.type = 'music' 
    AND e2.type = 'outdoor'

或者一种更难理解的方法，但比前一种方法快得多：

SELECT count(*) FROM (
    SELECT `name`
    FROM `events`
    WHERE `category` = 1
    GROUP BY `name`
    HAVING SUM( `type` = 'music') * SUM( `type` = 'outdoor' ) >= 1
) AS notNeeded

Try this:

SELECT count(DISTINCT e1.name)
FROM `events` AS e1
JOIN `events` AS e2 ON e1.name = e2.name
WHERE e1.category = 1 
    AND e2.category = 1 
    AND e1.type = 'music' 
    AND e2.type = 'outdoor'

Or a harder to understand way, but way faster than the previous one:

SELECT count(*) FROM (
    SELECT `name`
    FROM `events`
    WHERE `category` = 1
    GROUP BY `name`
    HAVING SUM( `type` = 'music') * SUM( `type` = 'outdoor' ) >= 1
) AS notNeeded

回复收藏 0 原文

方圜几里 2024-12-09 08:30:09

对于两个标准，我会使用阿林的答案。您可以使用下面的方法来获得更多数量。

SELECT COUNT(*)
FROM   (SELECT `name`
        FROM   `events`
        WHERE  `category` = 1
               AND `type` IN ( 'outdoors', 'music' )
        GROUP  BY `name`
        HAVING COUNT(DISTINCT `type`) = 2) t

For 2 criteria I would use Alin's answer. An approach you can use for greater numbers is below.

SELECT COUNT(*)
FROM   (SELECT `name`
        FROM   `events`
        WHERE  `category` = 1
               AND `type` IN ( 'outdoors', 'music' )
        GROUP  BY `name`
        HAVING COUNT(DISTINCT `type`) = 2) t

回复收藏 0 原文

锦欢 2024-12-09 08:30:09

尝试这个查询

Select count(*), group_concat(TYPE SEPARATOR ',') as types 
from events where category = 1 
HAVING LOCATE('music', types) and  LOCATE('outdoors', types)

Try this query

Select count(*), group_concat(TYPE SEPARATOR ',') as types 
from events where category = 1 
HAVING LOCATE('music', types) and  LOCATE('outdoors', types)

回复收藏 0 原文

失而复得 2024-12-09 08:30:09

尝试：

SELECT * FROM `events` AS e1
LEFT JOIN `events` AS e2 USING (`name`)
WHERE e1.`category` = 1 AND e2.`category` = 1 AND e1.`type` = 'music' AND e2.`type` = 'outdoors'

try:

SELECT * FROM `events` AS e1
LEFT JOIN `events` AS e2 USING (`name`)
WHERE e1.`category` = 1 AND e2.`category` = 1 AND e1.`type` = 'music' AND e2.`type` = 'outdoors'

回复收藏 0 原文

回忆那么伤 2024-12-09 08:30:09

SELECT COUNT(*)
    FROM table
    WHERE category=1
    AND type='music' AND type IN (SELECT type
                                  FROM table
                                  WHERE type = 'outdoor')

一根线不断重置我的连接。与？我会尝试发布评论

SELECT COUNT(*)
    FROM table
    WHERE category=1
    AND type='music' AND type IN (SELECT type
                                  FROM table
                                  WHERE type = 'outdoor')

one line keeps resetting my connection. wth? i'll try posting as a comment

回复收藏 0 原文