MySQL查找同一个表中是否存在具有相同值的其他行

I'm relatively new to MySQL and need a little guidance. I have a table called events looking like this

我对MySQL比较新,需要一些指导。我有一个名为event的表,看起来像这样

---------------------------------------------------------------
| event_id | promoter_id | venue_id | event_date | attendance |
---------------------------------------------------------------
|    0     |      5      |     1    | 2013-05-30 |     400    |
|    1     |      2      |     6    | 2013-06-09 |     104    |
|    2     |      9      |     1    | 2013-05-30 |     300    |
|    3     |      1      |     3    | 2013-06-09 |     205    |
|    4     |      3      |     2    | 2013-06-01 |     175    |
|    5     |      5      |     2    | 2013-06-01 |     295    |
---------------------------------------------------------------

I am looking for the attendance for the events of promoters that occur at the same venue and date as other events. In this example all I want to get back from the query is the attendance of all events for promoter 5 that shared the same venue and date.

我正在寻找与其他活动在同一地点和日期发生的促销员活动的出席情况。在这个例子中,我想从查询中获得的是参与者5的所有活动的出席,这些活动共享相同的地点和日期。

--------------
| attendance |
--------------
|     400    |
|     295    |
--------------

This query makes logical sense to me but obviously I am missing something as it generates a syntax error....

这个查询对我来说具有逻辑意义,但显然我遗漏了一些东西,因为它会产生语法错误....

SELECT t1.attendance FROM events t1 WHERE promoter_id=5 
    JOIN (SELECT event_id, venue_id, event_date, attendance FROM events 
        WHERE promoter_id!=5) t2 
    ON t1.event_id=t2.event_id 
    WHERE t1.venue_id=t2.venue_id 
        AND t1.event_date=t2.event_date;

Any assistance would be greatly appreciated, I haven't gotten any where near what I need on my own research. I would prefer to do this without creating a view as I would have to delete the view after I get the results (as the table grows I feel this could be a performance hit especially as I intend to run this query for every promoter).

非常感谢任何帮助,我没有在我自己的研究中接近我需要的任何地方。我更愿意这样做而不创建视图,因为在得到结果后我必须删除视图(随着表格的增长,我觉得这可能会影响性能,特别是因为我打算为每个启动器运行此查询)。

Thanks

4 个解决方案

#1

SELECT attendance FROM events NATURAL JOIN (
  SELECT   venue_id, event_date
  FROM     events
  GROUP BY venue_id, event_date
  HAVING   COUNT(*) > 1
) t WHERE promoter_id = 5

See it on sqlfiddle.

在sqlfiddle上看到它。

#2

SELECT t1.attendance 
FROM   events t1 
WHERE  promoter_id = 5 
       AND EXISTS (SELECT event_id
                   FROM events 
                   WHERE promoter_id!=5
                         AND venue_id = t1.venue_id=venue_id 
                         AND event_date = t1.event_date)

#3

If I understand correctly, you're including the 400 and 295 because other promoters had events at the same venue on the same day. If that's the case, try this:

如果我理解正确的话,你会包括400和295,因为其他发起人在同一天在同一场地举办了活动。如果是这种情况,试试这个:

SELECT attendance
FROM events
WHERE promoter_id = 5 AND (venue_id, event_date) IN (
  SELECT venue_id, event_date
  FROM events
  WHERE promoter_id <> 5)

#4

I see there are plenty of answers, but to address your syntax error: You are performing a WHERE clause before your JOIN. The where clause is also ambiguous as it points to promoter_id in both table t1 and t2.

我看到有很多答案,但要解决语法错误:您在JOIN之前执行WHERE子句。 where子句也是不明确的,因为它指向表t1和t2中的promoter_id。

Try this:

/* Select sum of attendance columns */
SELECT (t1.attendance + t2.attendance) attendance
FROM events t1
/*Specify Join immediately after FROM clause*/
JOIN events t2
/* declare all join specific conditionals before initiating WHERE clause */
ON t1.venue_id = t2.venue_id
AND t1.event_date = t2.event_date
AND t2.promoter_id != 5
WHERE t1.promoter_id = 5;

The above query will give you:

以上查询将为您提供:

--------------
| attendance |
--------------
|     700    |
|     470    |
--------------

To have it broken down, you can break apart the sum of attendance fields.

为了解决这个问题,你可以分解出勤率的总和。

SELECT t1.attendance, t2.attendance, t1.venue_id
FROM events t1
/*Specify Join immediately after FROM clause*/
JOIN events t2
/* declare all join specific conditionals before initiating WHERE clause */
ON t1.venue_id = t2.venue_id
AND t1.event_date = t2.event_date
AND t2.promoter_id != 5
WHERE t1.promoter_id = 5;

The above query will give you:

以上查询将为您提供:

---------------------------------------
| attendance |  attendance | venue_id |
---------------------------------------
|     400    |       300   | 1        |
|     295    |       175   | 2        |
---------------------------------------