MySQL枢轴有3个表 [英] MySQL pivot with 3 tables

问题描述

我已经搜索并查看了有关从MySQL透视(动态)数据的各种帖子.我已经设法使其与一个表一起使用，但是当我尝试对三个表进行操作时却遇到了障碍.有人可以指出我正确的方向吗?

• 表1:供应商(我们从中购买的供应商列表)
• 表2:标准(每个供应商设置的标准)
• 表3:矩阵(用于 以上两个)

我想要实现的是这样的矩阵/数据点:

           Criteria1        Criteria2        Criteria3 
Vendor 1   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 2   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 3   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix

此表的使用示例:

           MinOrderQty        MinOrderValue        ReturnsAllowed 
Intel      1000                5000.00             RMA required
AMD        2000               15000.00             No
nVidia     9000                8000.00             RMA + dropship

供应商和条件的数量是动态的，可以由用户添加/删除，因此我无法对从行到列的转换进行硬编码.

ValueFromMatrix是一个Varchar(200)字段，包含文本.通过使用VendorID和CriteriaID(int)字段将其他两个表链接在一起.

我只是在这里碰到一堵砖墙，而我对我所见过的一些例子的解密并没有任何运气.

表格代码:

CREATE TABLE `vendors` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `criteria_desc` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `matrix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) DEFAULT NULL,
  `criteria_id` int(11) DEFAULT NULL,
  `criteria_response` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

数据库架构/数据的SQL提琴

criteria_response字段是上表示例中的ValueFromMatrix填充字段.

如果有人能够指出正确的方向，我将不胜感激.我只是没有看到任何讨论三个表以生成此视图的示例，所以我一直陷于困境.

谢谢.

解决方案

尝试根据我以前的答案之一改编的该查询(改编自示例SQL小提琴

I've searched and viewed various posts about pivoting (dynamically) data from MySQL. I've managed to get it working with one table, but I am hitting a block when I attempt to do this with three tables. Can someone point me in the right direction?

• Table 1: Vendors (List of Vendors we buy from)
• Table 2: Criteria (The criteria each Vendor sets)
• Table 3: Matrix (A link table for the above two)

What I would like to achieve is a matrix/pivot like this:

           Criteria1        Criteria2        Criteria3 
Vendor 1   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 2   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix
Vendor 3   ValueFromMatrix  ValueFromMatrix  ValueFromMatrix

An example of this table in use:

           MinOrderQty        MinOrderValue        ReturnsAllowed 
Intel      1000                5000.00             RMA required
AMD        2000               15000.00             No
nVidia     9000                8000.00             RMA + dropship

The number of Vendors and Criteria is dynamic and can be added/deleted by users, so I can't hard code the transform from rows to columns.

The ValueFromMatrix is a Varchar(200) field and contains text. It's linked two the other two tables by the use of a VendorID and a CriteriaID (int) fields.

I simply have hit a brick wall here, and I'm not having any luck deciphering some of the examples I've seen.

Table code:

CREATE TABLE `vendors` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `criteria` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `criteria_desc` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE `matrix` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) DEFAULT NULL,
  `criteria_id` int(11) DEFAULT NULL,
  `criteria_response` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

SQL Fiddle of DB schema/data

The criteria_response field is what populates the ValueFromMatrix in the table example above.

If anyone is able to point me in the right direction, I'd be grateful. I've just not seen any examples that discuss three tables to generate this view, and I keep getting stuck.

Thanks.

解决方案

Try this query which is adapted from one of my earlier answers (which was adapted from this question/answer):

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN criteria_id = ''',
      criteria_id,
      ''' THEN criteria_response END) AS `',
      criteria_desc, '`'
    )
  ) INTO @sql
FROM  matrix m JOIN criteria c ON m.criteria_id = c.id;

SET @sql = CONCAT('SELECT V.vendor_name, ', @sql, ' 
                  FROM matrix m
                  JOIN vendors v ON m.vendor_id = v.id
                  JOIN criteria c on m.criteria_id = c.id
                  GROUP BY v.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Sample SQL Fiddle

这篇关于MySQL枢轴有3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！