php - MySQL行到四列

Question

任何人都可以帮助我如何使用源表上的行数据作为输出标题来创建查询输出。请参阅下面的说明。

例如

行数据:

+-----------+-----------+-----------+-----------+
| colHeader | value     | no        | price     |
+-----------+-----------+-----------+-----------+
| Header1   | value 1   | no 1      | price1    |
+-----------+-----------+-----------+-----------+
| Header1   | value 2   | no 2      | price2    |
+-----------+-----------+-----------+-----------+
| Header2   | value 1   | no 3      | price3    |
+-----------+-----------+-----------+-----------+
| Header2   | value 2   | no 4      | price4    |
+-----------+-----------+-----------+-----------+

输出:

+-----------+-----------+-----------+-----------+-----------+
| colHeader | value 1 no| value 2 no|value1price|value2price|
+-----------+-----------+-----------+-----------+-----------+
| Header1   | no 1      |    no 2   |price1     |   price2  |
+-----------+-----------+-----------+-----------+-----------+
| Header2   | no 3      |    no 4   |price3     |   price4  |
+-----------+-----------+-----------+-----------+-----------+

这可能吗？我想通过 PHP 打印它

谢谢。

Answer 1

select
colHeader,
max(case when value = 'value 1' then no else null end) as value1,
max(case when value = 'value 2' then no else null end) as value2,
max(case when value = 'value 1' then price else null end) as value1price,
max(case when value = 'value 2' then price else null end) as value2price
from
your_table
group by colHeader

• 看到它在 sqlfiddle 中实时运行

希望分多个步骤执行查询是 self 解释(不过添加了一些注释):

select
*
from
t;

结果:

| COLHEADER |   VALUE |   NO |  PRICE |
|-----------|---------|------|--------|
|   Header1 | value 1 | no 1 | price1 |
|   Header1 | value 2 | no 2 | price2 |
|   Header2 | value 1 | no 3 | price3 |
|   Header2 | value 2 | no 4 | price4 |

---

select
colHeader,
case when value = 'value 1' then no else null end as value1,
case when value = 'value 2' then no else null end as value2,
case when value = 'value 1' then price else null end as value1price,
case when value = 'value 2' then price else null end as value2price
from
t;

结果:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 | (null) |      price1 |      (null) |
|   Header1 | (null) |   no 2 |      (null) |      price2 |
|   Header2 |   no 3 | (null) |      price3 |      (null) |
|   Header2 | (null) |   no 4 |      (null) |      price4 |

---

不确定接下来的步骤是否比其他步骤更令人困惑。它按列 colHeader 返回“折叠”的行。也就是说，如果没有使用 max() 之类的聚合函数，则每组显示一个随机行(在这种情况下，每隔一行被省略)。请注意，这不是标准 SQL，如果我没记错的话，只有 MySQL 允许这样做。无论如何，这就是我们在最后一步中使用 max() 函数的原因。

select
colHeader,
case when value = 'value 1' then no else null end as value1,
case when value = 'value 2' then no else null end as value2,
case when value = 'value 1' then price else null end as value1price,
case when value = 'value 2' then price else null end as value2price
from
t
group by colHeader;

结果:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 | (null) |      price1 |      (null) |
|   Header2 |   no 3 | (null) |      price3 |      (null) |

---

最后一步:

select
colHeader,
max(case when value = 'value 1' then no else null end) as value1,
max(case when value = 'value 2' then no else null end) as value2,
max(case when value = 'value 1' then price else null end) as value1price,
max(case when value = 'value 2' then price else null end) as value2price
from
t
group by colHeader;

结果:

| COLHEADER | VALUE1 | VALUE2 | VALUE1PRICE | VALUE2PRICE |
|-----------|--------|--------|-------------|-------------|
|   Header1 |   no 1 |   no 2 |      price1 |      price2 |
|   Header2 |   no 3 |   no 4 |      price3 |      price4 |