mysql - 从多个表中选择列时，如何从同一列中的 2 个条件返回数据？

Question

我有 2 张 table ；T.1- Item_master 包含列:upc_ean、description、store_pos_department、item_id。T.2- Alternate_categories 包含列:category、item_id。

我正在尝试创建一个语句，为我提供 t.1 中的 upc_ean 和描述以及 t.2 中的类别，其中 t.1 中的 upc_ean 的类别号为 11 以及分配给它的任何其他编号t.2 中的类别列我只想要 *** 行指示的结果。

upc_ean         description                        category
013130006989    CRM OF RICE 14Z                     7
016000263444    GM CINN CHEX                        7
016000264182    GM RICE CHEX PP                     7
016000274730    GM CHEX CHOCOLATE                   7
**016000275270  GM HNY NUT CHEERIOS 12.25Z          11**
**016000275270  GM HNY NUT CHEERIOS 12.25Z          6**
016000275287    GM CHEERIOS 18Z                     11
016000275317    GM HNY NUT CHEX 13.8Z               7
016000275584    GM CHEX CORN 14Z                    7
016000275591    GM CHEX RICE 12.08Z                 7
016000275645    GM CHEERIOS 14Z                     6
016000406087    GM CHEX VAN                         7
016000444980    GM CHEX GF GRAN HNYNUT              7
016000444997    GM CHEX GF GRAN BRYALM              7
016000486416    CHEX GF OATMEAL ORIG                7
016000486423    CHEX GF OATMEAL MAPLE BRNW SGR      7
016000486430    CHEX GF OATMEAL APPLE CINN          7
**016000487727  GM CHERRIOS                         11**
**016000487727  GM CHERRIOS                         6**
016000487895    GM CHEX CINN                        7
016000487925    GM CHEX HONEY NUT                   7
016000487932    GM CHEX CHOC                        7
016000487949    GM CHEX RICE                        7
016000487963    GM CHEX CORN                        7
016000487987    GM CHEX VANILLA                     7
**030000010402  QUAKER OATS OLD FSHN 42Z            11**
**030000010402  QUAKER OATS OLD FSHN 42Z            6**
030000012000    QUAKER OATS OLD FSHN QUICK 42 OZ    6
**038000016110  KELL SP K 12Z                       11**
**038000016110  KELL SP K 12Z                       6**
038000076480    KELL SP K VAN ALMND                 6
038000102653    KELL SPEC K CHOC ALMND              6
038000113277    KELL SP K PRO CBS CRNCH             6
038000113284    KELL SP K GLUTEN FREE               6

这是我用于上述结果的语句，我尝试了几种变体，但无法获得所需的结果:

select a.upc_ean, a.description, b.category from item_master a
inner join alternate_categories b
on a.item_id=b.item_Id
where b.category in ('1','2','3','4','5','6','7','8','9','10','11','12')
and store_pos_department=556
order by a.upc_ean

谢谢

Answer 1

尚未对此进行测试，但它应该接近您正在寻找的内容:

SELECT upc, description, count(*) over(partition by alt) as catcount FROM
( 
 SELECT i.upc, a.description, '1' as alt
  FROM item_master i
  JOIN alternate_categories1 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '2' as alt
  FROM item_master i
  JOIN alternate_categories2 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '3' as alt
  FROM item_master i
  JOIN alternate_categories3 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '4' as alt
  FROM item_master i
  JOIN alternate_categories4 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '5' as alt
  FROM item_master i
  JOIN alternate_categories5 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '6' as alt
  FROM item_master i
  JOIN alternate_categories6 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '7' as alt
  FROM item_master i
  JOIN alternate_categories7 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '8' as alt
  FROM item_master i
  JOIN alternate_categories8 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '9' as alt
  FROM item_master i
  JOIN alternate_categories9 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '10' as alt
  FROM item_master i
  JOIN alternate_categories10 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '11' as alt
  FROM item_master i
  JOIN alternate_categories11 a
  ON i.upc = a.upc
  UNION
  SELECT i.upc, a.description, '12' as alt
  FROM item_master i
  JOIN alternate_categories12 a
  ON i.upc = a.upc
)
WHERE catcount > 1 
and EXISTS(
  SELECT * from
  FROM item_master i
  JOIN alternate_categories11 a
  ON i.upc = a.upc
)