联接表列出行作为列联接到另一个表? [英] Join tables listing rows as columns joined to another table?

问题描述

我有3个表-事物，定义和信息(糟糕的名称，但为简单起见，明显减少了！)

I have 3 tables - things, defs, and info (terrible names, but significantly reduced for simplicity!)

信息

CREATE TABLE `info` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `thingid` bigint(10) NOT NULL DEFAULT '0',
  `defid` bigint(10) NOT NULL DEFAULT '0',
  `data` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `infodata_coufie_ix` (`thingid`,`defid`)
);

id   | thingid  | defid   | data
1    | 1        | 1       | 1
1    | 1        | 2       | 25
1    | 2        | 1       | 0
1    | 2        | 3       | yellow
1    | 3        | 1       | 0

defs

CREATE TABLE `defs` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `datatype` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

id   | name    | datatype
1    | enabled | boolean
2    | size    | numeric
3    | colour  | string

事物

CREATE TABLE `things` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

id    | name
1     | bill
2     | terry
3     | nancy

我希望能够显示物"的"defs"值，因此生成的表/视图将类似于

I'd like to be able to show the "defs"' value of "things", so a resultant table / view would be something like

thingid | name   | enabled   | size   | colour
1       | bill   | true      | 25     | null
2       | terry  | false     | null   | yellow
3       | nancy  | true      | null   | null

，因此defs中的行将成为列标题；与thingid的那些列标题匹配的值将形成这些行的数据.

so the rows from defs would become column headers; the values matching those column headers for thingid would form the data for those rows.

我很早以前已经在SQL Server中做到了这一点，而且我一生都不记得该怎么做.我现在需要在MySql5中执行此操作.我一直在上下阅读 http://www.artfulsoftware.com/infotree/queries.php 和各种SE文章，但我现在已经把自己搞糊涂了，所以我不得不去问一个人.

I've done this long ago in SQL Server, and for the life of me can't remember how to do it. I now need to do it in MySql5. I've been reading up and down http://www.artfulsoftware.com/infotree/queries.php and various SE articles but I've now confused the heck out of myself, so I have to actually ask someone.

推荐答案

SELECT i.thingid, t.name,
       MAX(IF(d.name = "enabled", i.data, NULL)) enabled,
       MAX(IF(d.name = "size", i.data, NULL)) size,
       MAX(IF(d.name = "colour", i.data, NULL)) colour
FROM info i
JOIN defs d ON i.defid = d.id
JOIN things t ON i.thingid = t.id
GROUP BY i.thingid

演示

这篇关于联接表列出行作为列联接到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！