mysql - 复杂的 SELECT 查询

Question

我有一个表，它定义了另一个表可以包含的内容，例如:

CREATE TABLE `objects` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
);

INSERT INTO `objects` (`name`) VALUES ('Test');
INSERT INTO `objects` (`name`) VALUES ('Test 2');

CREATE TABLE `properties` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
);

INSERT INTO `properties` (`name`) VALUES ('colour');
INSERT INTO `properties` (`name`) VALUES ('size');

CREATE TABLE `objects_properties` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `object_id` INT UNSIGNED NOT NULL,
  `property_id` INT UNSIGNED NOT NULL,
  `value` VARCHAR(50) NOT NULL,
  FOREIGN KEY (`object_id`)
    REFERENCES `objects` (`id`),
  FOREIGN KEY (`property_id`)
    REFERENCES `properties` (`id`)
);

INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 1, 1, 'red');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 1, 2, 'small');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 2, 1, 'blue');
INSERT INTO `objects_properties` (`object_id`, `property_id`, `value`) VALUES 2, 2, 'large');

希望这是有道理的。基本上，在对象表中没有颜色、大小等列，我有另外两个表，一个定义任何对象可以具有的属性，另一个将对象链接到这些属性的部分或全部。

我的问题是是否有某种方法可以像这样检索此信息:

+--------+------------+------------+| object | colour     | size       |+--------+------------+------------+| Test   | red        | small      || Test 2 | blue       | large      |+--------+------------+------------+

因此您可以看到列标题实际上是行值。与执行几个单独的查询并将所有内容放在 PHP 中相比，我不确定这是否可能或成本如何。

Answer 1

SELECT o.name, c.colour, s.size
FROM objects o
LEFT JOIN (SELECT op.object_id, op.value colour
        FROM objects_properties op
        join properties p on op.property_id = p.id and p.name = 'colour') c
ON o.id = c.object_id
LEFT JOIN (SELECT op.object_id, op.value size
        FROM objects_properties op
        join properties p on op.property_id = p.id and p.name = 'size') s
ON o.id = s.object_id