MySQL - 复杂查询，包括来自多个表的查找列

Question

我们再来一次...

[编辑]底部有一个使用 SQL 的更新来生成测试数据库。 [编辑]

我已经为一个相当复杂的 SQL 查询而苦苦挣扎了几天。如果没有 SO 的大力帮助，我永远不可能走到这一步，他们让我学到了很多 SQL 知识。

我将尝试引导您完成设置，以便您了解我想要做什么。请耐心等待...

背景

我有一张带有整数属性（IntegerAttributes）的表。 ID 是项目 ID，其中 Key 是附加到 ID 为“ID”的项目的属性名称，Value 是属性的值并且只能是整数。一项可以有 0 到多个属性。

IntegerAttributes
ID  Key       Value
1   Location  3
1   Color     5
2   Location  1
3   Color     3

属性“Location”的值是另一个名为“Locations”的表中的 ID

Locations
ID Location etc...
1  Boston
2  Manilla
3  Stockholm
4  Beijing

属性“Color”的值是另一个名为“Colors”的表中的 ID

Locations
ID Color
1  Blue
2  Black
3  White
4  Red
5  Green

还有一个类似的字符串表 (StringAttributes)，适用于与 IntegerAttributes 的方式相同，只是 Value 列是文本。 IntegerAttributes 和 StringAttributes 都通过 UNION ALL 组合并转换为字符串。

此时保存每个项目的表非常简单：

ID  Checkin
1   2010-01-22 11:28:18
2   2010-01-21 16:27:54
3   2010-01-20 18:40:07

到目前为止，一切都清楚了，但现在变得复杂了：

这是我用来获取所有属性并将它们连接到 JSON 字符串并获取项目数据的 SQL

SELECT
  i.ID,
  i.Checkin,
  CONCAT('{\"Date\":\"',i.Checkin,'\",', GROUP_CONCAT('\"',Attribute.key, '\":\"', CONVERT(Attribute.value,CHAR), '\"'), '}') as Attributes
  # , l.Location
FROM  (
  SELECT ItemID, ats.Key, ats.Value
  FROM attributeStrings as ats 
  UNION ALL
  SELECT ItemID, ati.Key, ati.Value
  FROM attributeIntegers as ati
) Attribute
JOIN Items i ON i.ID = Attribute.ItemID
  # JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)
GROUP BY ItemID
ORDER BY i.ID DESC

查询我已经评论了两行，我很快就会回复这些内容。

问题

上面的查询结果将是这样的：

ID  Checkin              Attributes
1   2010-01-22 11:28:18  {"Date":"2010-01-22 11:28:18","Location":"3","Color":"5"}
2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}
3   2010-01-20 18:40:07  {"Date":"2010-01-20 18:40:07","Color":"3"}

到目前为止一切顺利。

但现在我想包括“位置”的查找（最终包括“颜色”或其他具有查找 ID 的属性）。

如果我取消注释上面查询中的两行，

# , l.Location
# JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)

我只会得到唯一属性为“位置”的结果，并且“属性”字段现在将仅包含“日期”和“位置”属性。

ID  Checkin              Attributes                                     Location
2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}  Boston

期望的结果

我想要的结果与以前一样，但有一个额外的列，其中包含在位置中查找的“位置”（以及稍后的更多查找列）属性。

我想要的示例：

ID  Checkin              Attributes                                                 Location   Color
1   2010-01-22 11:28:18  {"Date":"2010-01-22 11:28:18","Location":"3","Color":"5"}  Stockholm  Green
2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}              Boston     null
3   2010-01-20 18:40:07  {"Date":"2010-01-20 18:40:07","Color":"3"}                 null       White

感谢您一路读到这里：D 我试图详细说明 IF EXISTS 但我不知道如何做到这一点。

[编辑] SQL TO GENERATE A TEST DATABASE [EDIT]

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version   5.1.47-community


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!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' */;


--
-- Create schema swebussandbox
--

CREATE DATABASE IF NOT EXISTS SODatabase;
USE SODatabase;

--
-- Definition of table `attributeintegers`
--

DROP TABLE IF EXISTS `attributeintegers`;
CREATE TABLE `attributeintegers` (
  `ItemID` int(10) unsigned NOT NULL,
  `Key` varchar(45) NOT NULL,
  `Value` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ItemID`,`Key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `attributeintegers`
--

/*!40000 ALTER TABLE `attributeintegers` DISABLE KEYS */;
INSERT INTO `attributeintegers` (`ItemID`,`Key`,`Value`) VALUES
 (4,'Color',17),
 (4,'Location',3),
 (5,'Location',2),
 (6,'Location',6),
 (7,'Color',15),
 (8,'Location',8),
 (9,'Location',10),
 (10,'Color',15),
 (10,'Location',2),
 (11,'Color',15),
 (11,'Location',4),
 (12,'Color',15),
 (12,'Location',3),
 (13,'Color',15),
 (13,'Location',8),
 (14,'Location',3),
 (15,'Location',6),
 (16,'Color',18),
 (18,'Color',15),
 (18,'Location',4);
 /*!40000 ALTER TABLE `attributeintegers` ENABLE KEYS */;


--
-- Definition of table `attributestrings`
--

DROP TABLE IF EXISTS `attributestrings`;
CREATE TABLE `attributestrings` (
  `ItemID` int(10) unsigned NOT NULL DEFAULT '0',
  `Key` varchar(45) NOT NULL DEFAULT '_NA_',
  `Value` text,
  PRIMARY KEY (`ItemID`,`Key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- Dumping data for table `attributestrings`
--

/*!40000 ALTER TABLE `attributestrings` DISABLE KEYS */;
INSERT INTO `attributestrings` (`ItemID`,`Key`,`Value`) VALUES
 (5,'Type','BagForm'),
 (6,'Type','BagForm'),
 (9,'Type','BagForm'),
 (10,'Type','BagForm'),
 (11,'Type','BagForm'),
 (12,'Brand','Bogcase'),
 (12,'Type','BagForm'),
 (14,'Type','BagForm'),
 (15,'Brand','Carryline World Wide'),
 (15,'Type','BagForm'),
 (16,'Brand','Fjällräven'),
 (16,'Type','BagForm'),
 (17,'Brand','Packard Bell'),
 (17,'Tech','ComputerForm'),
 (17,'Type','TechGUI');
/*!40000 ALTER TABLE `attributestrings` ENABLE KEYS */;



--
-- Definition of table `colors`
--

DROP TABLE IF EXISTS `colors`;
CREATE TABLE `colors` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Color` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `colors`
--

/*!40000 ALTER TABLE `colors` DISABLE KEYS */;
INSERT INTO `colors` (`ID`,`Color`) VALUES
 (1,'Multicolored'),
 (2,'Black'),
 (3,'White'),
 (4,'Red'),
 (5,'Green'),
 (6,'Blue'),
 (7,'Yellow'),
 (8,'Black'),
 (9,'Gold'),
 (10,'Bown'),
 (11,'Purpul'),
 (12,'Pink'),
 (13,'Orange'),
 (14,'Gray'),
 (15,'Transparent');
/*!40000 ALTER TABLE `colors` ENABLE KEYS */;



--
-- Definition of table `items`
--

DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CheckIn` datetime DEFAULT NULL,
  `Line` int(10) unsigned DEFAULT NULL,
  `TypeID` int(10) unsigned DEFAULT NULL,
  `SizeID` int(10) unsigned DEFAULT NULL,
  `ColorID` int(10) unsigned DEFAULT NULL,
  `MaterialID` int(10) unsigned DEFAULT NULL,
  `CheckOut` datetime DEFAULT NULL,
  `LocationID` int(10) unsigned DEFAULT NULL,
  `Notes` text,
  `Tur` int(10) unsigned DEFAULT NULL,
  `Bus` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  FULLTEXT KEY `NoteIndex` (`Notes`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 PACK_KEYS=1;

--
-- Dumping data for table `items`
--

/*!40000 ALTER TABLE `items` DISABLE KEYS */;
INSERT INTO `items` (`ID`,`CheckIn`) VALUES
 (4,'2010-12-03 02:04:38'),
 (5,'2010-12-27 02:11:25'),
 (6,'2010-12-27 02:14:28'),
 (7,'2010-12-25 02:17:09'),
 (8,'2010-12-24 02:33:49'),
 (9,'2011-01-06 07:48:16'),
 (10,'2011-01-06 07:47:09'),
 (11,'2010-12-31 10:53:26');
/*!40000 ALTER TABLE `items` ENABLE KEYS */;


--
-- Definition of table `locations`
--

DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Location` varchar(45) NOT NULL,
  `Group` int(10) unsigned NOT NULL DEFAULT '1',
  `Address` text,
  `Phone` varchar(20) DEFAULT NULL,
  `Contact` varchar(45) DEFAULT NULL,
  `Hours` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `locations`
--

/*!40000 ALTER TABLE `locations` DISABLE KEYS */;
INSERT INTO `locations` (`ID`,`Location`) VALUES
 (1,'Boston'),
 (2,'Stockholm'),
 (3,'Manilla'),
 (4,'Berlin'),
 (5,'Oslo'),
 (6,'Paris'),
 (7,'London'),
 (8,'Amsterdam'),
 (9,'Helsinki'),
 (10,'Kopenhagen'),
 (11,'Barselona'),
 (12,'Luxenbourg'),
 (13,'Milano');
/*!40000 ALTER TABLE `locations` ENABLE KEYS */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

我用这个更新的查询尝试了 Tumbler 的建议：

SELECT
  i.ID,
  i.Checkin,
  CONCAT('{\"Date\":\"',i.Checkin,'\",', GROUP_CONCAT('\"',Attribute.key, '\":\"', CONVERT(Attribute.value,CHAR), '\"'), '}') as Attributes,
  COALESCE(l.Location,null) as Location,
  COALESCE(c.Color,null) as Color
FROM  (
  SELECT ItemID, ats.Key, ats.Value
  FROM attributeStrings as ats
  UNION ALL
  SELECT ItemID, ati.Key, ati.Value
  FROM attributeIntegers as ati
) Attribute
LEFT JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)
LEFT JOIN Colors c ON (Attribute.Key = 'Color' AND c.ID = Attribute.Value)
JOIN Items i ON i.ID = Attribute.ItemID
GROUP BY ItemID
ORDER BY i.ID DESC

...但它确实显示了所有属性，但是仅适用于将位置或颜色作为其唯一属性的项目。

Answer 1

尝试左连接

LEFT JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)

如果空值是一个问题：

, COALESCE(l.Location,'No Location')

您的示例有点太复杂而无法轻松复制，但请让我们知道它是如何进行的。

Answer 2

SELECT  i.ID, i.checkin,
        CONCAT('{\"Date\":\"', i.checkin,'\",', GROUP_CONCAT('\"', attribute.key, '\":\"', CONVERT(attribute.value,CHAR), '\"'), '}') as attributes,
        l.location, c.color
FROM    (
        SELECT  ItemID, ats.key, ats.value
        FROM    attributestrings as ats
        UNION ALL
        SELECT  ItemID, ati.Key, ati.Value
        FROM    attributeintegers as ati
        ) attribute
JOIN    items i
ON      i.ID = attribute.itemid
LEFT JOIN
        attributeintegers atli
ON      atli.itemid = i.id
        AND atli.key = 'Location'
LEFT JOIN
        locations l
ON      l.id = atli.value
LEFT JOIN
        attributeintegers atci
ON      atci.itemid = i.id
        AND atci.key = 'Color'
LEFT JOIN
        colors c
ON      c.id = atci.value
GROUP BY
        i.id DESC