给你一个网站怎么做,学做网站的书哪些好,贵阳网站建设是什么意思,做爰的最好看的视频的网站不知道Oracle怎么进行数据分栏(分栏: 因数据列过长, 部分数据作为新列显示). 在这里先记录一下粗浅的查询方法. 数据源例子: select 日用百货 as cat, 手电筒 as name, 20 as amount, 2024-01-27 as dt from dualunion allselect 餐饮美食 as cat, 鸡公煲 as name, 15.9 as amo… 不知道Oracle怎么进行数据分栏(分栏: 因数据列过长, 部分数据作为新列显示). 在这里先记录一下粗浅的查询方法. 数据源例子: select 日用百货 as cat, 手电筒 as name, 20 as amount, 2024-01-27 as dt from dualunion allselect 餐饮美食 as cat, 鸡公煲 as name, 15.9 as amount, 2024-01-27 as dt from dualunion allselect 餐饮美食 as cat, 海带粉 as name, 6 as amount, 2024-01-27 as dt from dualunion allselect 日用百货 as cat, 垃圾桶 as name, 9.9 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 大铁锅 as name, 66 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 电火锅 as name, 216 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 电饭锅 as name, 166 as amount, 2024-01-26 as dt from dualunion allselect 餐饮美食 as cat, 老乡鸡 as name, 19.9 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 塑料小板凳 as name, 8 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 垃圾袋 as name, 7.5 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 塑料靠背凳 as name, 10 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 鞋刷 as name, 4 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 撑衣杆 as name, 8.5 as amount, 2024-01-26 as dt from dualunion allselect 餐饮美食 as cat, 海带粉 as name, 6 as amount, 2024-01-26 as dt from dual思路: 创造提取新列的条件, 然后进行关联查询
with t as (select 日用百货 as cat, 手电筒 as name, 20 as amount, 2024-01-27 as dt from dualunion allselect 餐饮美食 as cat, 鸡公煲 as name, 15.9 as amount, 2024-01-27 as dt from dualunion allselect 餐饮美食 as cat, 海带粉 as name, 6 as amount, 2024-01-27 as dt from dualunion allselect 日用百货 as cat, 垃圾桶 as name, 9.9 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 大铁锅 as name, 66 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 电火锅 as name, 216 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 电饭锅 as name, 166 as amount, 2024-01-26 as dt from dualunion allselect 餐饮美食 as cat, 老乡鸡 as name, 19.9 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 塑料小板凳 as name, 8 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 垃圾袋 as name, 7.5 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 塑料靠背凳 as name, 10 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 鞋刷 as name, 4 as amount, 2024-01-26 as dt from dualunion allselect 日用百货 as cat, 撑衣杆 as name, 8.5 as amount, 2024-01-26 as dt from dualunion allselect 餐饮美食 as cat, 海带粉 as name, 6 as amount, 2024-01-26 as dt from dual
)
, tmd as (select p.*, ceil(p.rn/3) as dv, mod(p.rn, 3) as mdfrom (select row_number() over (partition by cat order by dt,amount desc) as rn, t.*from t) p
)
select t1.cat, t1.name as name1,t1.amount as amount1, t2.name as name2,t2.amount as amount2, t3.name as name3,t3.amount as amount3
from (select * from tmd where md1) t1
left join (select * from tmd where md2) t2 on t1.catt2.cat and t1.dvt2.dv
left join (select * from tmd where md0) t3 on t1.catt3.cat and t1.dvt3.dv
order by t1.cat,t1.dv,t1.md
;查询结果:
CATNAME1AMOUNT1NAME2AMOUNT2NAME3AMOUNT3日用百货电火锅216电饭锅166大铁锅66日用百货塑料靠背凳10垃圾桶9.9撑衣杆8.5日用百货塑料小板凳8垃圾袋7.5鞋刷4日用百货手电筒20NULLNULLNULLNULL餐饮美食老乡鸡19.9海带粉6鸡公煲15.9餐饮美食海带粉6NULLNULLNULLNULL 后面再找时间研究吧. (2024-01-27)
