SQL:需要一个具有布尔值的多个字段的聚合透视图

Question

给定：我有一个源表，在多个布尔字段中包含鞋类的不同属性，该值指示该属性的鞋类是否可用。1=可用，0=不可用。样本数据如下-

>s_9>c_black>c_other1>001100010100100<1>0

Prod_id	s_7	s_8	s_10	c_white	c_blue	c_brown	t_shoes	1t_solate
001	0	1	1		0	1
002	1	1	0	1	0
003	0	1	0		1	0	1	0
004	0	0		1	0	0	1	0
005	1	0	1	1	1	1	0	0
006	0	1	1		0	1		1	1	0
007	0	0		1	1
008	0	1	0		0	1		1
009	1	0	0		0	1	0

您可以使用类似-的查询

SELECT
's_7' `attribute`,
SUM(`s_7` = 1) `s_7`,
SUM(`s_8` = 1) `s_8`,
SUM(`s_9` = 1) `s_9`,
SUM(`s_10` = 1) `s_10`,
SUM(`c_white` = 1) `c_white`,
SUM(`c_black` = 1) `c_black`,
SUM(`c_blue` = 1) `c_blue`,
SUM(`c_brown` = 1) `c_brown`,
SUM(`c_other` = 1) `c_other`,
SUM(`t_shoes` = 1) `t_shoes`,
SUM(`t_sandals` = 1) `t_sandals`,
SUM(`t_slippers` = 1) `t_slippers`
FROM t1 WHERE `s_7` = 1
UNION ALL
SELECT
's_8' `attribute`,
SUM(`s_7` = 1) `s_7`,
SUM(`s_8` = 1) `s_8`,
SUM(`s_9` = 1) `s_9`,
SUM(`s_10` = 1) `s_10`,
SUM(`c_white` = 1) `c_white`,
SUM(`c_black` = 1) `c_black`,
SUM(`c_blue` = 1) `c_blue`,
SUM(`c_brown` = 1) `c_brown`,
SUM(`c_other` = 1) `c_other`,
SUM(`t_shoes` = 1) `t_shoes`,
SUM(`t_sandals` = 1) `t_sandals`,
SUM(`t_slippers` = 1) `t_slippers`
FROM t1 WHERE `s_8` = 1
UNION ALL
SELECT
's_9' `attribute`,
...

这可以很容易地建立在你选择的语言中。下面是一个使用PHP-的简单示例

<?php
$attributes = ['s_7', 's_8', 's_9', 's_10', 'c_white', 'c_black', 'c_blue', 'c_brown', 'c_other', 't_shoes', 't_sandals', 't_slippers'];
$sql = null;
foreach ($attributes as $attribute) {
if ($sql) {
$sql .= ' UNION ALL ';
}
$sql .= "SELECT '$attribute' `attribute`";
foreach ($attributes as $attr) {
$sql .= ", SUM(`$attr` = 1) `$attr`";
}
$sql .= " FROM t1 WHERE `$attribute` = 1";
}
echo $sql;