以下是我的SQL:
select org.orgName, org.id,'T' || REPLACE(pb.recidentifier,'-','_') as recidentifier
from itdc_catalog_e_infoclspublic pb
left join itdc_catalog_e_category cate on pb.categoryid =cate.id
left join tor_e_org org on pb.supplyorgid = org.id
where cate.nodevalue = '00003'
group by org.orgName, org.id,pb.recidentifier
order by orgName
结果为:
但这样我就要从后台取出表名(recidentifier)字段值,通过代码循环拼装SQL在查询这些表的记录。SQL的性能很差。
要如何才能用一句SQL把查询出来的“recidentifier”字段中记录的表名查询出这些表的总记录数并按orgName分组。
格式:(一句话实现,表名必须通过'T' || REPLACE(pb.recidentifier,'-','_') as recidentifier格式拼装,否则无法得到真正的表名,数据库只保存一个如“29738-38AA016”这样的格式数值)
orgname count
1 市财政局 1000
2 市残联 200
3 市城建集团 600
4 .... ...
select org.orgName, org.id,'T' || REPLACE(pb.recidentifier,'-','_') as recidentifier
from itdc_catalog_e_infoclspublic pb
left join itdc_catalog_e_category cate on pb.categoryid =cate.id
left join tor_e_org org on pb.supplyorgid = org.id
where cate.nodevalue = '00003'
group by org.orgName, org.id,pb.recidentifier
order by orgName
结果为:
但这样我就要从后台取出表名(recidentifier)字段值,通过代码循环拼装SQL在查询这些表的记录。SQL的性能很差。
要如何才能用一句SQL把查询出来的“recidentifier”字段中记录的表名查询出这些表的总记录数并按orgName分组。
格式:(一句话实现,表名必须通过'T' || REPLACE(pb.recidentifier,'-','_') as recidentifier格式拼装,否则无法得到真正的表名,数据库只保存一个如“29738-38AA016”这样的格式数值)
orgname count
1 市财政局 1000
2 市残联 200
3 市城建集团 600
4 .... ...
from itdc_catalog_e_infoclspublic pb
left join itdc_catalog_e_category cate on pb.categoryid =cate.id
left join tor_e_org org on pb.supplyorgid = org.id
where cate.nodevalue = '00003'
group by org.orgName, org.id,pb.recidentifier
order by orgName ) a group by a.orgname
结果不就是这样????
recidentifier is table name ! He wants to retrieve the total record number in each on of them , then sum it group by organization name.
本人在网上查了一些资料
SQL
select orgname,sum(ut.num_rows) from (select org.orgName, org.id,'T' || REPLACE(pb.recidentifier,'-','_') as recidentifier
from itdc_catalog_e_infoclspublic pb
left join itdc_catalog_e_category cate on pb.categoryid =cate.id
left join tor_e_org org on pb.supplyorgid = org.id
where cate.nodevalue = '00003'
group by org.orgName, org.id,pb.recidentifier
order by orgName ) tempTable
left join user_tables ut on ut.table_name=recidentifier
group by orgname
初步实现要求
但是无法用时间做条件进行对数据的统计,因为时间字段都是在具体的表里("T29796_96AA001"中的period字段)
而且如果我对某张记录表中的记录继续删除后,user_tables 中的num_rows也不会变化,还是原有的记录求高手指点如何处理
1-无法用时间做条件进行对数据的统计,因为时间字段都是在具体的表里("T29796_96AA001"中的period字段)
2-如果我对某张记录表("T****_***")中的记录进行删除后,user_tables 中的num_rows也不会变化,还是原有的条数记录,而不是随之减少。
我要的结果是
orgname count
1 市财政局 1000
2 市残联 200
3 市城建集团 600
4 .... ...
1.并且SQL中可以用时间条件进行对记录的统计
5楼你说要测试数据 怎么叫测试数据? 表就是普通的建表语句