SQLite学习系列：深入子查询表技巧（第二部分）

sqlite日常学习之子查询

表一原表

表二结果表

SELECT 指标,group_concat(kh,',') as 客户变化

from(SELECT 分组,指标,

sum(本期金额) - sum(上期金额) AS 差,

abs(sum(本期金额) - sum(上期金额) ) AS ju差,

row_number() over (partition by 指标 order by abs(sum(本期金额) - sum(上期金额) )desc) as 排名,

名称 || "" || CASE WHEN (sum(本期金额) - sum(上期金额) ) >= 0 THEN '增加' ELSE '减少' END || "" || CASE WHEN length(round(abs(sum(本期金额) - sum(上期金额) ) ) ) >= 10 THEN round( (sum(本期金额) - sum(上期金额) ) / 10000000, 1) || "" || '亿元（' || "" || 机构 || "" || '）' ELSE round( (sum(本期金额) - sum(上期金额) ) / 10000, 1) || "" || '万元（' || "" || 机构 || "" || '）' END AS kh

FROM (

SELECT 科目 || "" || 变动 || "" || 机构 || "" || 名称 AS 分组,

科目 || "" || 变动 AS 指标,

CASE WHEN 日期 = "20240131" THEN 金额 ELSE "0" END AS 本期金额,

CASE WHEN 日期 = "20230131" THEN 金额 ELSE "0" END AS 上期金额,科目,变动,机构,名称,日期

FROM 花名册

WHERE 金额 <> 0

ORDER BY 科目,变动,机构,名称)t

GROUP BY 指标,分组)y

WHERE 排名

GROUP BY 指标