postgresql高级应用之合并单元格的思路详解

1.写在前面✍

继上一篇postgresql高级应用之行转列&汇总求和之后想更进一步做点儿复杂的(?D表?呵也徽?哈😂)，当然作为?蟊恚?霈F最多的?o非就是合并?卧?窳耍?堑模?乙呀?迫不及待啦😎~

2.思考

首先，我??的?海中???有一???η岸?code>table有一??大致的了解， ?然??Ψ乔岸说耐?W十分的不友好，如果您?L???以下内容存在困?的?(前端html、javascript) 可就此打住哈。。。
enn...,?我先稍稍解?下前端 html 的表格格式吧😀

2.1 前端`html`->`table`基本???

先?出一??十分base的html demo.html

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8" />
	<title>demo</title>
</head>
<body>
  <!-- ??定?了????傩 border:定?表格?框 cellpadding:定??卧?翊笮 -->
  <table border="3"  cellpadding="8">
	<thead>
		<tr>
			<th>表?1</th><th>表?2</th><th>表?3</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>第一行第1??</td><td>第一行第2??</td><td>第一行第3??</td>
		</tr>
		<tr>
			<!-- 使用colspan?傩赃M行?M向合并，?M向被合并的?卧?裎恢眯枰?v出? -->
			<!-- 以下?M向合并????卧?瘢??缘诙??td?撕?就不要??了，否?t??绯雠? -->
			<td colspan="2">?M向合并了????卧??lt;/td><td>第二行第3??</td>
		</tr>
		<tr>
			<td>第三行第1??</td><td>第三行第2??</td><td>第三行第3??</td>
		</tr>
		<tr>
			<!-- 使用rowspan?傩赃M行?向合并，?向合并的(跨越的)?卧?裎恢眯枰?v出? -->
			<!-- 以下?向合并三?????卧??在本行最後一???撕?)，所以下?尚械淖钺???td?撕?就不要??啦~，否?t同???绯雠? -->
			<td>第四行第1??</td><td>第四行第2??</td><td rowspan="3">?向合并了三???卧??lt;/td>
		</tr>
		<tr>
			<td>第五行第1??</td><td>第五行第2??</td>
		</tr>
		<tr>
			<td>第六行第1??</td><td>第六行第2??</td>
		</tr>
	</tbody>
  </table>
</body>
</html>

?g?器渲染出??使用?g?器打?html文件)的?幼邮沁@?拥?

以上??就是colspan????M向合并?卧?瘢?code>rowspan????向合并?卧??

呃嗯，既然我??知道了html需要?????傩灾?也就是合并的行?祷蚝喜⒌牧??,那麽就是要在sql中生成??????抵等会崽峁┙o前端的同?W使用哈，?是?\?右馑迹?趋嵘?右馑际鞘谗崮兀浚浚咳菸蚁胂肟础！！?/p>

???M向合并?卧??/li>

需要使用 case+when+then ?句判?嗍欠裥枰?M向合并(重要的是要?出?M向合并的?抵?，??酉胧呛侠淼模?赡茉斐傻睦?_可能是??幼??斐?code>sql冗?（?然也是不得已而??之），?然本?就不再??M向合并?卧?窭?/p>

??犊v向合并?卧??/li>

step1.👉 如果使用聚合+窗口函??/code>?碛?算需要合并的相同的列?担?赡茉斐傻???是生成的rowspan??断嗤??碚h?抵凳且?拥模ㄈ缦?D），??硬豢梢?G~



step2.👉 。。。既然可以通?step1生成窗口内合并??档?抵担??然也可以通?窗口函??砩?梢??倒排序列的列,哈哈😊，你似乎??了什麽~~~，?Γ?⒋翱诤喜⒖??档牧信c窗口内倒排序的列做等值判?啵?嗟鹊牟痪褪堑谝??合并?底至辛恕！！?ingo

倒排序的窗口列

求?的列+倒排序的列

step3.👉 既然我??能做一?????械暮喜ⅲ?材茏???列的合并(也可以是一??二?列，注意 order by ??喜⑿械挠绊?哦），????胃饕??????影?b後的合并?抵盗猩?桑?⒁庀?D的?色部分哦😉

光?不?假把式，通?一下?y?脚本????#128526;~
2.2表结构

drop table if EXISTS  report2 ;
CREATE TABLE report2 (
  "id" varchar(10) primary key,
  "name" varchar(50),
  "price" numeric,
  "level2" varchar(50) ,
  "level1" varchar(50)
);

2.3表字段注释

    
        
            字段
            注释
        
    
    
        
            id
            主键
        
        
            name
            商品名称
        
        
            price
            价格
        
        
            level2
            二级分类
        
        
            level1
            一级分类
        
    

2.4表数据

INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0015', '洗发露', '36', '洗护', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0008', '香皂', '17.5', '洗护', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0007', '薯条', '7.5', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0009', '方便面', '3.5', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0004', '辣条', '5.6', '垃圾食品', '零食');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0006', 'iPhone X', '9600', '小电器', '电器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0003', '手表', '1237.55', '小电器', '电器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0012', '电视', '3299', '大电器', '电器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0016', '洗衣机', '4999', '大电器', '电器');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0013', '围巾', '93', '配饰', '服装配饰');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0017', '特步凉鞋', '499', '鞋子', '服装配饰');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0001', 'NIKE新款鞋', '900', '鞋子', '服装配饰');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0002', '外套', '110.9', '上衣', '服装配饰');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0014', '作业本', '1', '纸张', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0005', '铅笔', '7', '笔', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0010', '水杯', '27', '餐饮', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0011', '毛巾', '15', '洗护', '日用品');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0018', '绘图笔', '15', '笔', '文具');
INSERT INTO "report2"("id", "name", "price", "level2", "level1") VALUES ('0019', '汽水', '3.5', '其它', '零食');

3.🎉?果集最?求解

select 
  t1.*,
  case when  t_rank=t_count then t_count else null end as level1_row,
  case when  tu_rank=tu_count then tu_count else null end as level2_row
from 
(
    select 
      *,
      row_number() over(PARTITION by level1 order by level1 asc) t_rank,
      count(1) over (partition by level1) t_count,
      row_number() over(PARTITION by level1,level2 order by level1,level2 asc) tu_rank,
      count(1) over (partition by level1,level2) tu_count
    from report2 order by level1
) t1 order by t1.level1,t_rank desc,t_count desc,tu_rank desc,tu_count desc;

_?色_部分即?榍岸送??枰?暮喜?抵倒?#129392;~

如果你能看懂以上???及求解的 sql ,恭喜你又升?啦😂