使用第一个查询的结果作为第二个查询的条件 [英] Use results from first query as criteria for second query

问题描述

我有以下 SQL 表，您也可以在 SQL fiddle 此处:

I have the following SQL table which you can also find in the SQL fiddle here:

CREATE TABLE Products (
    Supplier TEXT,
    Product TEXT,
    Quantity VARCHAR(255)
);

INSERT INTO Products
(Supplier, Product, Quantity)
VALUES 

("Supplier_01", "Product A", "500"),
("Supplier_05", "Product A", "300"),
("Supplier_09", "Product A", "250"),

("Supplier_05", "Product B", "900"),
("Supplier_06", "Product B", "270"),
("Supplier_02", "Product B", "850"),
("Supplier_05", "Product B", "950"),
("Supplier_10", "Product B", "630");

我目前使用以下查询:

SELECT Supplier, Product, Quantity
FROM Products
WHERE Product = "Product B";

<小时>

但是，现在我想使用上面查询的部分结果作为第二个查询的 WHERE 条件.
基本上，我希望 Product B 存在的所有 Suppliers 都用于 Product A 的 WHERE 标准代码>.
像这样:

---

However, now I want to use part of the results from the query above as WHERE criteria for a second query.
Basically, I want that all Suppliers which exist for Product B are used for the WHERE criteria for Product A.
Something like this:

SELECT Supplier, Product, Quantity
FROM Products
WHERE Product = "Product A"
AND Supplier EXISTS IN
   (SELECT Supplier
    FROM Products
    WHERE Product = "Product B") table_01;

最终想要的结果应该是这样的:

In the end the desired result should look like this:

Supplier             Product          Quantity
Supplier_05          Product A         300
Supplier_02          Product B         850
Supplier_05          Product B         900
Supplier_06          Product B         270
Supplier_10          Product B         630

我需要对代码进行哪些更改才能使其正常工作?

What do I need to change in my code to make it work?

推荐答案

你就快到了:

SELECT Supplier, Product, Quantity
FROM Products
WHERE Supplier IN
   (SELECT Supplier
    FROM Products
    WHERE Product = "Product B")
GROUP BY 1,2
ORDER BY 2,1;

这篇关于使用第一个查询的结果作为第二个查询的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！