获取不存在的行

Title might sound strange but I'll try to explain the problem as easy as possible. Let's start with example. I got a table with 2 columns - id, ip. Let's say I got 3 rows with id 2,3,5. Now, I need to get any row that isn't between id 1 AND 5, which is clearly 1 and 4. Currently I'm stuck with this query:

标题可能听起来很奇怪，但我将尽可能简单地解释这个问题。让我们开始的例子。我有一个2列的表，id, ip。假设有3行id为2 3 5。现在，我需要得到任何不在id 1和5之间的行，显然是1和4。目前我被这个问题困住了:

SELECT * 
FROM `votes` 
WHERE ip = "1.1.1.1."  
AND question_id BETWEEN 1 AND 5

3 个解决方案

#1

sounds bizarre but this is what many do.

听起来很奇怪，但很多人都是这么做的。

create a helper table. Use it for left joins

创建一个辅助表。将它用于左连接

create table amfn
(   -- All My Favorite Numbers  
    id int auto_increment primary key,
    theWhat char(1) null
)engine=MyIsam;   --  <----- somewhat important

insert amfn(theWhat) values (null),(null),(null),(null),(null),(null),(null),(null),(null),(null); -- 10
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;
insert amfn(theWhat) select theWhat from amfn;

select count(*),min(id),max(id) from amfn;
+----------+---------+---------+
| count(*) | min(id) | max(id) |
+----------+---------+---------+
|  1310720 |       1 | 1310720 |
+----------+---------+---------+
1 row in set (0.00 sec)

Your Schema:

create table votes
(   question_id int not null,
    ip varchar(20) not null
);
insert votes (question_id,ip) values (1,'xxxx'),(2,'1.1.1.1'),(3,'1.1.1.1'),(4,'1.6.1.1'),(5,'1.1.1.1');

The Query:

select a.id,v.question_id,v.ip
from amfn a
left join votes v
on v.question_id=a.id and v.ip='1.1.1.1'
where a.id between 1 and 5 and v.question_id is null;
+----+-------------+------+
| id | question_id | ip   |
+----+-------------+------+
|  1 |        NULL | NULL |
|  4 |        NULL | NULL |
+----+-------------+------+
2 rows in set (0.00 sec)     <------------- boy that is fast

Edit (to show Conrad Frix the time differences).

My above approach to create 5242880 rows, 23.5 seconds. Conrad's approach, 168.5 seconds. I will stick with my approach :>

我上面的方法创建了5242880行，23.5秒。康拉德的方法,168.5秒。我将坚持我的方法:>

#2

A really unusual request;

一个真的不同寻常的请求;

But you may get what you want by creating an auxiliary sequence of 1..5, then select all the ids from this sequence where there's no matching entry in the votes table.

但是你可以通过创建一个1的辅助序列来得到你想要的。然后从这个序列中选择所有的id，因为在投票表中没有匹配条目。

SELECT id 
FROM (
  SELECT 1 AS id UNION ALL 
  SELECT 2 UNION ALL 
  SELECT 3 UNION ALL 
  SELECT 4 UNION ALL 
  SELECT 5 UNION ALL 
) AS temp 
WHERE temp.id NOT IN (SELECT DISTINCT question_id FROM votes);

#3

Ok, I find the solution -

我找到了解

I made new column for each question_id and all ip fields I filled with NULL, after that I use this logic in SQL query:

我为每个question_id和所有用NULL填充的ip字段新建了一个列，然后在SQL查询中使用这个逻辑:

SELECT question_id
FROM  `votes` 
WHERE (ip =  'NULL' OR ip = ?)
GROUP BY question_id
HAVING COUNT(*) - COUNT(DISTINCT question_id) = 0
ORDER BY RAND()
LIMIT 1

And in result I get one random row that haven't been used yet, because it searches for duplicates, and if any duplicates are found they are removed from search. I hope I wrote this understandable

结果，我得到了一个尚未使用的随机行，因为它搜索重复的，如果找到重复的，它们就从搜索中删除。我希望我写这篇文章是可以理解的