如何获取mysql中in子句中没有的数据

Question

我在'email'表上尝试了以下查询

SELECT email_id,count(*) as count FROM email 
where  email_id in ('[e-mail]','[e-mail]','[e-mail]') group by email_id

结果

email_id   count
[e-mail]     3
[e-mail]     2

但我想要像下面的序列一样的结果

email_id     count
[e-mail]       3
[e-mail]       0
[e-mail]       2

供您参考[e-mail]不存在于电子邮件表中

Answer 1

架构

create table users
(   userId int auto_increment primary key,
    userName varchar(100) not null,
    emailAddr varchar(200) not null
);
create table emails
(   id int auto_increment primary key,
    userId int not null
-- etc
);
insert users(userName,emailAddr) values ('Sally','[e-mail]'),('Henry','[e-mail]'),('JT','[e-mail]');
insert emails(userId) values (1),(1),(1),(3),(3);

查询

select u.userId,u.emailAddr,count(e.id) as theCount
from users u
left join emails e
on e.userId=u.userId
group by u.userId,u.emailAddr
+--------+-------------------+----------+
| userId | emailAddr         | theCount |
+--------+-------------------+----------+
|      1 | [e-mail] |        3 |
|      2 | [e-mail]           |        0 |
|      3 | [e-mail]     |        2 |
+--------+-------------------+----------+

可以说，Left join会收集缺失的电子邮件。

Answer 2

您可以使用：

SELECT e1.email_id, COUNT(e2.email_id) AS count 
FROM (SELECT '[e-mail]' AS email_id 
      UNION 
      SELECT '[e-mail]'
      UNION
      SELECT '[e-mail]'
 ) AS e1
LEFT JOIN email AS e2 ON e1.email_id = e2.email_id
GROUP BY e1.email_id

此处演示

Answer 3

可能是这样的：

SELECT t.email_id,count(e.email_id) as count 
FROM 
(
select '[e-mail]' as email_id union all
select '[e-mail]' union all
select '[e-mail]'
) t
left join email e on t.email_id = e.email_id
group by t.email_id

Answer 4

试试这个：

SELECT '[e-mail]' as email_id,count(1)  as `count` FROM email where email_id='[e-mail]'
union
SELECT '[e-mail]' as email_id,count(1) as `count`  FROM email where email_id='[e-mail]'
union
SELECT '[e-mail]' as email_id,count(1) as `count`  FROM email where email_id='[e-mail]'