表A:pk为a1;
a1(工资项编号),a2(工资项名称),a3(工资类别)
-------------------------------------------
a001 基本工资 00
a002 饭补 00
a003 奖金 01
a004 绩效工资 01
a005 所得税 02 表B:pk为b1,a1;
a1(工资项编号),b1(编号), b2(金额)
-------------------------------------------
a001 b001 50
a002 b002 50
a003 b003 100
a005 b004 -40结果:注:a3(工资类别)只能有00,01,03这三种。类别一(00) 类别二 (02) 类别三(03)
----------------------------------------------------------------
基本工资50,饭补50 绩效工资100 所得税-40请问这个sql怎么写?
a1(工资项编号),a2(工资项名称),a3(工资类别)
-------------------------------------------
a001 基本工资 00
a002 饭补 00
a003 奖金 01
a004 绩效工资 01
a005 所得税 02 表B:pk为b1,a1;
a1(工资项编号),b1(编号), b2(金额)
-------------------------------------------
a001 b001 50
a002 b002 50
a003 b003 100
a005 b004 -40结果:注:a3(工资类别)只能有00,01,03这三种。类别一(00) 类别二 (02) 类别三(03)
----------------------------------------------------------------
基本工资50,饭补50 绩效工资100 所得税-40请问这个sql怎么写?
----------------------------------------------------------------
基本工资50,饭补50 绩效工资100 所得税-40这里的类别有些混乱吧?还有,类别二得到的结果是“奖金100”而不是“绩效工资100”,是么?要实现这个逻辑,重要的是使用以下2个函数:
sys_connect_by_path()和row_number()over()
剩下的自己做做看。
create or replace function GetString(gz_type varchar2) return varchar2 is
Result varchar2(2000);
v_Str varchar2(200);
cursor c_Str is
select A.a2||B.b2 from A , B
where A.a1=B.a1 and A.a3=gz_type;
begin
Result :=' ';
open c_table;
loop
fetch c_Str into v_Str;
exit when c_Str%NOTFOUND;
Result := Result||v_Str;
end loop;
close c_table;
return(Result);
end GetString;
2.select GetString('00') "类别一(00)",GetString('01') "类别二(02)",GetString('03') "类别三(03)" from dual
a b
1 1
1 2
1 3
2 1
2 2
3 1
如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 创建一个合并的函数 create function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果:
select distinct a ,dbo.f_rowtocol(a) from rowtocol
Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94想变成
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94declare @sql varchar(4000)
set @sql = 'select Name as ' + '姓名'
select @sql = @sql + ' , sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from rowtocol) as a
set @sql = @sql + ' from rowtocol group by name'
exec(@sql) 如果上述两表互相换一下:即
表名(cj)
姓名 语文 数学 物理
张三 73 83 93
李四 74 84 94想变成 Name Subject Result
张三 语文 73
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94select 姓名 as Name,'语文' as Subject,语文 as Result from CJ union
select 姓名 as Name,'数学' as Subject,数学 as Result from CJ union
select 姓名 as Name,'物理' as Subject,物理 as Result from CJ
order by 姓名 desc
表A:pk为a1;
a1(工资项编号),a2(工资项名称),a3(工资类别)
-------------------------------------------
a001 基本工资 00
a002 饭补 00
a003 奖金 01
a004 绩效工资 01
a005 所得税 02 表B:pk为b1,a1;
a1(工资项编号),b1(编号), b2(金额)
-------------------------------------------
a001 b001 50
a002 b002 50
a003 b003 100
a005 b004 -40结果:注:a3(工资类别)只能有00,01,02这三种。类别一(00) 类别二 (01) 类别三(02)
----------------------------------------------------------------
基本工资50,饭补50 绩效工资100 所得税-40请问这个sql怎么写?
------- ----------- --------------------
a001 基本工资 00
a002 饭补 00
a003 奖金 01
a004 绩效工资 01
a005 所得税 02SQL> select * from b;ITEM_NO ITEM_SUB_NO ITEM_CODE
------- ----------- --------------------
a001 b001 50
a002 b002 50
a003 b003 100
a005 b004 -40
select max(A) "类别一(00)",max(B) "类别二(01)",max(C) "类别三(02)" from(
select case when item_name = '00' then k end A,
case when item_name = '01' then k end B,
case when item_name = '02' then k end C
from
(
select item_name,substr(replace(max(sys_connect_by_path(code,',')),',',','),2) K
from
(
select t.*,row_number()over(partition by item_name order by item_name) rn
from
(select a.item_name,a.item_sub_no||b.item_code code from a,b where a.item_no=b.item_no) t
)
start with rn=1
connect by rn = prior rn+1
and item_name = prior item_name
group by item_name
)
)
;类别一(00) 类别二(01) 类别三(02)
-------------------------- -------------------------- ---------------------------------------
基本工资50,饭补50 奖金100 所得税-40
.....类别一(00) 类别二(01) 类别三(02)
-------------------- -------------------- --------------------
基本工资50,饭补50 奖金100 所得税-40SQL>
建议:后台代码判断,而非sql完成,这么复杂的逻辑分支搞个研究试试还可以,作实际项目有点本本了!
sum(decode(useid,2,1,0)) useid2,
sum(decode(useid,3,1,0)) useid3,
sum(decode(twouseid,1,1,0)) twouseid1,
sum(decode(twouseid,2,1,0)) twouseid2,
sum(decode(twouseid,3,1,0)) twouseid3
from table1
group by ordid
select name,sum(decode(course,'语文', grade,null)) "语文",
sum(decode(course,'数学', grade,null)) "数学",
sum(decode(course,'英语', grade,null)) "英语"
from student
group by name
--不定列转换
C1 C2
---------- ----------
1 我
1 是
1 谁
2 知
2 道
3 不
sum(decode(b2,'01', grade,null)) "类别二(01)",
sum(decode(b2,'02', grade,null)) "类别三(02)"
from A , B
where A.a1 = B.a1(+)
group A.a3
sum(decode(a3, '01', B.b2, null)) "类别二(01)",
sum(decode(a3, '02', B.b2, null)) "类别三(02)"
from A , B
where A.a1 = B.a1(+)
group A.a3