AlaSql是否支持分组功能？

Question

*对不起，我犯了一些错误，粗鲁的举止。

现在我试着用AlaSql制作销售汇总报表，代码如下：

​

     var testData = [
    { Area: "Area 1", Shop: "Shop 1", Val: 5 },
    { Area: "Area 1", Shop: "Shop 2", Val: 20 },
    { Area: "Area 2", Shop: "Shop 1", Val: 25 },
    { Area: "Area 2", Shop: "Shop 2", Val: 40 }
    ];
    
    
    res = alasql( 'SELECT CASE WHEN \
    GROUPING(Area) = 1 THEN "ALL"  ELSE MAX(Area) END AS Area,  \
    CASE WHEN GROUPING(Area) = 1 THEN "TOTAL" \
    GROUPING(Shop) = 1 THEN "SUBTOTAL" ELSE MAX(Shop) END AS Shop,\
    SUM(Val) AS Val  \
    INTO HTML("#res1",{headers:true}) FROM ? \
    GROUP BY ROLLUP(Area,Shop)', [testData]);

table {border:1px solid black}

<script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
<p>ROLLUPTEST()</p><div id="res1"></div>

​

我希望显示具有价值摘要的表格。

{ Area: "Area 1", Shop: "Shop 1"  , Val: 5 },
{ Area: "Area 1", Shop: "Shop 2"  , Val: 20 },
{ Area: "Area 1", Shop: "SUBTOTAL", Val: 25 },
{ Area: "Area 2", Shop: "Shop 1"  , Val: 25 },
{ Area: "Area 2", Shop: "Shop 2"  , Val: 40 },
{ Area: "Area 2", Shop: "SUBTOTAL", Val: 65 },
{ Area: "ALL"   , Shop: "TOTAL"   , Val: 90 },

但是当运行它时，调试器显示"Script error“。并且在浏览器中不显示任何表。

我猜AlaSql不支持分组功能。如果有人知道这件事，请告诉我。

Answer 1

否，AlaSQL仅在另一个上下文中知道GROUPING keyword，即在GROUPING SETS中：

GROUP BY GROUPING SETS(Area, Shop)

如果您的Area和Shop字段不为空，则可以按如下方式解决此问题：

SELECT   CASE WHEN Area IS NULL THEN "ALL" ELSE Area END AS Area, 
         CASE WHEN Shop IS NULL THEN 
            CASE WHEN Area IS NULL THEN "TOTAL" ELSE "SUBTOTAL" END 
            ELSE Shop
         END as Shop,
         SUM(Val) AS Val 
INTO     HTML("#res1", {headers:true}) 
FROM     ?
GROUP BY ROLLUP(Area,Shop)