Access Query-添加列以计数其他查询中的匹配值



我想向该查询添加另一列,该列统计来自另一个名为[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);

我为新专栏尝试了以下内容:

实验1:

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

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

请问我怎样才能做到这一点?

如果我理解正确,您需要在黑名单查询中添加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;

如果我正确理解你的问题,你想在qry_ip_blacklist表中用一个新列显示ip_address的编号来执行第一个查询。

如果是这样,并且tbl_ip_addresss没有更多的列,则此查询将执行此操作:

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地址的地址,也可以查询另一个没有的地址:

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)

最新更新