将尺寸列显示为相关表中每种颜色产品的行? [英] Display size column as row for each color product in related table?

问题描述

我正在寻找一个查询来读取和组合三个表.下面是数据库表的简化设计:

<前>表:尺寸 表:颜色+------+------+ +------+-------+|身份证 |尺寸 ||身份证 |颜色 |+------+------+ +------+-------+|1 |32 ||1 |红色 ||2 |34 ||2 |蓝色 ||3 |36 ||3 |绿色||4 |38 ||4 |白色|+------+------+ +------+-------+

<前>表:订单+------+------+-------+------+|身份证 |尺寸 |颜色 |金额 |+------+------+-------+------+|1 |1 |1 |1 ||2 |1 |2 |3 ||3 |2 |2 |4 ||4 |3 |2 |2 |+------+------+-------+------+

因此，如果确实有该颜色的产品，则查询应始终返回所有尺寸并仅返回颜色.因此，使用上面描述的产品表，我将查询返回以下数据:

<前>+-------+------+------+------+------+||32 |34 |36 |38 |+-------+------+------+------+------+|红色 |1 |0 |0 |0 ||蓝色 |3 |4 |2 |0 |+-------+------+------+------+------+

当然，查询只返回大小、颜色和数量的组合，PHP 脚本将生成它的表.有人可以帮忙吗?提前致谢！

解决方案

没有 PIVOT 命令 (MySQL 数据透视表(将行转换为列))在 MySQL 中，因此您的查询将是静态的.这就是为什么最好在申请中推迟这一点.

认为如果您的大小列只有有限和小域，那么您可以使用我在下面发布的以下查询:

mysql>选择->c.color 作为颜色，->SUM(IF(s.size = 32, o.amount, 0)) 为'32'，->SUM(IF(s.size = 34, o.amount, 0)) 为'34'，->SUM(IF(s.size = 36, o.amount, 0)) 为'36'，->SUM(IF(s.size = 38, o.amount, 0)) 为 '38'->从`颜色` c->INNER JOIN `order` o->INNER JOIN `size` s->哪里 c.`id` = o.`color` 和 s.`id` = o.`size`->按颜色分组->;+-------+------+------+------+------+|颜色 |32 |34 |36 |38 |+-------+------+------+------+------+|蓝色 |3 |4 |2 |0 ||红色 |1 |0 |0 |0 |+-------+------+------+------+------+2 行(0.04 秒)

正如您在 IF 条件中所见，我使用大小值，这意味着问题是静态的.我假设所有可能的大小可以是 32、34、36、38.

工作演示@SQL Fiddle

正如我所说的，如果大小值未知或域很大，那么最好推迟服务器脚本(例如 PHP)中的数据透视工作，您仍然可以使用以下查询在脚本中进行处理:

SELECTc.color 作为颜色，s.大小，o.amount --Edit: 添加从`颜色` cINNER JOIN `order` oINNER JOIN `size` s哪里 c.`id` = o.`color` 和 s.`id` = o.`size`

看看它是如何工作的@SQL fiddle.

I'm looking for a query to read and combine three tables. Below, a simplified design of the database tables:

Table: Sizes             Table: Colors
+------+------+         +------+-------+
| id   | size |         | id   | color | 
+------+------+         +------+-------+
|    1 |   32 |         |    1 | red   |
|    2 |   34 |         |    2 | blue  |
|    3 |   36 |         |    3 | green |
|    4 |   38 |         |    4 | white |
+------+------+         +------+-------+

Table: Orders 
+------+------+-------+--------+
| id   | size | color | amount |
+------+------+-------+--------+
|    1 |    1 |     1 |      1 |
|    2 |    1 |     2 |      3 |
|    3 |    2 |     2 |      4 |
|    4 |    3 |     2 |      2 |
+------+------+-------+--------+

So, the query should return always all the sizes and return just the colors if there is actually a product in that color. So, with the product table above described, i would a query which is return the following data:

+-------+------+------+------+------+
|       | 32   | 34   | 36   | 38   |
+-------+------+------+------+------+
| red   |    1 |    0 |    0 |    0 |
| blue  |    3 |    4 |    2 |    0 |
+-------+------+------+------+------+

Of course, the query returns just the combination of the size, color and amount and a PHP script will generate the table of it. Could somebody help with it? Thanks in advance!

解决方案

There is no PIVOT command (MySQL pivot tables (transform rows to columns)) in MySQL so your query will be static for size. That is why it is better to postponed this in application.

Thought if you have just finite and small domain for size column then you can use following Query I posted below:

mysql> SELECT 
    ->      c.color as color,
    ->      SUM(IF(s.size = 32, o.amount, 0)) as '32',
    ->      SUM(IF(s.size = 34, o.amount, 0)) as '34',
    ->      SUM(IF(s.size = 36, o.amount, 0)) as '36',
    ->      SUM(IF(s.size = 38, o.amount, 0)) as '38'
    ->  FROM `colors` c
    ->  INNER JOIN `order` o
    ->  INNER JOIN `sizes` s
    ->      WHERE c.`id` = o.`color` and s.`id` = o.`size`
    -> GROUP BY color 
    -> ;
+-------+------+------+------+------+
| color | 32   | 34   | 36   | 38   |
+-------+------+------+------+------+
| blue  |    3 |    4 |    2 |    0 |
| red   |    1 |    0 |    0 |    0 |
+-------+------+------+------+------+
2 rows in set (0.04 sec)

As you can see in IF conditions I use value of size that is what I means question is static. I am assuming all possible size can be either 32, 34, 36, 38.

Working demo @SQL Fiddle

Edit: As I am saying from starting If size values are unkown or domain is large then better is you postponed pivot work within your server script (e.g. PHP) still you can use following query to process in script:

SELECT 
     c.color as color,
     s.size,
     o.amount  --Edit: added 
 FROM `colors` c
 INNER JOIN `order` o
 INNER JOIN `sizes` s
     WHERE c.`id` = o.`color` and s.`id` = o.`size`

See how does it works @SQL fiddle.

这篇关于将尺寸列显示为相关表中每种颜色产品的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！