mysql，查询两个链接表从第一个返回 id 的秒详细信息满足这个但不是这个条件

Question

我正在尝试编写一个 MySQL 查询，该查询将返回其详细信息满足 this 但不满足 this 条件的元素。

以办公用品库存管理系统为例。所有类型的供应品都将包含在这个数据库中，而不是创建一个包含无数列的表格，我改为这样做了产品描述的每个属性。

CREATE TABLE `inventory`(
id INTEGER AUTO_INCREMENT PRIMARY KEY,
element_name VARCHAR(255) NOT NULL
);

CREATE TABLE `inventory_detail`(
id INTEGER AUTO_INCREMENT PRIMARY KEY,
element_id INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
attribute_string VARCHAR(255) NULL,
attribute_integer INTEGER NULL
);

使用这些表格，每个产品都可以添加为库存，并且它的属性可以添加为库存详细信息。 (即 label = color, attribute_string = red 或 label = color, attribute_string != blue) 不要批评这种方法，这不是我的问题。此外，这种方法允许我不断更改有关存储的产品的详细信息的数量，而不必以任何方式更改数据库。

我的问题是，如果我想获得所有具有正数的绿色产品的列表，我的查询应该是什么样子。基本上我正在寻找一个返回 element.id 的查询，并且元素必须按异常(exception)的细节和不异常(exception)的细节进行排序。例如:

SELECT element.id
FROM element
INNER JOIN element_detail ON element.id = element_detail.id
WHERE (element_detail.label = 'color'
AND element_detail.attribute_string = 'green')
AND (element_detail.label = 'count'
AND element_detail.attribute_integer <> 0);

####################################
#             INVENTORY            #
####################################
# id #           label             #
####################################
#  1 #   multi color pen pack      #
#  2 #   single pen pack (red)     #
#  3 #   single pen pack (green)   #
#  4 #   single pen pack (black)   #
#  5 #   single pen pack (blue)    #
#  6 #   single pen pack (purple)  #
####################################

########################################################
#                  INVENTORY_DETAILS                   #
########################################################
# id # element_id #    label   #    attribute_string   #
########################################################
#  1 #     1      #    color   #          red          #
#  2 #     1      #    color   #          blue         #
#  3 #     1      #    color   #          black        #
#  4 #     1      #    color   #          green        #
#  5 #     1      #    color   #          red          #
#  6 #     1      #    count   #          100          #
#  7 #     2      #    color   #          red          #
#  8 #     2      #    count   #          50           #
#  9 #     3      #    color   #          green        #
# 10 #     3      #    count   #          50           #
# 11 #     4      #    color   #          black        #
# 12 #     4      #    count   #          50           #
# 13 #     5      #    color   #          blue         #
# 14 #     5      #    count   #          0            #
# 15 #     5      #    color   #          purple       #
# 16 #     5      #    count   #          50           #
########################################################

将返回库存中所有产品及其数量为红色而非绿色的查询。

########################################
#              RESULT                  #
########################################
#       PRODUCT             #   COUNT  #
#  single pen pack (red)    #     50   #
#  single pen pack (black)  #     50   #
#  single pen pack (blue)   #     0    #
#  single pen pack (purple) #     50   #
########################################

虽然多色笔包有一支红色笔，但它也有一支绿色笔，因此不应包含在结果中。

Answer 1

如果您希望所有“绿色”产品的计数为正，那么您需要在明细表中加入两次

SELECT element.id
FROM element
INNER JOIN element_detail e1 ON element.id = e1.id
INNER JOIN element_detail e2 ON element.id = e2.id
WHERE (
      e1.label = 'color'
  AND e1.attribute_string = 'green'
) AND (
      e2.label = 'count'
  AND e2.attribute_integer > 0
);