选择给定集合之外没有属性的项目 [英] Select items with no attributes outside a given set

问题描述

我需要有关 MySQL 查询的帮助.我有 2 张桌子，一张名为 cars，一张名为 accessories(这是虚构的).

I need help with a query in MySQL. I have 2 tables, one named cars and one called accessories (this is fictional).

cars 表中的一辆汽车在表中带有一套配件.汽车桌看起来像这样.

A car in the cars table carries a set of accessories in the table. Car table looks like this.

ID
Name
Accessory 1
Accessory 2
Accessory 3
Accessory 4
Accessory 5
etc..

然后我希望用户通过复选框指定他想要的配件.然后运行查询以显示 cars 中的各种汽车，only那些配件或更少.

I then want the user to specify what accessories he wants with tickboxes. Then run a query to show various cars from cars with only those accessories or less.

ID = 1
Name = Ford Mondeo
Accessory 1 = AC
Accessory 2 = Turbo

ID = 2
Name = Ford Mondeo SE
Accessory 1 = AC
Accessory 2 = Turbo
Accessory 3 = Cruise control
Accessory 4 = CD

ID = 3
Name = Ford Mustang
Accessory 1 = AC
Accessory 2 = Turbo
Accessory 3 = Cruise control
Accessory 4 = CD
Accessory 5 = DVD

如果用户指定了AC、Turbo、Cruise control 和CD，则第一和第二辆车应该显示，但不是第三个，因为它们没有勾选 DVD.

If the user specified AC, Turbo, Cruise control and CD, then the 1st and 2nd car should show, but not the 3rd, since they did not tick DVD.

结果不应显示任何未打勾"的帖子.

The result should not show any post with anything not "ticked".

我希望有人可以帮助我构建此操作的查询.

I hope someone can help me with constructing a query for this operation.

推荐答案

首先，正如其他人所建议的，你应该有一张表用于汽车，一张用于配件，还有一张链接表 列出每辆车有哪些配件.举个简单的例子，你可以像这样设置你的表格:

First of all, as others have suggested, you should have one table for the cars, one for the accessories, and one link table listing which accessories each car has. For a simple example, you could set up your tables like this:

CREATE TABLE cars (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE accessories (
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  UNIQUE KEY (name)  /* for efficient lookups by name */
);

CREATE TABLE car_accessories (
  car INTEGER NOT NULL,
  accessory INTEGER NOT NULL,
  PRIMARY KEY (car, accessory),
  UNIQUE KEY (accessory, car)
);

要列出除AC、Turbo、Cruise control 和CD 之外没有附件的所有汽车，然后，您可以使用 NOT EXISTS 子选择 (SQLize):

To list all cars that have no accessories other than AC, Turbo, Cruise control and CD, you can then use either a NOT EXISTS subselect (SQLize):

SELECT *
FROM cars AS c
WHERE NOT EXISTS
  ( SELECT * FROM car_accessories AS ca, accessories AS a
    WHERE ca.car = c.id
      AND ca.accessory = a.id
      AND a.name NOT IN ('AC', 'Turbo', 'Cruise control', 'CD') )

或LEFT JOIN(SQLize):

SELECT c.*
FROM cars AS c
  LEFT JOIN 
    ( car_accessories AS ca
      JOIN accessories AS a
        ON ca.accessory = a.id
        AND a.name NOT IN ('AC', 'Turbo', 'Cruise control', 'CD')
    ) ON ca.car = c.id
WHERE ca.car IS NULL

这篇关于选择给定集合之外没有属性的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！