SQL Select 语句中的动态列 [英] Dynamic Columns in SQL Select statement
问题描述
这个问题被标记为问题的重复"mySQL - 使用数据创建新表和三个表中的列
This Question was marked as beeing a "duplicate" of the question mySQL - Create a New Table Using Data and Columns from Three Tables
我认为问题是不同的，因为有三个表在起作用，在我的问题中是两个.这里的问题是如何创建一个新表，这里的问题是创建一个输出.但是感谢您的指出，也许它比我预期的更接近我的问题.
I think the question is different, because there are three tables in action, in my question are two. There the question is how to create a new table, here the question is to create an output. But thanks for pointing, maybe it's closer to my problem than I expecting.
我尝试使用重复"问题中的解决方案，但答案和问题不是很相似.
I tried to use the solutions from the "Duplicate"-Question, but the Ansers and the problems are not very simmilar.
也根据重复"问题，我试过了
According too the "Duplicated"-Question, I tried
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
而不是预期的四行，在没有找到属性名称的地方包含值(NULL)".
Instead of the expected four lines, containing the value "(NULL)" at the places where no property name is found.
所以恕我直言，重复"问题是不同的，没有回答我的问题！
So IMHO the "Duplicate"-Question is different and did not answer my question!
原问题:
我个人的 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:
- 项目和
- 项目属性.
对于每个项目，只定义了一个 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.)
对于每个项目，一个动态的用户定义的属性集是可能的.这些属性被定义为名称-值-对.例如，对于名为Banana"的项目，可能存在值为yellow"的属性Color".
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
并且为上述项目定义了总共九个属性.条目(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 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? Thank you very much!
你好，埃克哈德
数据库定义:
-- --------------------------------------------------------
-- 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 */;
推荐答案
您可以通过这种方式在不连接每个属性的情况下执行此操作:
You can do this without a join per attribute this way:
SELECT i.ID, i.Name,
MAX(IF(p.Name='Color', p.value, NULL)) AS Color,
MAX(IF(p.Name='Speed', p.value, NULL)) AS Speed,
MAX(IF(p.Name='Price', p.value, NULL)) AS Price
FROM items i
LEFT JOIN itemproperties p
ON (i.ID=p.ItemID)
GROUP BY i.ID
输出:
+----+-------+--------+--------+--------+
| ID | Name | Color | Speed | Price |
+----+-------+--------+--------+--------+
| 1 | Car | blue | 200 | 50000 |
| 2 | House | red | NULL | 250000 |
| 3 | Homer | yellow | 5 | NULL |
| 4 | Earth | blue | 108000 | NULL |
+----+-------+--------+--------+--------+
但使用 EAV 数据总是很尴尬且有风险.尽量避免.
But working with EAV data is always awkward and risky. Avoid it if you can.
PS:你还应该为你的 items 表声明一个主键约束.
PS: Also you should declare a primary key constraint for your items table.
alter table items add primary key (ID);
这篇关于SQL Select 语句中的动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
