Sql编写实例：
一、行变列。
create or replace view v_account_detail asselect t.project_id, t.pay_year, t.pay_quart, t.pay_month, (case t.catalog when '工程施工费' then decode(sum(amount), null, 0,sum(amount)) end) as engineer_cost, (case t.catalog when '设备费' then decode(sum(amount), null, 0,sum(amount)) end) as equip_cost, (case t.catalog when '前期工作费' then decode(sum(amount), null, 0,sum(amount)) end) as pre_cost, (case t.catalog when '工程监理费' then decode(sum(amount), null, 0,sum(amount)) end) as super_cost, (case t.catalog when '拆迁补偿费' then decode(sum(amount), null, 0,sum(amount)) end) as remove_cost, (case t.catalog when '竣工验收费' then decode(sum(amount), null, 0,sum(amount)) end) as checke_cost, (case t.catalog when '业主管理费' then decode(sum(amount), null, 0,sum(amount)) end) as owner_cost from (select a.project_id, '工程施工费' as catalog, to_number(to_char(t.pay_date, 'YYYY')) as pay_year, to_number(to_char(t.pay_date, 'Q')) as pay_quart, to_number(to_char(t.pay_date, 'mm')) as pay_month, amount from pay_pre_cost_payment t left join pay_pre_cost_apply a on a.apply_bill_id = t.apply_bill_id union all select a.project_id, '工程施工费' as catalog, to_number(to_char(t.pay_date, 'YYYY')) as pay_year, to_number(to_char(t.pay_date, 'Q')) as pay_quart, to_number(to_char(t.pay_date, 'mm')) as pay_month, amount from pay_proc_cost_payment t left join pay_proc_cost_apply a on a.apply_bill_id = t.apply_bill_id union all select a.project_id, '工程施工费' as catalog, to_number(to_char(t.pay_date, 'YYYY')) as pay_year, to_number(to_char(t.pay_date, 'Q')) as pay_quart, to_number(to_char(t.pay_date, 'mm')) as pay_month, amount from pay_comp_cost_payment t left join pay_comp_cost_apply a on a.apply_bill_id = t.apply_bill_id union all select t.project_id, cost_catalog as catalog, to_number(to_char(t.pay_date, 'YYYY')) as pay_year, to_number(to_char(t.pay_date, 'Q')) as pay_quart, to_number(to_char(t.pay_date, 'mm')) as pay_month, amount from pay_other_cost_payment t) t group by t.project_id,t.catalog, t.pay_year, t.pay_quart, t.pay_month;
?知识点：1）view 为视图。
???????????? 2）union all 将 查询结果合并（含重复的数据），union 排除重复的。列名可以不同，列的个数必须相同。
???????????? 3）group by 除了查询出来的可以求和的sum值以外的所有列作为条件分组。
???????????? 4)? left join? on 左外联，左边表的数据全部显示，不管右边表有没有对应的符合条件的数据
???????????? 5）to_number(to_char(t.pay_date, 'Q')) as pay_quart 取季度，取数值类型。
?????????????6）行变列，将行中 某一属性作为列?(case t.catalog
???????? when '工程监理费' then
????????? decode(sum(amount), null, 0,sum(amount))
?????? end) as super_cost,
二、表a、表b。a一对多b。俩表关联查询，由上向下查询。例子：显示a的信息同时显示b对应a 中id 相同项目的和。
?select a.id,a.name,(select sum(b.score) from b where b.id = a.id) as totalScorce from a