SQL Select 문의 동적 열, "정의되지 않은" 값 유지

SQL Select 문의 동적 열, "정의되지 않은" 값 유지

이것은 mySQL - Create a New Table Using Data and Columns from Three Tables(세 개의 테이블에서 데이터와 열을 사용하여 새 테이블 만들기) 질문의 "복제"를 나타내는 것으로 표시된 프리바이오스 질문에 근거한 새로운 질문입니다.

이 질문은 매우 비슷해 보이지만, 중요한 부분이 하나 있는데, 링크된 솔루션을 사용하려고 했지만 앤서가 제 문제에 맞지 않습니다.

개인 SQL 스킬은 한정되어 있습니다.며칠 동안 둘러본 결과, 아래 데이터베이스 쿼리 문제에 대한 유효한 해결책을 찾을 수 없었습니다.이 질문의 끝에 Database SQL-text의 전체 예를 첨부했습니다.예제 데이터베이스(MariaDB로 작성)에는 다음 두 개의 테이블이 있습니다.

• 아이템 및
• 항목 속성.

각 항목에 대해 하나의 항목만 해당됩니다.아이디와 아이템.이름이 정의되어 있습니다.(실제의 예에서는, 이름은 일의로 정의됩니다).

각 항목에 대해 동적 사용자 정의 속성 집합을 사용할 수 있습니다.이러한 속성은 이름-값 쌍으로 정의됩니다.예를 들어 "Banana"라는 이름의 항목의 경우 값 "노란색"을 가진 속성 "색상"이 존재할 수 있습니다.

하나의 항목에 대해 하나의 "색상" 속성을 가질 수 있으므로 한 항목에 두 개의 다른 색상을 할당할 수 없습니다.

(실제 문제에서는 속성 이름에 2글자만 포함되어 있기 때문에 추가 속성 이름 테이블이 필요하지 않으며, 이후 예에서는 사용되지 않는 문제를 쉽게 표시할 수 있습니다).

항목 테이블의 예제 데이터:

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

그리고 위의 항목에 대해 총 9개의 속성이 정의되어 있습니다.항목 "(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"가 없기 때문에 건너뜁니다.

그럼 어떻게 하면 원하는 방식으로 테이블을 얻을 수 있을까요?

인사 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취득할 필요가 있는 개별 키(클라이언트)마다 1회씩을 지정합니다.한 건, '아까', '아까', '아까', '아까' 이렇게 요.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절을 클릭합니다.

아래 답변은 에 대한 것입니다.SQL SERVER츠키노하고 .PIVOT기능을 합니다.특히 MariaDB에서도 같은 기능을 사용할 수 있습니다.

WITH cte as(   
SELECT i.ID as ItemID, i.Name as ItemName,p1.name,p1.value
FROM items as i
JOIN itemproperties AS p1 ON (i.ID=p1.ItemID)
)

select * from cte
PIVOT
(
    max(value) for Name in ([Color],[Speed],[Price])
)A

렉스테스터 데모

언급URL : https://stackoverflow.com/questions/43695483/dynamic-columns-in-sql-select-statement-keeping-undefined-values