我试图搜索帖子,但只找到了针对sql server/access的解决方案。我需要一个mysql(5.x)的解决方案。
我有一个表(称为history),有3列:hostid、itemname、itemvalue。
如果我选择( select * from history
),它会回来的
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | c | 40 |
+--------+----------+-----------+
如何查询数据库以返回
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
12条答案
按热度按时间3pmvbmvn1#
我将对解决这个问题所要采取的步骤做一个更长更详细的解释。如果时间太长,我道歉。
我将从你给出的基础开始,用它来定义几个术语,我将在本文的其余部分使用这些术语。这将是基表:
这就是我们的目标,漂亮的透视表:
中的值
history.hostid
列将成为透视表中的y值。中的值history.itemname
列将成为x值(原因很明显)。当我必须解决创建透视表的问题时,我使用三步流程(可选的第四步)来解决它:
选择感兴趣的列,即y值和x值
用额外的列扩展基表——每个x值一列
分组并聚合扩展表——每个y值一个组
(可选)美化聚合表
让我们将这些步骤应用于您的问题,看看我们得到了什么:
步骤1:选择感兴趣的列。在期望的结果中,
hostid
提供y值和itemname
提供x值。步骤2:用额外的列扩展基表。通常每个x值需要一列。回想一下,我们的x值列是
itemname
:请注意,我们没有更改行数--只是添加了额外的列。还要注意
NULL
s—与itemname = "A"
新列的值为非空A
,以及其他新列的空值。步骤3:对扩展表进行分组和聚合。我们需要
group by hostid
,因为它提供y值:(请注意,现在每个y值有一行。)好的,我们就快到了!我们只需要除掉那些丑陋的东西
NULL
s。第四步:美化。我们只是将所有空值替换为零,这样结果集更易于查看:
我们完成了——我们用mysql构建了一个漂亮的透视表。
应用此程序时的注意事项:
在额外列中使用什么值。我曾经
itemvalue
在这个例子中在额外的列中使用什么“中性”值。我曾经
NULL
,但也可能是0
或者""
,取决于你的具体情况分组时要使用的聚合函数。我曾经
sum
,但是count
以及max
也经常使用(max
通常在构建一行“对象”时使用(对象已分布在多行中)对y值使用多列。这个解决方案并不局限于对y值使用单个列——只需将额外的列插入到
group by
条款(别忘了select
(他们)已知限制:
此解决方案不允许透视表中有n列—在扩展基表时,需要手动添加每个透视列。所以对于5或10个x值,这个解决方案很好。100块,不太好。有些解决方案使用存储过程生成查询,但它们很难看,而且很难获得正确的结果。当透视表需要有很多列时,我目前还不知道有什么好方法来解决这个问题。
prdp8dxp2#
我找到了一种方法,可以使用简单的查询使报表的行到列的转换几乎是动态的。你可以在这里在线查看和测试。
查询的列数是固定的,但值是动态的,并且基于行的值。您可以构建它,因此,我使用一个查询构建表头,另一个查询查看值:
你也可以总结一下:
测试结果:
http://rextester.com/zswks28923
作为一个实际使用的例子,本报告在下面的列中显示了船/巴士的出发时间和到达时间,并提供了一个可视化的时间表。您将看到最后一列中未使用的另一列,而不会混淆可视化效果:
**网上售票系统
nzrxty8p3#
aurhwmvo4#
这不是确切的答案,你正在寻找,但这是一个解决方案,我需要在我的项目,希望这有助于某人。这将列出由逗号分隔的1到n行项目。在mysql中,groupconcat使这成为可能。
这个公墓有两个公共名称,因此名称列在不同的行中,由一个id连接,但有两个名称id,查询产生如下结果
墓地ID 墓地名称 纬度
1 阿普尔顿,萨弗斯普林斯 35.4276242832293
6yjfywim5#
使用子查询
但如果子查询产生的结果多于一行,则会出现问题,请在子查询中使用进一步的聚合函数
soat7uwm6#
另一个选择,如果您有许多需要透视的项,那么让mysql为您构建查询尤其有用:
fiddle添加了一些额外的值来查看它的工作情况
GROUP_CONCAT
有一个默认值1000,所以如果您有一个非常大的查询,请在运行它之前更改此参数测试:
bxpogfeg7#
我很抱歉这么说,也许我没有完全解决你的问题,但postgresql比mysql早10年,与mysql相比非常先进,有很多方法可以轻松实现这一点。安装postgresql并执行此查询
那你瞧!这里有大量的文档:postgresql:documentation:9.1:tablefunc或这个查询
再说一遍,瞧!postgresql:文档:9.0:hs存储
wljmcqd88#
我从子查询中编辑agung sagita的答案以加入。我不确定这两种方式之间有多大的区别,但仅供参考。
ac1kyiln9#
如果你能用mariadb有一个非常简单的解决方案。
自从mariadb-10.02以来,添加了一个名为connect的新存储引擎,它可以帮助我们将另一个查询或表的结果转换为pivot表,就像您想要的那样:您可以查看文档。
首先安装连接存储引擎。
现在表的透视列是
itemname
每个项目的数据位于itemvalue
列,因此我们可以使用此查询获得结果透视表:现在我们可以从
pivot_table
:更多详情请点击此处
xmq68pz910#
我把它变成
Group By hostId
然后它将只显示第一行的值,比如:
um6iljoc11#
我的解决方案:
它在提交的案例中产生预期的结果。
gajydyqb12#
利用matt fenwick帮助我解决问题的想法(非常感谢),让我们将其简化为一个查询: