Sqlite：子总计在自己的行中，也称为“rollup”。

Question

我正在寻找一种模拟其他基于SQL的数据库管理系统中可用的"rollup"的方法。明确一点，我知道如何获得运行子总计，这不是我想要的。我也知道可以通过编程方式实现这一点。然而，如果可能的话，我宁愿使用一个（或几个）SQL语句来实现。

例如，对于这样的表：产品类型、订单金额、日期，我将寻求一份报告，显示按产品类型和日期分组的所有数据，其中在产品类型和日期变化时显示订单金额小计的行。

我受限于在Android上使用sqlite。我知道足够的SQL，可能会伤害数据库，但显然还不足以模拟Rollup，在这一点上，我认为这是不可能的。

感谢任何见解（即使是"不可能"）。

- TrustNoOne

Answer 1

这里有一种方法可以在sqlite下模拟rollup。我在一个2007年的数据库论坛上找到了一个关于小计和总计的答案。我将在此处概述我的简化情况。

我有一个包含Ticker_Symbol（文本），Underlying（文本）和Quantity（整数）等其他字段的股票表。这些对于本例足够。

可以通过使用来自sqlite的以下调用来模拟Rollup：

select Ticker_Symbol,Underlying,Quantity from (
select '1' orderCol, Ticker_Symbol,Underlying,Quantity from stocks 
union  
select '2' orderCol, Ticker_Symbol, 'Subtotal' as Underlying, Sum(Quantity) as Quantity from stocks 
group by Ticker_Symbol 
union 
select '99' orderCol, '_' as Ticker_Symbol, 'GrandTotal' as Underlying, sum(Quantity) as Quantity from stocks) 
as t1  order by case  when orderCol=99 then 1 else 0 end, Ticker_Symbol, orderCol;

这将产生类似以下的输出： |股票代码 |基础证券|数量| |-------------------|----------|--------| AAPL AAPL 500 AAPL AAPL 1000 AAPL AAPL 2000 AAPL Subtotal 3500 AAPL140222P00500000 AAPL 10 AAPL140222P00500000 Subtotal 10 IBM140322C00180000 IBM 25 IBM140322C00180000 Subtotal 25 R140222C00067500 R 10 R140222C00067500 Subtotal 10 VLCCF VLCCF 300 VLCCF VLCCF 2000 VLCCF Subtotal 2300 _ GrandTotal 5845

不幸的是，我找不到避免使用"股票代码"的方法。理想情况下，只需用"小计"(或"总计")替换当前的"股票代码"即可，但这并不起作用。还请注意使用"_"确保"GrandTotal"确实显示在最后一行。

我希望这能帮助到其他人，如果有人有更好的方法，请添加。

- TrustNoOne

Answer 2

我自己也遇到了同样的问题 - 在SQLite3中模拟subtotals，这是我的解决方案：

with counter(numm)as(select 1 union all select numm+1 from counter where numm<34),
str(par,node)as(select 1, numm from counter where numm in(2,5,8,11)union 
select 11, numm from counter where numm in(12)union 
select 12, numm from counter where numm in(13,17,20)union 
select 13, numm from counter where numm in(14,15,16)union 
select 17, numm from counter where numm in(18,19)union 
select 2, numm from counter where numm in(3,4)union 
select 20, numm from counter where numm in(21)union 
select 21, numm from counter where numm in(22,23)union 
select 5, numm from counter where numm in(6,7)union 
select 8, numm from counter where numm in(9,10)union 
select null, numm from counter where numm in(1)),
struct(par,node,clevel)as(select par,node,0 from str where par is null union all select c.par,c.node,s.clevel+1 from str c join struct s on s.node=c.par)/*struct*/,
namez(namee,node)as(select 'Grandtotal', numm from counter where numm in(1)union
select 'Subtotal1', numm from counter where numm in(2)union
select 'Subtotal2', numm from counter where numm in(5)union
select 'Subtotal3', numm from counter where numm in(8)union
select 'Subtotal4', numm from counter where numm in(11)union
select 'Subtotal5', numm from counter where numm in(12)union
select 'Subtotal6', numm from counter where numm in(13)union
select 'Subtotal7', numm from counter where numm in(17)union
select 'Subtotal8', numm from counter where numm in(20)union
select 'Subtotal9', numm from counter where numm in(21)union
select 'value10', numm from counter where numm in(18)union
select 'value11', numm from counter where numm in(19)union
select 'value12', numm from counter where numm in(22)union
select 'value2', numm from counter where numm in(4)union
select 'value3', numm from counter where numm in(6)union
select 'value4', numm from counter where numm in(7)union
select 'value5', numm from counter where numm in(9)union
select 'value6', numm from counter where numm in(10)union
select 'value7', numm from counter where numm in(14)union
select 'value8', numm from counter where numm in(15)union
select 'value9', numm from counter where numm in(16)union
select 'valueN', numm from counter where numm in(23)union
select 'vaule1', numm from counter where numm in(3)),
some_random_values(node,val)as(
select node,
case node 
  when 3 then 10 when 4 then 33 when 6 then 123 when 7 then 2 
  when 9 then 321 when 10 then 202 when 14 then 2 when 15 then 88 
  when 16 then 56 when 18 then 17 when 19 then 345 when 22 then 99 when 23 then 9 
  else 0 
end from str),
sval(par,node,val)as(select s.par,s.node,a.val from str s join some_random_values a on a.node=s.node),
recur(par,node,val)as(
select * from sval where par in(select par from str group by par having(node)>1)
union all
select b.pAR,b.node,a.val+b.val
from recur a join sval b on b.node = a.par)
select s.par,s.node,substr('                            ',1,s.clevel*5)||n.namee name,v.val
from struct s join namez n on n.node=s.node
join(select par,node,sum(val)val from recur group by 1,2)v on v.node=s.node
order by s.node

示例可能看起来有点复杂。主要部分以 recur(par,node,val) 开始。

在 SQLite 3.9.1 上运行正常。

- Al Zhar

Answer 3

我按照TrustNoOne的答案参考，以以下方式使用它。

    select securityname,bal,Total from (
select 1 orderCol,sectorname, '#' || COALESCE (sectorname,'') as securityname ,'' bal ,'' as Total from tablename 
group by sectorname  --Title
union 
select 2 orderCol,sectorname, securityname,bal,'' from tablename --Items
union  
select 3 orderCol,sectorname, sectorname || '- Subtotal :' as securityname ,'',Sum(bal) as Total from tablename 
group by sectorname --SubTotal
union 
select 99 orderCol,'_' sectorname, 'Grand Total :' as securityname ,'', sum(bal) as Total from tablename
group by 1,2,3 --GrandTotal
) 
 

证券名称	数量	总计
#第一组 :
项目1	50.0
项目2	30.0
第一组-小计		80
#第二组 :
项目1	10.0
第二组-小计		10
总计		90

- Thunder