mysql - 如何在 MySQL 2 中连接多个表？

Question

我想看一个公民的全名

reportBasket,reportNoBasket 表中的 notifyEmployee ='1'

AND reportNoBasket.groupEmail 不为空

并且计算来自 reportBasket 的电子邮件，citizenEmail reportNOBasket 在一个计数器中

表格:

citizen(email, firstname,lastname)

reportBasket(email,notifyEmployee)

reportNoBasket(citizenEmail, notifyEmployee , groupEmail)

之前 我代表我的查询以这种方式显示他们的电子邮件，它运行良好:

SELECT email,count(email) as email_count 
FROM (
    SELECT email
    FROM reportBasket
    WHERE notifyEmployee='1'
    UNION ALL 
    SELECT citizenEmail
    FROM reportNoBasket
    WHERE notifyEmployee='1'
) as T
GROUP BY email
ORDER BY email ASC 

但现在我想显示公民的全名而不是电子邮件地址 concat(citizen.firstname,' 'citizen.lastname)

如何加入？

Answer 1

尝试这样的事情:

SELECT concat(firstname, ' ', lastname), count(*) as citizen_count 
FROM (
    SELECT c.firstname, c.lastname
    FROM reportBasket rb
    JOIN citizen c
        ON rb.email = c.email
    WHERE notifyEmployee='1'
    UNION ALL 
    SELECT c.firstname, c.lastname
    FROM reportNoBasket rnb
    JOIN citizen c
        ON rnb.citizenEmail = c.email
    WHERE notifyEmployee='1'
) as T
GROUP BY firstname, lastname
ORDER BY firstname, lastname ASC