SQL Select 语句中的动态列，保留“未定义”值

这是基于我之前的问题的一个新问题，该问题被标记为问题的“重复”mySQL - 使用三个表中的数据和列创建新表 https://stackoverflow.com/questions/26665499/mysql-create-a-new-table-using-data-and-columns-from-three-tables

这个问题看起来很相似，但有一个重要部分是不同的 我尝试使用链接中的解决方案，但答案不适合我的问题。

我个人的 SQL 技能有限。环顾了几天后，我没有找到以下数据库查询问题的任何有效解决方案。我在这个问题的末尾附上了完整的示例数据库 SQL 文本。 我的示例数据库（用 MariaDB 创建）包含两个表：

• 物品，以及
• 项目属性。

对于每个项目只有一个item.ID and an 项目名被定义为。 （在现实生活中的示例中，该名称将被定义为唯一的。）

对于每个项目，可以有一组动态的用户定义的属性。这些属性被定义为名称-值对。 例如，对于名为“Banana“一个财产”Color“与价值”yellow“可能存在。

一项只有一个“颜色”属性才有效，因此不能为一项指定两种不同的颜色。

（在我的现实世界问题中，属性名称仅包含两个字符，因此不需要额外的属性名称表，并且随后为了便于显示示例中未使用的问题）。

items 表的示例数据：

ID, Name
1,  Car
2,  House
3,  Homer
4,  Earth

上述项目总共定义了九个属性。条目“(NULL)”表示未为给定项目定义此属性

ItemID, ItemName, Color,    Speed,  Price
1,      Car,      blue,       200,  50000
2,      House,    red,     (NULL), 250000
3,      Homer,    yellow,       5, (NULL)
4,      Earth,    blue,    108000, (NULL)

不幸的是我的选择声明

SELECT items.ID as ItemID, items.Name as ItemName,
CASE WHEN (itemproperties.Name = 'Color')
       THEN itemproperties.Value
       #ELSE NULL
END as Color,
CASE WHEN (itemproperties.Name = 'Speed')
       THEN itemproperties.Value
       #ELSE NULL
END as Speed,
CASE WHEN (itemproperties.Name = 'Price')
       THEN itemproperties.Value
       #ELSE NULL
END as Price
FROM items left join itemproperties 
ON  (items.ID=itemproperties.ItemID)

返回这样的数据

ItemID, ItemName, Color,   Speed, Price
1,      Car,      blue,   (NULL), (NULL)
1,      Car,      (NULL),  200,   (NULL)
1,      Car,      (NULL), (NULL), 50000
2,      House,    red,    (NULL), (NULL)
2,      House,    (NULL), (NULL), 250000
3,      Homer,    yellow, (NULL), (NULL)
3,      Homer,    (NULL),      5, (NULL)
4,      Earth,    blue,   (NULL), (NULL)
4,      Earth,    (NULL), 108000, (NULL)

问题：如何编写 select 语句来获取集合中的数据，每一项一行？

根据上面的链接问题，我还尝试了以下方法

SELECT i.ID as ItemID, i.Name as ItemName, 
       p1.Value AS Color, p2.Value AS Speed, p3.Value AS Price
FROM items as i
JOIN itemproperties AS p1 ON (i.ID=p1.ItemID)
JOIN itemproperties AS p2 ON (i.ID=p2.ItemID)
JOIN itemproperties AS p3 ON (i.ID=p3.ItemID)
WHERE (p1.Name = 'Color') and (p2.Name = 'Speed') and (p3.Name = 'Price')

但结果只有一行：

ItemID, ItemName, Color, Speed, Price
1,      Car,      blue,    200, 50000

出现此行为的原因是，只有项目“汽车”的所有三个属性“颜色”、“速度”和“价格”都填充了值。 项目“House”被跳过，因为它有“Color”和“Price”，但显然没有“Speed”。

那么如何以想要的方式获得该表呢？

问候艾克哈德

数据库定义：

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server Version:               10.1.13-MariaDB - mariadb.org binary distribution
-- Server Betriebssystem:        Win32
-- HeidiSQL Version:             9.4.0.5125
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


-- Exportiere Datenbank Struktur für DynamicColTest
CREATE DATABASE IF NOT EXISTS `dynamiccoltest` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `DynamicColTest`;

-- Exportiere Struktur von Tabelle DynamicColTest.itemproperties
CREATE TABLE IF NOT EXISTS `itemproperties` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the property',
  `ItemID` int(10) unsigned DEFAULT '0' COMMENT 'ID of the Item this property belongs to',
  `Name` varchar(20) DEFAULT '0' COMMENT 'Name of the property',
  `Value` varchar(20) DEFAULT '0' COMMENT 'Value of the property',
  UNIQUE KEY `Schlüssel 3` (`Name`,`ItemID`),
  KEY `Schlüssel 1` (`ID`),
  KEY `FK_itemproperties_items` (`ItemID`),
  CONSTRAINT `FK_itemproperties_items` FOREIGN KEY (`ItemID`) REFERENCES `items` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='The properties of the items';

-- Exportiere Daten aus Tabelle DynamicColTest.itemproperties: ~9 rows (ungefähr)
DELETE FROM `itemproperties`;
/*!40000 ALTER TABLE `itemproperties` DISABLE KEYS */;
INSERT INTO `itemproperties` (`ID`, `ItemID`, `Name`, `Value`) VALUES
    (1, 1, 'Color', 'blue'),
    (1, 4, 'Color', 'blue'),
    (1, 2, 'Color', 'red'),
    (2, 3, 'Color', 'yellow'),
    (3, 1, 'Speed', '200'),
    (3, 4, 'Speed', '108000'),
    (4, 3, 'Speed', '5'),
    (5, 1, 'Price', '50000'),
    (5, 2, 'Price', '250000');
/*!40000 ALTER TABLE `itemproperties` ENABLE KEYS */;

-- Exportiere Struktur von Tabelle DynamicColTest.items
CREATE TABLE IF NOT EXISTS `items` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Item ID',
  `Name` varchar(25) DEFAULT '0' COMMENT 'Name of the Item',
  KEY `Schlüssel 1` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='Contains all Items, with a minimum of definitions';

-- Exportiere Daten aus Tabelle DynamicColTest.items: ~4 rows (ungefähr)
DELETE FROM `items`;
/*!40000 ALTER TABLE `items` DISABLE KEYS */;
INSERT INTO `items` (`ID`, `Name`) VALUES
    (1, 'Car'),
    (2, 'House'),
    (3, 'Homer'),
    (4, 'Earth');
/*!40000 ALTER TABLE `items` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

你们非常接近。您需要加入您的键/值表itemproperties对于您需要检索的每个不同的键（属性）一次。问题是，你需要使用LEFT JOIN。素色内衬JOIN当未满足连接条件时抑制输出行。

尝试这个。

SELECT i.ID as ItemID, i.Name as ItemName, 
       p1.Value AS Color, p2.Value AS Speed, p3.Value AS Price
  FROM items as i
  LEFT JOIN itemproperties AS p1 ON (i.ID=p1.ItemID) AND (p1.Name = 'Color')
  LEFT JOIN itemproperties AS p2 ON (i.ID=p2.ItemID) AND (p2.Name = 'Speed')
  LEFT JOIN itemproperties AS p3 ON (i.ID=p3.ItemID) AND (p3.Name = 'Price')

选择的表达式Name值（z.B.p3.Name = 'Price'）进入你的ON条款而不是你的WHERE clause.