SQL Select语句中的动态列，保持“未定义"状态价值观 [英] Dynamic Columns in SQL Select statement, keeping "undefined" values
问题描述
这是一个基于我的previos问题的新问题，被标记为对该问题重复" mySQL-使用数据创建新表和三个表中的列
This is a new Question based on my previos question, which was marked as beeing a "duplicate" of the question mySQL - Create a New Table Using Data and Columns from Three Tables
这个问题看起来很相似，但是有一个基本部分是不同的 我尝试使用链接中的解决方案，但Ansers不适合我的问题.
This question looks very similiar, but has one essential part which is different I tried to use the solutions from the linked, but the Ansers does not fit to my problem.
我的个人SQL技能是有限的.环顾了几天后，我在下面的数据库查询问题中找不到任何有效的解决方案.我在此问题的末尾附加了完整的示例数据库SQL文本. 我的示例数据库(由MariaDB创建)包含两个表:
My personal SQL skills are limited. After looking around for several days, I did not find any working solution for my database query problem below. I attached the full example Database SQL-text at the end of this question. My example database (made with MariaDB) contains two tables:
- 项目和
- itemproperties.
对于每个项目，仅定义一个 item.ID 和一个 item.Name . (在现实生活中，该名称将定义为唯一.)
For each item only an item.ID and an item.Name is defined. (In a real life example, the name would be defined to be unique.)
对于每个项目，动态的用户定义属性集都是可能的.这些属性被定义为名称-值对. 例如，对于名为"香蕉"的项目，可能存在值为"黄色"的属性"颜色".
For each item an dynamic user defined set of properties is possible. These properties are defined as a name-value-pair. For example, for an item named "Banana" a property "Color" with the value "yellow" may exists.
只有一项具有颜色"属性是有效的，因此不能将两种不同的颜色分配给一项.
It is only valid to have one "Color" property for one item, so that not two different colors could be assigned to one item.
(在我的现实世界中，属性名称仅包含两个字符，因此不需要附加的属性名称表，随后为了易于显示示例中未使用的问题).
(In my real world problem the property names contains only two characters, so an additional property name table is not necessary, and subsequently for the ease of showing the problem not used in the example).
items表的示例数据:
The example data for the items table:
ID, Name
1, Car
2, House
3, Homer
4, Earth
以上项共定义了9个属性.条目(NULL)"表示未为给定项目定义此属性
And a total of nine properties for the items above are defined. The entry "(NULL)" are indicating that this property is not defined for a given item
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语句
Unfortunally my select statement
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语句以整理形式获取数据，每一项对应一行?
Question: How to write the select statement to get tha data in a collated form, one row for each item?
根据上面的链接问题，我也尝试了以下方法
According too the linked question above, I tried also the following approach
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
发生这种情况的原因是，只有汽车"项中的所有三个属性颜色"，速度"和价格"都用值填充. 跳过房屋"项，因为它具有颜色"和价格"，但显然没有速度".
The reason for this behavior is, that onle the item "Car" have all the three properties "Color", "Speed" and "Price" filled with values. The item "House" is skipped because it has a "Color" and a "Price", but obviously no "Speed".
那么如何以想要的方式获取表?
So how to get the table in the wanted manner?
Ekkehard的问候
Greetings Ekkehard
数据库定义:
-- --------------------------------------------------------
-- 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将禁止输出行.
You are very close. You need to join your key/value table itemproperties once for each distinct key (property) you need to retrieve. The thing is, you need to use LEFT JOIN. Plain inner JOIN suppresses the output row when the join criterion is unmet.
尝试一下.
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子句中.
The expressions selecting the Name values (z.B. p3.Name = 'Price') go in your ON clause rather than your WHERE clause.
这篇关于SQL Select语句中的动态列，保持“未定义"状态价值观的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
