SQL 仅显示同时包含这两项的结果

发布于 2025-01-11 11:28:35 字数 376 浏览 0 评论 0原文

我有一个 SQL 表，其中包含人们吃的名字和水果。我只想结果显示同时吃了苹果和香蕉的人的名字。但是，如果我使用“Where Item='Banana' and item='Apple'，则数据不会显示任何内容。如果我使用：Where item in('banana','apple')，它会显示如表中所示的结果。

名称	物品
约翰	·苹果
大卫·	香蕉
约翰·	香蕉

原文

I have a SQL table with names and fruits that the persons ate.
I only want the result to show the Names of people who ate both an apple and a banana. But if i use "Where Item='Banana' and item='Apple', the data shows nothing.
If i use: Where item in('banana','apple'), it shows the result as shown in the table.

Name	Item
John	Apple
David	Banana
John	Banana

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

百合的盛世恋 2025-01-18 11:28:35

我们可以进行 GROUP BY，然后只选择从列表中吃过 2 个水果的人，即两个

创建表吃了（
人 VARCHAR (10),
水果 VARCHAR (10));

插入吃过的值
('约翰','苹果'),
('比尔','苹果'),
('比尔','梨'),
('大卫','香蕉'),
('大卫','香蕉'),
('约翰','香蕉');

选择人员
从吃过的
WHERE Fruit IN ('苹果','香蕉')
按人分组
有计数（不同的水果）=2；

<前>|人 |
| :----- |
|约翰 |

db<>fiddle 此处

We can do a GROUP BY and then select only the person who has eaten 2 fruits from the list, ie both of them

CREATE TABLE eaten(
person VARCHAR (10),
fruit VARCHAR (10));

INSERT INTO eaten VALUES
('John','apple'),
('Bill','apple'),
('Bill','pear'),
('David','banana'),
('David','banana'),
('John','banana');

SELECT person
FROM eaten
WHERE fruit IN ('apple','banana')
GROUP BY person
HAVING COUNT(DISTINCT fruit) =2;

| person |
| :----- |
| John   |

db<>fiddle here

回复收藏 0 原文

秋心╮凉 2025-01-18 11:28:35

WITH CTE_Apple AS 
(
    SELECT * FROM Table where Item = 'Apple'
)
,
CTE_Banana AS 
(
    SELECT * FROM Table WHERE Item = 'Banana'

)
SELECT 
           A.Name 
FROM 
           CTE_Apple AS A
    JOIN
           CTE_Banana AS B ON B.Name = A.Name

基本上创建数据的两个子集，并在数据相交的地方将它们连接在一起。还有很多其他方法可以做到这一点，但我发现 CTE（公用表表达式）是最优雅的

WITH CTE_Apple AS 
(
    SELECT * FROM Table where Item = 'Apple'
)
,
CTE_Banana AS 
(
    SELECT * FROM Table WHERE Item = 'Banana'

)
SELECT 
           A.Name 
FROM 
           CTE_Apple AS A
    JOIN
           CTE_Banana AS B ON B.Name = A.Name

Basically create two sub-sets of the data and join them together where the data intersects. There are plenty of other ways to do this but I find CTE (Common Table Expressions) the most elegant

回复收藏 0 原文

意犹 2025-01-18 11:28:35

使用子查询应该适用于任何 RDBMS：

select distinct Name 
from table_name
where Item = 'Apple'
and Name in 
(select Name from table_name where Item = 'Banana');

Using a subquery should work in any RDBMS:

select distinct Name 
from table_name
where Item = 'Apple'
and Name in 
(select Name from table_name where Item = 'Banana');

回复收藏 0 原文

东京女 2025-01-18 11:28:35

您可以将 DISTINCT 与 EXISTS 子句一起使用来获取所需的内容。

SELECT DISTINCT name
           FROM table1 a
          WHERE a.Item = 'Apple'
            AND EXISTS (SELECT *
                          FROM table1 b
                         WHERE b.Item = 'Banana'
                           AND b.Name = a.Name)

You can use DISTINCT with an EXISTS clause to get what you need.

SELECT DISTINCT name
           FROM table1 a
          WHERE a.Item = 'Apple'
            AND EXISTS (SELECT *
                          FROM table1 b
                         WHERE b.Item = 'Banana'
                           AND b.Name = a.Name)

回复收藏 0 原文

~没有更多了~