Access查询-添加列以计算另一个查询中的匹配值

Question

我想在此查询中添加另一个列，该列统计来自另一个名为qry_ip_blacklist的查询的匹配ip_address值的数量

SELECT tbl_ip_addresses.ip_address, tbl_ip_addresses.selected
FROM tbl_ip_addresses
WHERE (tbl_ip_addresses.selected=True);

我在新专栏中尝试了以下方法：

Expr1：

(SELECT COUNT(*) FROM qry_ip_blacklist WHERE ip_address = ip_address)

但这似乎显示了qry_ip_blacklist中的记录总数

我怎样才能做到这一点呢？

Answer 1

如果我正确理解了您的问题，您希望使用一个新列执行第一个查询，该列显示qry_ip_blacklist表中的ip_address的数量。

SELECT tbl_ip_addresses.ip_address, tbl_ip_addresses.selected, COUNT(qry_ip_blacklist.ip_address )
FROM tbl_ip_addresses
LEFT JOIN qry_ip_blacklist 
ON qry_ip_blacklist .ip_address = tbl_ip_addresses.ip_address
WHERE (tbl_ip_addresses.selected=True)
GROUP BY tbl_ip_addresses.ip_address, tbl_ip_addresses.selected

如果这个查询仍然不起作用，你可以对有黑名单ip地址的地址进行查询，对没有黑名单ip地址的地址进行另一次查询：

SELECT tbl_ip_addresses.ip_address, tbl_ip_addresses.selected, COUNT(qry_ip_blacklist.ip_address)
FROM tbl_ip_addresses
INNER JOIN qry_ip_blacklist ON qry_ip_blacklist .ip_address = tbl_ip_addresses.ip_address
WHERE (tbl_ip_addresses.selected=True)
GROUP BY tbl_ip_addresses.ip_address, tbl_ip_addresses.selected
UNION
SELECT tbl_ip_addresses.ip_address, tbl_ip_addresses.selected, 0
FROM tbl_ip_addresses
WHERE (tbl_ip_addresses.selected=True) AND tbl_ip_addresses.ip_address NOT INT (SELECT ip_addresses FROM qry_ip_blacklist)

Answer 2

如果我理解正确的话，您需要在黑名单查询中添加GROUP BY子句：

SELECT 
 tbl_ip_addresses.ip_address
,tbl_ip_addresses.selected
,BlackList.Count

FROM tbl_ip_addresses 

LEFT JOIN (SELECT COUNT(qry_ip_blacklist.ip_address) AS Count, qry_ip_blacklist.ip_address
FROM qry_ip_blacklist
GROUP BY qry_ip_blacklist.ip_address) AS BlackList 
ON tbl_ip_addresses.ip_address = BlackList.ip_address

WHERE tbl_ip_addresses.selected = True;