在MySQL中查询以基于主键查找不同的状态 [英] Query in MySQL to Find Distinct Status Based on Primary Key

问题描述

我的问题是关于我要编写的MySQL查询。我已经写了一些伪代码来帮助说明我要写的查询：

My question is regarding a MySQL query that I am trying to write. I have written some psuedo-code to help illustrate what query I am trying to write:

SELECT *
FROM persons AS p
INNER JOIN person_info AS pi 
ON p.person_id = pi.person_id
WHERE status MAY INCLUDE lost, missing, or found
WHAT person_id has no instances of the found status

我想知道每个person_id（可以有多个状态），但没有状态为找到的任何实例。我不仅关心丢失和丢失的记录。我想找到基于每个唯一的不同person_id没有发现状态的独特情况。

I'd like to know for each person_id (which can have multiple statuses), which do not have any instance of the status "found." I'm not concerned with just the records of lost and missing. I want to find the unique cases where there is no "found" status based on each unique, distinct person_id.

推荐答案

据我所知@sgeddes。在编写它时，我意识到它只会使人眼前一亮。

This is as far as I took it @sgeddes. In writing it I realized it just makes peoples eyes glaze over.

SQL NOT IN（）危险

SQL NOT IN () danger

create table mStatus
(   id int auto_increment primary key,
    status varchar(10) not null
);
insert mStatus (status) values ('single'),('married'),('divorced'),('widow');

create table people
(   id int auto_increment primary key,
    fullName varchar(100) not null,
    status varchar(10)  null
);

Chunk1：

truncate table people;
insert people (fullName,status) values ('John Henry','single');
select * from mstatus where status not in (select status from people);

** 3行，如预期的那样**

** 3 rows, as expected **

Chunk2：

truncate table people;
insert people (fullName,status) values ('John Henry','single'),('Kim Billings',null);
select * from mstatus where status not in (select status from people);

没有行，是吗？

显然这是不正确的。它是由于SQL使用三值逻辑而产生的，
由NULL的存在驱动，NULL是表示缺失（或UNKNOWN）信息的非值。
使用NOT IN，Chunk2时，其翻译如下：

Obviously this is 'incorrect'. It arises from SQL's use of three-valued logic, driven by the existence of NULL, a non-value indicating missing (or UNKNOWN) information. With NOT IN, Chunk2 it is translated like this:

status NOT IN ('married', 'divorced', 'widowed', NULL)

这等效于：

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

表达式 status = NULL的计算结果为UNKNOWN，根据三值逻辑规则，
NOT UNKNOWN的计算结果也为UNKNOWN 。结果，所有行都被过滤掉，查询返回一个空集。

The expression "status=NULL" evaluates to UNKNOWN and, according to the rules of three-valued logic, NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

可能的解决方案包括：

select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null

或使用不存在

这篇关于在MySQL中查询以基于主键查找不同的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！