主页 / 问题 / 1369455
Accepted
Daniil Screpchenko
Asked:2022-06-08 06:08:22 +0000 UTC2022-06-08 06:08:22 +0000 UTC

选择商店 1 和 2 中的所有商品

  • 772

我有一张这样的桌子：

id | name   | shop | price |
 1 | Яблоко |  1   |   5   |
 2 | Яблоко |  2   |   15  |
 3 | Груша  |  1   |   8   |
 4 | Груша  |  2   |   12  |
 4 | Персик |  1   |   18  |

name- 名称
shop- 商店 ID
price- 价格

我需要进行 sql 查询以获取商店中的所有内容1和2.
在上面的示例中，它将是яблокоand груша，没有персика，因为它仅在商店中可用1。如何实施？

sql

2 个回答

  1. 1个选项：

    WITH t AS(
  SELECT 1 AS id, 'apple' AS name, 1 AS shop, 5 AS price
  UNION
  SELECT 2 AS id, 'apple' AS name, 2 AS shop, 15 AS price
  UNION
  SELECT 3 AS id, 'pear' AS name, 1 AS shop, 8 AS price
  UNION
  SELECT 4 AS id, 'pear' AS name, 2 AS shop, 12 AS price
  UNION
  SELECT 4 AS id, 'peach' AS name, 1 AS shop, 18 AS price
)
  SELECT t.name
    FROM t
   WHERE t.shop = 1
     AND t.name IN (SELECT name FROM t WHERE shop = 2)

    选项 2：

    WITH t as (
  SELECT 1 AS id, 'apple' AS name, 1 AS shop, 5 AS price
  UNION
  SELECT 2 AS id, 'apple' AS name, 2 AS shop, 15 AS price
  UNION
  SELECT 3 AS id, 'pear' AS name, 1 AS shop, 8 AS price
  UNION
  SELECT 4 AS id, 'pear' AS name, 2 AS shop, 12 AS price
  UNION
  SELECT 4 AS id, 'peach' AS name, 1 AS shop, 18 AS price
)
  SELECT t.name
    FROM t
   WHERE t.shop IN (1, 2)
   GROUP BY t.name
   HAVING COUNT(t.name) > 1

    选项之间有什么区别-这里已经是您自己的...

    • 0
  2. Best Answer

    来自评论的谷歌专业人士having，我提出了以下要求：

    SELECT * FROM `tovars` WHERE `name` IN (
    SELECT `name` FROM `tovars` GROUP BY `name` HAVING COUNT(`name`)=(
        SELECT COUNT(DISTINCT `shop`) FROM `tovars`
    )
)

    也许你可以把它做得更小，但我不知道怎么做，而且它工作得很好。
    在第一个SELECT中，计算商店的数量，在第二个中，获取所有商店中的所有商品，在第三个中，最后一个中，获取有关从第二个交付name的商品的所有信息。nameSELECT

    • -1

相关问题