报表分组通用 sql 解决方案已出

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1467 天前的主题，其中的信息可能已经有所发展或是发生改变。

接上一主题 https://www.v2ex.com/t/719136

通过 dba 的苦思冥想，sql 已经出来了，大家有这方面的可以参考下 sql 效果图见下，懂的人已懂

https://vkceyugu.cdn.bspapp.com/VKCEYUGU-imgbed/9a586ec3-41ea-4190-9b03-37ceb074c486.png

行分组字段 row1，row2，列分组字段 column1，column2，column1 有 012 三种状态，column2 有 01 两种状态，sql 可以呈现 column1，column2 所有组合的值并小计总计，而且可以随意扩展，sql 也是很简洁，稍微变换下可以解决行的分组，可以完美呈现类似下面的报表

https://vkceyugu.cdn.bspapp.com/VKCEYUGU-imgbed/0dfbb436-d5b1-493e-b0d9-2918aecd145e.png 不用自己再逻辑处理数据，动态分组处理逻辑代码简直写的崩溃，不信的人可以去试下

17 条回复 • 2020-11-02 16:15:09 +08:00

madworks

2020-11-02 10:56:38 +08:00

建表 sql
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `xx`;
CREATE TABLE `xx` (
`id` int(11) NOT NULL,
`fee` int(255) DEFAULT NULL,
`column1` varchar(255) DEFAULT NULL,
`column2` varchar(255) DEFAULT NULL,
`row1` varchar(255) DEFAULT NULL,
`row2` varchar(255) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `xx` VALUES ('1', '454', '0', '1', '李', '湖北');
INSERT INTO `xx` VALUES ('2', '35', '1', '0', '王', '上海');
INSERT INTO `xx` VALUES ('3', '23', '1', '1', '李', '上海');
INSERT INTO `xx` VALUES ('4', '567', '1', '0', '李', '上海');
INSERT INTO `xx` VALUES ('5', '43', '0', '2', '王', '湖北');
INSERT INTO `xx` VALUES ('6', '456', '0', '1', '李', '河南');

zoharSoul

2020-11-02 10:57:06 +08:00

听起来就是行转列?

madworks

2020-11-02 10:57:12 +08:00

实现 sql
SELECT
row1,
row2,
IFNULL(column1,'总计'),
IFNULL(column2,'小计'),
sum(fee) fee
FROM
(
SELECT
row1,
row2,
column1,
column2,
fee
FROM
(
SELECT
*
FROM
(
SELECT
x.row1,
x.row2,
y.column1,
y.column2,
0 AS fee
FROM
(
SELECT
row1,
row2
FROM
xx
GROUP BY
row1,
row2,
column1,
column2
) x,
(
SELECT
*
FROM
(
SELECT
0 AS column1
UNION
SELECT
1 AS column1
) AS t1
LEFT JOIN (
SELECT
0 AS column2
UNION
SELECT
1 AS column2
UNION
SELECT
2 AS column2
) AS t2 ON 1 = 1
) y
WHERE
(3 * y.column1 + y.column2) NOT IN (
SELECT
3 * m.column1 + m.column2
FROM
xx m
WHERE
m.row1 = x.row1
AND m.row2 = x.row2
)
GROUP BY
row1,
row2,
column1,
column2
) n
UNION
SELECT
row1,
row2,
column1,
column2,
sum(fee) fee
FROM
xx
GROUP BY
row1,
row2,
column1,
column2
) temp
GROUP BY
row1,
row2,
column1,
column2
) AS temp
GROUP BY
row1,
row2,
column1,
column2 WITH ROLLUP

madworks

2020-11-02 11:00:20 +08:00

@zoharSoul，不是行转列，有 2 个行，2 个列，每行每列都有固定的几个状态，一些行列组合肯定没有记录，现在写个 sql 可以展现所有组合的值，没有记录的赋值 0，你怎么写？听起来很简单，但是实现起来还是比较难的

weizhen199

2020-11-02 11:06:53 +08:00

pivot 应该是可以的

还有 dba 帮忙写业务 sql 的，太羡慕。。。/哭

madworks

2020-11-02 11:12:16 +08:00

@weizhen199 是啊，pbi，tableau 这种写这个不要太简单，可惜不开源，不能搞在项目里，只能单独的搭建一套报表系统

madworks

2020-11-02 11:17:47 +08:00

@weizhen199 @zoharSoul 你们说的好像是一个，我了解下，我以为你说的 bi pivot

l00t

2020-11-02 12:05:11 +08:00

这样不还是存在你在原帖中自己评论的问题？值多了怎么办？打算写多长？

不如搞个字典表，直接关联上字典表，比你这么挨个 select 要简洁很多。而且字典表也方便管理这种状态值，甚至可以把中文说明都给直接带出来。

madworks

2020-11-02 13:53:14 +08:00

@l00t 哈哈，是这样的，多谢

madworks

2020-11-02 14:25:54 +08:00

SELECT
row1,
row2,
IFNULL(column1, '总计'),
IFNULL(column2, '小计'),
sum(fee) fee
FROM
(
SELECT
row1,
row2,
column1,
column2,
fee
FROM
(
SELECT
*
FROM
(
SELECT
x.row1,
x.row2,
y.column1,
y.column2,
0 AS fee
FROM
(
SELECT
row1,
row2
FROM
xx_index
GROUP BY
row1,
row2
) x,
(
SELECT
column1,
column2
FROM
xx_index
GROUP BY
column1,
column2
) y
WHERE
(3 * y.column1 + y.column2) NOT IN (
SELECT
3 * m.column1 + m.column2
FROM
xx m
WHERE
m.row1 = x.row1
AND m.row2 = x.row2
)
GROUP BY
row1,
row2,
column1,
column2
) n
UNION
SELECT
row1,
row2,
column1,
column2,
sum(fee) fee
FROM
xx
GROUP BY
row1,
row2,
column1,
column2
) temp
GROUP BY
row1,
row2,
column1,
column2
) AS temp
GROUP BY
row1,
row2,
column1,
column2 WITH ROLLUP

wysnylc

2020-11-02 14:29:54 +08:00

代码可能不会超过 10 行(lambda)

madworks

2020-11-02 14:43:32 +08:00

@wysnylc 太自信了

zifangsky

2020-11-02 15:29:14 +08:00

你这个 SQL 写得也太复杂了吧，先不说在数据量大的情况下执行速度怎么样，光是后期维护也非常困难吧。而且，一般来说复杂逻辑都是放在业务代码里手动实现，SQL 只做简单高效的查询就可以了

madworks

2020-11-02 16:04:59 +08:00

@zifangsky 这 sql 只是看起来长，不复杂啊，修改的话就套模板，很快的。执行速度的话只要行列分组不超过 5 个都在 1 分钟之类，这种报表用逻辑代码写才是噩梦，我写出来要几百行代码，各种递归，小计总计还要判断位置，没写过想象不到的复杂

madworks

2020-11-02 16:10:32 +08:00

@zifangsky 随便给你看下插入小计的位置的代码
public void getInsertPosition(int levelFlag, List<List<Integer>> groupOrderInfoNumCollection,
List<OrderInfo> orderInfoList, int len, List<List<Integer>> nodeIndexList, List<List<Integer>> nestOrderInfoRepeatCollection)
{
int insertPos = 0;
int position = 0;
int[] listLen = new int[levelFlag];

if (levelFlag >= 1)
{
int[] index = new int[levelFlag];
for (int i = 0; i < levelFlag - 1; i++)
{
index[i] = groupOrderInfoNumCollection.get(i + 1).get(0);
}
for (Integer integer : groupOrderInfoNumCollection.get(0))
{
position += len * integer;
orderInfoList.remove(position);
insertPos += integer;
nodeIndexList.get(0).add(insertPos);
if(integer == 1){
nestOrderInfoRepeatCollection.get(0).add(insertPos);
}
for (int i = 0; i < levelFlag - 1; i++)
{
if (insertPos == index[i])
{
nodeIndexList.get(i + 1).add(insertPos);
orderInfoList.remove(insertPos * len);
listLen[i]++;
if (listLen[i] < groupOrderInfoNumCollection.get(i + 1).size())
{
index[i] += groupOrderInfoNumCollection.get(i + 1).get(listLen[i]);
}
}
}
}
if(orderInfoList.size() > 1){
orderInfoList.remove(orderInfoList.size() - 1);
}
}
}

madworks

2020-11-02 16:14:26 +08:00

把结果集整理成需要的形式的代码
public void resultSetHandler(int rowLen, int levelFlag, Map<String, Object> map,
Map<String, Object> rowGroupMap, String[] rowParamArr,
List<List<OrderInfo>> nestOrderInfoCollection, int len, int i)
{
if (rowLen >= 1)
{
List<List<Integer>> temp = new ArrayList<>();
levelFlag--;
map.remove(rowParamArr[rowLen]);
rowGroupMap.remove(rowParamArr[rowLen]);
nestOrderInfoCollection.get(i).addAll(performanceService.getOrderInfo(map));
deleteNodeTotal(nestOrderInfoCollection.get(i), len, rowGroupMap, rowParamArr, levelFlag,temp,temp);
rowLen--;
i++;
resultSetHandler(rowLen, levelFlag, map, rowGroupMap, rowParamArr, nestOrderInfoCollection, len, i);
}
}

madworks

2020-11-02 16:15:09 +08:00

删除节点统计的代码
public void deleteNodeTotal(List<OrderInfo> orderInfoList, int len,
Map<String, Object> rowGroupMap, String[] rowParamArr, int levelFlag, List<List<Integer>> nestOrderInfoIndexCollection, List<List<Integer>> nestOrderInfoRepeatCollection)
{

List<List<Integer>> groupOrderInfoNumCollection = new ArrayList<List<Integer>>();
for (int i = 0; i < levelFlag; i++)
{
nestOrderInfoIndexCollection.add(new ArrayList<Integer>());
nestOrderInfoRepeatCollection.add(new ArrayList<Integer>());
groupOrderInfoNumCollection.add(new ArrayList<Integer>());
}

if (rowParamArr.length > 1)
{

getGroupOrderInfoNum(levelFlag, rowGroupMap, rowParamArr, groupOrderInfoNumCollection);

if (levelFlag >= 1)
{
getInsertPosition(levelFlag, groupOrderInfoNumCollection, orderInfoList, len, nestOrderInfoIndexCollection, nestOrderInfoRepeatCollection);
}
}

}