SQL从具有多个where子句的一张表中进行选择 [英] SQL Selecting From One Table With Multiple Where Clauses

问题描述

在高处和低处寻找这个问题的答案，但没有运气.我相信它不会像我想象的那么复杂.

Searching high and low for an answer to this one and having no luck. I'm sure it can't be as complicated as I think it is.

我有一张带有标题和代码的表格.这些代码是唯一的，可以有多个标题代码.我希望能够选择那些包含我列出的代码的标题.

I have one table with titles and codes. The codes are unique and can have more than one titlecode. I want to be able to select those titles that have codes I list.

 Titles                        Codes
 -----------------------------------
 Book1                         001
 Book2                         010
 Book2                         020
 Book2                         021
 Book3                         030
 Book3                         040

所以我希望能够返回代码为 020 和 021 的标题.或者我列出的任何内容.在这种情况下，它只会返回 Book2，因为该标题具有这两个代码.

So I want to be able to return Titles that have codes 020 and 021. Or whatever I list. In this case it would just return Book2 as that Title has those two codes.

我最初尝试过

 SELECT Titles FROM table WHERE Codes = 020 AND Codes = 021 

但是返回零结果，我可以理解为什么.没有一行包含多个代码条目

but that returned zero results and I could see why. No row contains more than one Codes entry

 SELECT Titles FROM table WHERE Codes = 020 OR Codes = 021 

返回两者之一的标题.所以我一直在尝试使用 GROUP BY 和一个子查询来尝试获取它，但没有运气.有人可以帮忙吗?提前致谢

returns Titles that are either. So I've been trying to use GROUP BY and also a subquery to try and get it but having no luck. Can anyone help please? Thanks in advance

推荐答案

为了获得您正在寻找的数据，您需要在您的表上使用自联接:

In order to get the data you're looking for you will need to use a self-join on your table:

SELECT * FROM table t1
JOIN table t2
ON t1.Title = t2.Title
WHERE t1.Codes = '020'
AND t2.Codes = '021'

这篇关于SQL从具有多个where子句的一张表中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！