计算一个记录表以匹配另一个表的记录 [英] Counting one table of records for matching records of another table

问题描述

伙计们。这是我的表格如何工作：

我想能够计算与另一个表中的记录匹配的记录的视图数（视图是唯一的，包含用户的IP）例如我做一个 GET 请求和一个SQL查询将找到匹配和计数已收集的每个记录的数量，因此它将显示如下：

GET查询： stack

$ b b

显示：

  record_id |关键字| total_views 
 --------------------------------------------- ------- 
 2 |堆栈| 2 
 --------------------------------------------- ------- 
 5 |堆栈| 1 
  

从表中可以看到 2 查看 record_id 2 和 1 record_id 5 ，等等。你得到我的意思吗？我有麻烦知道如何这样做。

干杯。

解决方案

  SELECT r。*，COUNT（v.record_id）
 FROM records r 
 LEFT JOIN 
视图v 
 ON v.record_id = r.record_id 
 WHERE r。 keyword ='stack'
 GROUP BY 
 r.record_id 
  

创建以下索引：

  records（keyword，record_id）
 views（record_id）
  
 
 
 
。
 
Hey guys. This is my table of how it works:



I want to be able to count the number of views (the views are unique which contains user's IP), for records that matches record in another table, for example I do a GET request and a SQL query will find matches and count the number of views that have been collected for each record, so it'll display something like this:

GET query: stack

Display:
   record_id    |    keyword    |    total_views
----------------------------------------------------
       2        |     stack     |         2
----------------------------------------------------
       5        |     stack     |         1 
As you can see in the table there are 2 views for record_id 2 and 1 view for record_id 5, and so on. Do you get what I mean? I'm having trouble knowing how to do this. 

Cheers.
解决方案
SELECT  r.*, COUNT(v.record_id)
FROM    records r
LEFT JOIN
        views v
ON      v.record_id = r.record_id
WHERE   r.keyword = 'stack'
GROUP BY
        r.record_id
Create the following indexes:
records (keyword, record_id)
views (record_id)
for this to work fast.

                        
这篇关于计算一个记录表以匹配另一个表的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！