我有mail桌
+---------+-----------+--------------+
| m_id | dns_id | mail_name |
+---------+-----------+--------------+
| 1| 5 | myac1 |
| 2| 5 | myac2 |
+---------+-----------+--------------+-dns_id它是来自表dns的域名的id
m_id它是电子邮件id的关键字段
电子邮件的mail_name名称
表dns
+-----------+-----------+------------+
| dns_id | acc_id | dns|
+-----------+-----------+------------+
| 5 | 1 | test.com|
| 6 | 1 | test2.com|
+-----------+-----------+------------+我的帐户的acc_id id
我的域的dns名称
你可以看到,我有两个不同的域名为一个帐户。表dns中的字段dns_id与表mail中的dns_id链接。我有id为5、名为test.com的dns_id的邮件帐户。但是我没有使用dns_id 6的test2.com的邮件帐户。我需要简单地计算一下acc_id的dns_id计数:
select count(dns_id) from dns where acc_id = 1; 结果:
+------------------+
| count(dns_id) |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)但是,如果我想只计算那些在表mail中有邮件帐户的域。该怎么做呢?我不想计算域,在那里我没有邮件帐户。在这种情况下,我应该有:
+------------------+
| count(dns_id) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)因为我的域为test2.com、编号为6的dns_id在表mail中没有邮件帐户。
发布于 2017-05-19 18:56:18
试一试
SELECT COUNT(dns_id)
FROM dns
WHERE acc_id IN (
SELECT m_id FROM mail
)发布于 2017-05-19 23:06:18
我确信我的理解是正确的,但是你能试试下面的问题吗?SELECT COUNT(*) FROM dbo.DNS WHERE (acc_id=1 )和dns_id IN (SELECT d_id FROM dbo.Mail)
https://stackoverflow.com/questions/44068119
复制相似问题