SQL(HSQLDB)查询以在LibreOffice Base中创建等效的数据透视表 [英] SQL (HSQLDB) query to create a pivot table equivalent in LibreOffice Base

问题描述

我正在使用LibreOffice Base中的数据库，并尝试输出等效的数据透视表.据我了解，Base使用的HSQL不支持数据透视，但是您可以使用它编写的报告对我来说是完美的，所以我要坚持使用它.

I'm working on a database in LibreOffice Base and trying to output the equivalent of a pivot table. Base uses HSQL which, I understand, doesn't support pivot, but the reports you can make with it are perfect for me so I want to stick with it.

我想在我的数据透视表中涉及三列: Rotation ， Modality 和 Number .

There are three columns I want to involve in my pivot: Rotation, Modality and Number.

以下是当前数据外观的一个示例:

Here is an example of how the data looks at present:

Rotation    |   Modality    |   Number
1           |   1           |   5
1           |   2           |   3
1           |   3           |   4
2           |   1           |   6
2           |   1           |   5
2           |   3           |   2
3           |   1           |   1
3           |   2           |   4

如您所见，共有三种模式.您可以有任意数量的模态-每次旋转为零或更多.

As you can see, there are three modalities. You can have any number of modalities - zero or more per rotation.

我要做什么(每次旋转的每个模态的总和):

And what I'm after (the sum of each modality per rotation):

Rotation    |   Modality 1  |   Modality 2  |   Modality 3
1           |   5           |   3           |   4
2           |   11          |   0           |   2
3           |   1           |   4           |   0

(相反，我对 Modality 和 Rotation 同样满意.)我进行了很多搜索，并提出了两种我认为的方法我应该能够实现:使用 CASE WHEN 并使用子查询.

(I'd be equally happy with Modality and Rotation the other way around.) I've searched around a lot and have come up with two ways I think I should be able to achieve this: using CASE WHEN and using subqueries.

尝试 CASE何时

Trying CASE WHEN

这是我尝试过的:

SELECT   "Rotation"
   ,CASE "Modality" WHEN 1 THEN SUM( "Number" ) END
   ,CASE "Modality" WHEN 2 THEN SUM( "Number" ) END
   ,CASE "Modality" WHEN 3 THEN SUM( "Number" ) END
FROM     "my database"
GROUP BY "Rotation"
ORDER BY "Rotation"   

此查询返回的表类似于上面的表，除了不显示每个模态的总和，而是在一个(看似)随机列中显示该轮换中所有模态的总和，如下所示:

This query returns a table that looks like the one above except instead of showing the sum of each modality, it shows the sum for all modalities in that rotation in one (seemingly) random column, like this:

Rotation    |   Modality 1  |   Modality 2  |   Modality 3
1           |               |               |   12
2           |               |               |   13
3           |   5           |               |   

所以查询的 SUM()组件出了点问题，我只是弄不清楚是什么.

So something is wrong with the SUM() component of the query, I just can't figure out what.

尝试子查询

SELECT  "Modality"
   ,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 1 AND "Modality" = 1 )
   ,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 2 AND "Modality" = 1 )
   ,( SELECT SUM( "Number" ) FROM "my database" WHERE "Rotation" = 3 AND "Modality" = 1 )
FROM "my database"
WHERE "Modality" = 1
GROUP BY "Modality"

会产生:

Modality    |   Rotation 1  |   Rotation 2  |   Rotation 3
1           |   5           |   11          |   1

现在显然由于"WHERE"子句，它仅返回模态1，但我不知道如何以这种子查询方式显示所有模态.子查询代码的运行速度也比 CASE WHEN 慢，这让我想知道这是否是错误的处理方法.

Now obviously it only returns modality 1 due to the 'WHERE' clauses, but I can't figure out how to get all modalities displayed this subquery way. The subquery code also runs way slower than the CASE WHEN, which makes me wonder if it's the wrong way to go about this.

推荐答案

您的查询已关闭，您只需将 case 语句放在总和 statement 内即可执行条件聚合:

Your query was close, you just need to put the case statement inside of the sum statement to perform conditional aggregation:

SELECT   Rotation
   , SUM(CASE WHEN Modality = 1 THEN Number END ) 
   , SUM(CASE WHEN Modality = 2 THEN Number END ) 
   , SUM(CASE WHEN Modality = 3 THEN Number END ) 
FROM     yourtable
GROUP BY Rotation
ORDER BY Rotation  

这篇关于SQL(HSQLDB)查询以在LibreOffice Base中创建等效的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！