复杂的sql语句到不同的行 [英] complex sql statement to different rows

问题描述

我有一个包含 4 个字段的简单表格:

I have a simple table which has 4 fields:

indexID
UserID
text_1
IsFinal 

此表中每个UserID 可能有多个值，IsFinal 字段只能有一个值 1 或 0.

This table may have multiple values for each UserID, and IsFinal field can have only a single value 1 or 0.

我想要做的是创建一个选择语句，如果 IsFinal 仅等于 0，它将返回用户 ID.问题可能是同一个用户 ID 有多个记录，有些记录具有 IsFinal 等于 0，只有 1，IsFinal 等于 1.

What I'm trying to do is to make a select statement which will return the user IDs if IsFinal only equal 0. The problem there may be multiple records for the same userID, some having IsFinal equal to 0 and only 1 with IsFinal equal to 1.

我的问题是:对于每个 UserID，如果它有 Isfinal = 1 的记录，我想忽略所有具有相同 UserID 的记录，否则我想返回它的记录.不知道只用SQL语句能不能搞定.

My problem here is this: for every UserID, if it has a record with Isfinal = 1, I want to ignore all records with the same UserID, otherwise I want to return its records. I don't know if that can be done by SQL statement only or not.

推荐答案

好像你想要一个反加入，即你首先需要确定哪些用户 ID 具有 IsFinal = 1，然后使用该结果集返回该列表中不的所有用户 ID.

Seems like you want an anti-join, i.e. you first need to establish which user IDs have IsFinal = 1, then use that result set to return all user IDs not in that list.

有多种实现反加入的方法.

There are various ways to implement an anti-join.

1. 不在:

SELECT *
FROM atable
WHERE UserID NOT IN (
  SELECT UserID
  FROM atable
  WHERE IsFinal = 1
);

不存在:

SELECT *
FROM atable t1
WHERE NOT EXISTS (
  SELECT *
  FROM atable t2
  WHERE t1.UserID = t2.UserID
    AND t2.IsFinal = 1
);

LEFT JOIN + WHERE IS NULL:

a)

SELECT *
FROM atable t1
  LEFT JOIN (
    SELECT *
    FROM atable
    WHERE IsFinal = 1
  ) t2 ON t1.UserID = t2.UserID
WHERE t2.UserID IS NULL;

b)

SELECT *
FROM atable t1
  LEFT JOIN atable t2
    ON t1.UserID = t2.UserID AND t2.IsFinal = 1
WHERE t2.UserID IS NULL;

它们在您的数据库中的效率可能是一样的，但测试它们中的每一个以至少避免最终的性能比其他的差，这仍然是一个好主意.

It may so happen that they will be equally efficient in your database, but it still may be a good idea to test each of them to at least avoid ending up with one that performs worse than the others.

这篇关于复杂的sql语句到不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！