获取同一表中的公共行 [英] Get Common Rows Within The Same Table

问题描述

我有一个搜索，但没有找到任何非常像我想要实现的东西。

基本上，我试着找到两个用户的投票习惯之间的相似性。

我有一个存储每个单独投票的表，其中存储：

  voteID 
 itemID（投票附加的项目）
 userID（投票的用户）
方向（用户是否投票post up或down）
  

我的目的是计算用户A和用户A之间的相似性， B，找出两件事：

1. 他们共同投票的次数。


2. 他们投票的次数（在这一点上，他们投票的次数）

（然后简单地以＃1的百分比计算＃2，

（即，如何查找两个用户的投票集之间的交集？）我如何充分地计算点＃1，而不是以非常低效的方式循环每个投票。）如果他们在不同的表中，INNER JOIN就足够了，我想象...但是显然不会在

解决方案

/ div>

像这样：

  SELECT COUNT（*）
 FROM votes v1 
 INNER JOIN votes v2 ON（v1.item_id = v2.item_id）
 WHERE v1.userID ='userA'
 AND v2.userUD ='userB'
  

I've had a bit of a search, but didn't find anything quite like what I'm trying to achieve.

Basically, I'm trying to find a similarity between two users' voting habits.

I have a table storing each individual vote made, which stores:

voteID
itemID     (the item the vote is attached to)
userID     (the user who voted)
direction  (whether the user voted the post up, or down)

I'm aiming to calculate the similarity between, say, users A and B, by finding out two things:

1. The number of votes they have in common. That is, the number of times they've both voted on the same post (the direction does not matter at this point).
2. The number of times they've voted in the same direction, on common votes.

(Then simply to calculate #2 as a percentage of #1, to achieve a crude similarity rating).

My question is, how do I find the intersection between the two users' sets of votes? (i.e. how do I calculate point #1 adequately, without looping over every vote in a highly inefficient way.) If they were in different tables, an INNER JOIN would suffice, I'd imagine... but that obviously won't work on the same table (or will it?).

Any ideas would be greatly appreciated.

解决方案

Something like this:

SELECT COUNT(*)
FROM votes v1
INNER JOIN votes v2 ON (v1.item_id = v2.item_id)
WHERE v1.userID = 'userA'
AND v2.userUD = 'userB'

这篇关于获取同一表中的公共行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！