SQL一对多返回&按搜索条件分组 [英] SQL One-To-Many return & group by search condition

问题描述

你好再次喜欢的溢出者，我希望我们有一些可供挑选的大脑;一如既往，非常感谢！ ：）

我有一个Events表，可以分配多个类别（用作标签）：

然后我有类别（存储每个标签）和Events_Categories（链接表）

  **事件** 
 | id | event_name | 
 + ------------ + 
 + 1 |篮球
 + 2 |儿童慈善舞会
 + 3 |保龄球慈善活动
 
 
 ** Event_Categories **（商店标签/分类）
 | id |名称| 
 + ------------ + 
 + 1 |体育
 + 2 |慈善
 + 3 | other_tag 
 
 
 ** Events_Categories **（链接表）
 | event_id | event_category_id | 
 + ------------------------------- + 
 + 1 | 1 
 + 2 | 2 
 + 3 | 1 
 + 3 | 2 
  

以上摘要

 篮球=>体育
儿童慈善舞会=>慈善
保龄慈善=>慈善&运动
  

预期结果

我希望能够搜索'Sport'标签，它会返回所有包含该标签的事件。但是，它需要返回（和分组）所有与该特定事件相关的标签。

搜索'Sport'标签的IE会返回：

  **活动** 
 | id | event_name |标签| 
 + --------------------------------------------- + 
 + 1 |篮球|运动
 + 3 |保龄慈善活动|运动，慈善机构
  

SQL返回没有WHERE条件的事件示例：
以下SQL返回所有事件并对类别（或标记）进行分组。这应该给你一个我的数据库结构的想法，可能有助于解决方案？

  SELECT e。*，group_concat（c。 name）as tags 
 FROM`events` e 
 JOIN`events_categories` ec 
 ON e.`id` = ec.`event_id` 
 JOIN`event_categories` c 
 ON ec.`event_category_id` = c.`id` 
 GROUP BY e.```; 
  

附加问题（对于额外的布朗克点）

你能相信吗，他想要更多帮助！ ：）

另外，在链接表中，当事件链接到一个活动时，实际上还有另一个字段：

  ** Events_Categories **（链接表）
 | event_id | event_category_id | activity_id | 
 + --------------------------------------------- + 
 + 1 | 1 | 1 
 + 2 | 2 | 
 + 3 | 1 | 2 
 + 3 | 2 | 2 
  

我将如何搜索一个事件，其活动ID为[x]返回事件详细信息，再次将所有标签分组：

链接到Activity_ID为2的事件

  | id | event_name |标签| activity_id | 
 + --------------------------------------------- --------------- + 
 + 3 |保龄慈善活动|体育，慈善| 2 
  

非常感谢！

解决方案

这将回答你的问题的第一部分。为了获取事件中包含的所有类别的列表，您可以使用 EXISTS 来检查事件是否包含至少一部分搜索事件名称。

  SELECT a.ID，
 a.Event_name，
 GROUP_CONCAT（c.Name）
 FROM事件a 
 INNER JOIN Events_Categories b 
 ON a.ID = b.event_ID 
 INNER JOIN Event_Categories c 
 ON b.event_category_id = c.ID 
 WHERE EXISTS 
 （
 SELECT 1 
 FROM Events_Categories d 
 INNER JOIN Event_Categories e 
 ON d.event_category_id = e.id 
 WHERE e.Name ='Sport'AND 
 d.event_id = a.id 
）
 GROUP BY a.ID，
 a.Event_name 
  

• SQLFiddle Demo

UPDATE 1

  SELECT a.ID，
 a。 Event_name，
 GROUP_CONCAT（c.Name）CategoryList，
 MAX（activity_id）activity_id 
 FROM事件a 
 INNER JOIN Events_Categories b 
 ON a.ID = b.event_ID 
 INNER JOIN Event_Categories c 
 ON b.event_category_id = c.ID 
 GROUP BY a.ID，a.Event_name 
 HAVING MAX（activity_id）= 2 
  





• SQLFiddle演示

Hello again beloved overflowers, I hope we have some brains available for the picking; as always it's greatly appreciated! :)

I have an Events table, which can be assigned multiple categories (used as tags):

I then have the Categories (stores each tag) and Events_Categories (linking table)

**Events**
| id  | event_name |
+------------+
+ 1   | Basketball
+ 2   | Children Charity Ball
+ 3   | Bowling Charity Event


**Event_Categories** (Stores Tags / Categories)
| id  | name |
+------------+
+ 1   | sport
+ 2   | charity
+ 3   | other_tag


**Events_Categories** (Linking Table)
| event_id  | event_category_id |
+-------------------------------+
+    1      |       1   
+    2      |       2   
+    3      |       1   
+    3      |       2   

Summary of above

Basketball            => Sports 
Children Charity Ball => Charity 
Bowling Charity       => Charity & Sports 

Desired Result

I want to be able to search for 'Sport' tag, which would return all events which have that tag. BUT, it needs to return (and group) all the tags associated with that particular event.

IE searching for 'Sport' tag would return:

**Events**
| id  |       event_name      |  tags         |
+---------------------------------------------+
+ 1   | Basketball            | sport
+ 3   | Bowling Charity Event | sport, charity

SQL Example returning Events without WHERE conditions: The following SQL, returns all events and groups the categories (or tags). This should give you an idea of my DB structure, possibly help with the solution?

SELECT e.*, group_concat(c.name) as tags
FROM `events` e 
JOIN `events_categories` ec
    ON e.`id` = ec.`event_id` 
JOIN `event_categories` c
     ON ec.`event_category_id` = c.`id`
GROUP BY e.`id`;

Additional Question (for extra brownie points)

Can you believe it, HE WANTS MORE HELP!! :)

Also, in the linking table, there is actually another field for when the events link to an Activity:

**Events_Categories** (Linking Table)
| event_id  | event_category_id | activity_id |
+---------------------------------------------+
+    1      |       1           |     1
+    2      |       2           |
+    3      |       1           |     2
+    3      |       2           |     2

How would I search for an event, with the Activity ID of [x] to return the event details, again with all the tags grouped:

Events which link to Activity_ID of 2 associated

| id  |       event_name      |  tags          | activity_id |
+------------------------------------------------------------+
+ 3   | Bowling Charity Event | sport, charity |     2

Thanks so much, once again!

解决方案

This will answer the first part of your question. In order to get the list of all categories contain in an event, you can use EXISTS to check if the event contains atleast a portion of your search event name.

SELECT  a.ID,
        a.Event_name,
        GROUP_CONCAT(c.Name)
FROM    Events a
        INNER JOIN Events_Categories b
            ON a.ID = b.event_ID
        INNER JOIN Event_Categories c
            ON b.event_category_id = c.ID
WHERE   EXISTS
        (
            SELECT  1
            FROM    Events_Categories d
                    INNER JOIN Event_Categories e
                        ON d.event_category_id = e.id 
            WHERE   e.Name = 'Sport' AND
                    d.event_id = a.id
        )
GROUP   BY  a.ID,
            a.Event_name

• SQLFiddle Demo

UPDATE 1

SELECT  a.ID,
        a.Event_name,
        GROUP_CONCAT(c.Name) CategoryList,
        MAX(activity_id) activity_id
FROM    Events a
        INNER JOIN Events_Categories b
            ON a.ID = b.event_ID
        INNER JOIN Event_Categories c
            ON b.event_category_id = c.ID
GROUP   BY  a.ID, a.Event_name
HAVING  MAX(activity_id) = 2

• SQLFiddle Demo

这篇关于SQL一对多返回&按搜索条件分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！