字段 | 注释 |
---|---|
id | 主键 |
name | 商品名称 |
price | 价格 |
level2 | 二级分类 |
level1 | 一级分类 |
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', '其它', '零食');
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;
_紅色_部分即為前端童鞋需要的合并數值哈🥰~
如果你能看懂以上問題及求解的 sql
,恭喜你又升級啦😂
總結下::對問題的分析✨
以及對問題求解的思考🤔
很重要嘛,當然還包含對postgresql
所提供工具的靈活使用 👉 總會產生意想不到的驚喜,哈哈😘~
到此这篇关于postgresql高级应用之合并单元格的思路详解的文章就介绍到这了,更多相关postgresql合并单元格内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!