php - 如何计算我的 event_participant 表中的用户名？

Question

SELECT a.event_name AS event_name, a.event_date AS event_date,
       a.event_venue AS event_venue, a.event_tparticipant AS event_tparticipant,
       b.username AS username 
FROM (SELECT * FROM event WHERE status = 'valid') AS a
JOIN
(
    SELECT COUNT(DISTINCT username) AS username
    FROM event_participant
    GROUP BY event_name
) AS b
GROUP BY event_name desc

我的问题是当它计算用户名时，它不是我数据库中的确切数量。请帮助我

Answer 1

您的查询缺少 join 条件。试试下面的。

SELECT 
a.event_name as event_name, a.event_date as event_date, 
a.event_venue as event_venue, 
a.event_tparticipant as event_tparticipant, 
b.username as username 
FROM (select * from event WHERE status='valid') as a
JOIN (select event_name, COUNT(DISTINCT username) as username
      from event_participant
      GROUP BY event_name) as b
ON a.event_name = b.event_name --change this condition to be appropriate
GROUP BY event_name desc