LibreOfficeBase-使用SQL进行联合变量计算

Question

我有一个包含3列的表，例如Food、Type和Taste。

• 列Food可以有任何内容
• 列Type可能具有"水果"或"蔬菜"值
• 列Taste可能具有"Bad"、"Good"或"Delicious"值

我正试图得到一个SQL语句（到目前为止运气不佳），它将生成一个类似于的表

            Bad       Good    Delicious   Total
Fruit        05         09           16      20
Vegetables   12         20           03      35
Total        17         29           19      55

输出表上的数字是每个Type/Taste组合在表中有多少Food的计数。

如何做到这一点？

Answer 1

前两行可以用子查询子句在一个查询中完成：

SELECT X."Type", 
       SUM(X."Bad") AS "Bad",
       SUM(X."Good") AS "Good",
       SUM(X."Delicious") AS "Delicious",
       SUM(X."Total") AS "Total" 
FROM (SELECT "Type",
          CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") X 
GROUP BY "Type"

子查询创建列"Bad"、"Good"one_answers"Delicious"，每列中有一个1或0，然后查询的外部将所有值相加。

要获得底部的totals行，必须使用基本解析器不支持的UNION ALL。要使此查询工作，您需要打开Edit→Run SQL Directly选项。（这将禁用某些需要解析器的表单和报表功能-这可能对您的使用无关紧要，但仅供以后在表单或报表中使用此查询时参考。）

UNION ALL使用的查询基本上是相同的查询，只是不按类型分组。所以总的来说：

SELECT X."Type", 
       SUM(X."Bad") AS "Bad",
       SUM(X."Good") AS "Good",
       SUM(X."Delicious") AS "Delicious",
       SUM(X."Total") AS "Total" 
FROM (SELECT "Type",
          CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") X 
GROUP BY "Type"
UNION ALL
SELECT 'Total' AS "BottomTotal",
       SUM(Y."Bad"),
       SUM(Y."Good"),
       SUM(Y."Delicious"),
       SUM(Y."Total") 
FROM (SELECT CASEWHEN("Taste" = 'Bad',1,0) AS "Bad",
          CASEWHEN("Taste" = 'Good',1,0) AS "Good",
          CASEWHEN("Taste" = 'Delicious',1,0) AS "Delicious",
          1 AS "Total" FROM "YourTableName") Y 
GROUP BY "BottomTotal"

请确保始终将"YourTableName"替换为实际的表名。