是这样的我在SQL中查询出来了一些数据,不过这些数据需要转换成为要用的数据、拜托各位大佬了。没有多少积分。
数据库是Oracle。用的是plsqlselect bugUuid,
strbugUuid,
peopleType,
strPeopleType,
count(idCard) 人数,
sum(unitPrice) 价格
from (select t.budget_batchuuid bugUuid,
bd.batch_name strbugUuid,
t.id_card idCard,
sum(nvl(t.woman_num, t.man_num) * t.unit_price) unitPrice,
t.people_type peopleType,
pt.code_name strPeopleType
from budget_details_info t
left join budget_batch bd
on t.budget_batchuuid = bd.uuid
left join (select * from sys_code where parent_id = '2') pt
on pt.code_value = t.people_type
group by t.id_card,
t.budget_batchuuid,
t.people_type,
pt.code_name,
bd.batch_name
order by t.people_type ASC)
group by peopleType, strPeopleType, bugUuid, strbugUuid
order by strbugUuid ASC, peopleType ASC
数据库是Oracle。用的是plsqlselect bugUuid,
strbugUuid,
peopleType,
strPeopleType,
count(idCard) 人数,
sum(unitPrice) 价格
from (select t.budget_batchuuid bugUuid,
bd.batch_name strbugUuid,
t.id_card idCard,
sum(nvl(t.woman_num, t.man_num) * t.unit_price) unitPrice,
t.people_type peopleType,
pt.code_name strPeopleType
from budget_details_info t
left join budget_batch bd
on t.budget_batchuuid = bd.uuid
left join (select * from sys_code where parent_id = '2') pt
on pt.code_value = t.people_type
group by t.id_card,
t.budget_batchuuid,
t.people_type,
pt.code_name,
bd.batch_name
order by t.people_type ASC)
group by peopleType, strPeopleType, bugUuid, strbugUuid
order by strbugUuid ASC, peopleType ASC
你到底想展示在哪?网页?控制台?swing?问题都不问好,别人还要猜你想问的什么?
strbugUuid,
peopleType,
strPeopleType,
count(idCard) peopleCount,
sum(unitPrice) moneyCount
from (select t.budget_batchuuid budUuid,
bd.batch_name strbugUuid,
t.id_card idCard,
sum(nvl(t.woman_num, t.man_num) * t.unit_price) unitPrice,
t.people_type peopleType,
pt.code_name strPeopleType
from budget_details_info t
left join budget_batch bd
on t.budget_batchuuid = bd.uuid
left join (select * from sys_code where parent_id = '2') pt
on pt.code_value = t.people_type
group by t.id_card,
t.budget_batchuuid,
t.people_type,
pt.code_name,
bd.batch_name
order by t.people_type ASC)
group by peopleType, strPeopleType, budUuid, strbugUuid
order by strbugUuid ASC, peopleType ASC。
private String budUuid;//预算批次UUID
private String strbugUuid;//预算批次名称
private String peopleType;//着装分类
private String strPeopleType;//着装分类名称
private String peopleCount;//人员总数
private String moneyCount;//金额创建了这些个实体类。
c.人员名称,
sum((case when c.批次名称='16A' then c.ren ELSE 0 END)) as 16年人数,
sum((case when c.批次名称='16A' then c.jin ELSE 0 END)) as 16年金额,sum((case when c.批次名称='17A' then c.ren ELSE 0 END)) as 17年人数,
sum((case when c.批次名称='17A' then c.jin ELSE 0 END)) as 17年金额,
sum((case when c.批次名称='18A' then c.ren ELSE 0 END)) as 18年人数,
sum((case when c.批次名称='18A' then c.jin ELSE 0 END)) as 18年金额
from(select 人员名称 ,批次名称, sum(人数) as ren,sum(金额) as jin from rest GROUP BY 人员名称 ,批次名称) c GROUP BY 人员名称
先假设你的数据是在一张表中的
假设bugUuid值是16,17, 18, 其实你自己 截取一下前两位字符就行select t.strPeopleType as 人员类型,
MAX(decode(t.bugUuid,'16',idCard,null)) as 16年人数,
MAX(decode(t.bugUuid,'16',unitPrice,null)) as 16年价格,
MAX(decode(t.bugUuid,'17',idCard,null)) as 17年人数,
MAX(decode(t.bugUuid,'17',unitPrice,null)) as 17年价格,
MAX(decode(t.bugUuid,'18',idCard,null)) as 18年人数,
MAX(decode(t.bugUuid,'18',unitPrice,null)) as 18年价格
from yourtable t group by t.strPeopleType order by t.strPeopleType差不多是这个意思