t_info id name
1 华泰保险
2 新华保险
t_type id t_info_id type value
1 1 手续费 10.0
2 1 分保费 20.9
3 1 现金赔款 27.8
4 2 手续费 33.9
要求这样显示:
保险公司 手续费 分保费 现金赔款
华泰保险 10.0 20.9 27.8
新华保险 33.9 0 0
1 华泰保险
2 新华保险
t_type id t_info_id type value
1 1 手续费 10.0
2 1 分保费 20.9
3 1 现金赔款 27.8
4 2 手续费 33.9
要求这样显示:
保险公司 手续费 分保费 现金赔款
华泰保险 10.0 20.9 27.8
新华保险 33.9 0 0
FROM t_info a , t_type b
WHERE a.id=b.t_info_id
sum(case when b.type='分保费' then value end ) 分保费,
sum(case when b.type='现金赔款' then value end ) 现金赔款
from t_info a,t_type b
where a.id=b.t_info_id
create table t_info(id int,name nvarchar(20), nvarchar(100))insert into t_info
select 1,N'华泰保险',null
union
select 2,N'新华保险',nullcreate table t_type(id int,t_info_id int,type nvarchar(20),value decimal(18,1))insert into t_type
select 1,1,N'手续费',10.0
union
select 2,1,N'分保费',20.9
union
select 3,1,N'现金赔款',27.8
union
select 4,2,N'手续费',33.9select a.name,sum(case when b.type='手续费' then value end ) 手续费,
sum(case when b.type='分保费' then value end ) 分保费,
sum(case when b.type='现金赔款' then value end ) 现金赔款
from t_info a,t_type b
where a.id=b.t_info_id
group by a.name--华泰保险 10.0 20.9 27.8
--新华保险 33.9 NULL NULL
id type code
1 手续费 D1
2 分保费 D2
3 现金赔款 D3
4 手续费 D4t_value
id t_info_id typeid value
1 1 D1 10.0
2 1 D2 20.9
3 1 D3 27.8
4 2 D4 33.9 如果那个VALUE还需要在别的表查呢
SELECT RI_BILL_INFO_MA.concno "合同编号(concno)",
t_cont_main.C_CONT_NME "合同名称",
RI_BILL_INFO_MA.BILLOUT "账单号(BILLOUT)",
RI_BILL_INFO_MA.REINTR "接受公司(REINTR)",
RI_BILL_INFO_MA.REPAOB "实际收付款人(REPAOB)",
RI_BILL_INFO_MA.BILLPERIFROM "账单起期(BILLPERIFROM)" ,
RI_BILL_INFO_MA.BILLPERITO "账单止期(BILLPERITO)" ,
RI_BILL_INFO_MA.CURR "币种(CURR)",
c.feibao "分保费",
c.shouxu "手续费",
c.other "其他费用",
'账龄(不能直接取得,需要计算得出)',
'余额=保费—手续费—其他费用',
RI_BILL_INFO_MA.REPAPERI "付(收)费期别=期次?(REPAPERI)",
RI_BILL_INFO_MA.REPASTDA "付(收)款起期(REPASTDA)",
RI_BILL_INFO_MA.REPAENDDA "付(收)款止期(REPAENDDA)"
from RI_BILL_INFO_MA,t_cont_main,(SELECT RI_BILL_INFO_SUB.BILLMAID concno,
sum(case when T_FEE_TYPE.c_Fee_Nme = '分保费' then RI_BILL_INFO_SUB.LOCUREPAAM end) feibao,
sum(case when T_FEE_TYPE.c_Fee_Nme = '手续费' then RI_BILL_INFO_SUB.LOCUREPAAM end) shouxu,
sum(case when T_FEE_TYPE.c_Fee_Nme = '其他费用' then RI_BILL_INFO_SUB.LOCUREPAAM end) other
from T_FEE_TYPE,RI_BILL_INFO_SUB
where RI_BILL_INFO_SUB.FETY = T_FEE_TYPE.C_FEE_CDE
group by RI_BILL_INFO_SUB.BILLMAID) c
where RI_BILL_INFO_MA.CONCNO = t_cont_main.c_cont_no and RI_BILL_INFO_MA.reinmann='2' and RI_BILL_INFO_MA.REINTYPEBIG='1' AND RI_BILL_INFO_MA.BILLTYPE in(1,5,9)
and RI_BILL_INFO_MA.CONCNO=c.concno;
这样写对吗?查着查着就出错
invalid number 这样的错
select a.name,sum(case when b.type='手续费' then value end ) 手续费,
sum(case when b.type='分保费' then value end ) 分保费,
sum(case when b.type='现金赔款' then value end ) 现金赔款
from t_info a,t_type b
where a.id=b.t_info_id
group by a.name