用于检查现有记录的 sql 查询

Question

我有以下格式的数据：

 HashMap<PageID, Set<SubscriberIDS>>

我需要检查的是MySQL表中已经不存在每个表中的数量。表具有 和 列。SubscriberIDSPageIDsPageIDSubscriberID

这就是我到目前为止所拥有的：

String NEW_SUBSCRIBER_COUNT = "SELECT ? - COUNT(*) as new_subscribers from FB_SUBSCRIPTIONS WHERE PAGEID=? AND SUBSCRIBERID IN (?)";

第一个参数是，第二个参数是第三个参数numberOFSubscriberIDsPageIdSubscriberIds

但这需要为每个页面Id打一个。我如何修改它以使用单个查询为每个提供我。number of new subscribersPageID

Answer 1

是否有任何特定需要在一个查询中执行此操作？因为虽然可以，但实际上使用原始解决方案并调用每个页面 ID 的查询可能更具可读性。无论如何，你想要的东西不能在一行中完成，所以你需要期望在给定的点循环。// Build dynamic queryStringBuilder whereClause = new StringBuilder();Iterator<PageID> it = yourMap.keySet().iterator();while(it.hasNext()){&nbsp; PageID key = it.next();&nbsp; Set<SubscriberIDS> value = yourMap.get(key);&nbsp; // You need to fill the 'IN' clause with multiple parameters, one for each subscriber id&nbsp; StringBuilder inClause = new StringBuilder();&nbsp; for(SubscriberIDS subId : value){&nbsp; &nbsp; if(inClause.length > 0){&nbsp; &nbsp; &nbsp; inClause.append(", ");&nbsp; &nbsp; }&nbsp; &nbsp; inClause.append("?");&nbsp; &nbsp; preparedStatement.setInt(paramIndex++, subId.getId());&nbsp; }&nbsp; // For each page id we append a new 'OR' to our query&nbsp; if(whereClause.lenght > 0){&nbsp; &nbsp; whereClause.append(" OR ");&nbsp; }&nbsp; whereClause.append("(PAGEID=? AND SUBSCRIBERID IN (").append(inClause.toString()).append("))");}String query = "SELECT PAGEID, COUNT(SUBSCRIBERID) AS SUBSCRIBERS FROM FB_SUBSCRIPTIONS WHERE " + whereClause.toString() + " GROUP BY PAGEID";// Create prepared statement and set parametersPreparedStatement preparedStatement = connection.prepareStatement(query);int paramIndex = 0;it = yourMap.keySet().iterator();while(it.hasNext()){&nbsp; PageID key = it.next();&nbsp; Set<SubscriberIDS> value = yourMap.get(key);&nbsp; preparedStatement.setInt(paramIndex++, key.getId());&nbsp; for(SubscriberIDS subId : value){&nbsp; &nbsp; preparedStatement.setInt(paramIndex++, subId.getId());&nbsp; }}// Execute query, loop over result and calculate new subscriptionsResultSet rs = preparedStatement.executeQuery();while(rs.next()){&nbsp; int pageId = rs.getInt("PAGEID");&nbsp; int newSubscriptions = yourMap.get(pageId).size() - rs.getInt("SUBSCRIBERS");&nbsp; System.out.println(pageId + ", " + newSubscriptions);}在地图中给定以下数据：PAGEID&nbsp; SUBSCRIBERIDS1&nbsp; &nbsp;1,3,4,5,92&nbsp; &nbsp;3,4,5,6,8,93&nbsp; &nbsp;2,5,6以及数据库中的以下数据：PAGEID&nbsp; SUBSCRIBERIDS1&nbsp; &nbsp;3,4,10,112&nbsp; &nbsp;1,2,5,73&nbsp; &nbsp;1,2,5,6,7,8,9这应该给出以下输出：1,32,63,0我实际上还没有运行代码，所以它可能需要一些调整，但它给了你一般的想法......