一个结果中有四个查询-oracle

Question

我有四个查询。

1.结果：计数（）|Nazev

2. 结果：Count（）|Nazev

3. 结果：Ode_dne_včetnŞ|Do_dne_včetn|Nazev_organizace|Pocet

4. 结果：Nazev|Create_uzivatel|Create_cas

我只想在一次点击后得到一个结果。这样：

计数（）|Nazev|Count

有可能吗？

--第一个

    select count(*),subjekt.nazev from osoba,subjekt where   
    osoba.ID_PATRI_DO=subjekt.ID group by subjekt.nazev order by     
    subjekt.nazev;   

--第二

    select count(*),subjekt.nazev from ZADAVACI_POSTUP,subjekt where
    ZADAVACI_POSTUP.id_zadavatel=subjekt.ID group by subjekt.nazev order by 
    subjekt.nazev;

--第三

    select    max(trunc(sysdate)-6) ode_dne_včetně, max(trunc(sysdate))  
    do_dne_včetně,nazev_organizace,count(*) pocet
    from(
    select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,   
    u.username,u.nazev, a.datumzapisauditu   
    ,to_char(a.datumzapisauditu,'DD.MM.YYYY')  datum , a.id
    from d$caudit a
    join cuzivatel u on u.id= a.id_uzivatel
    join osoba os on u.id_osoba_bridge = os.id
    join t$subjekt subj on subj.id = os.id_patri_do
    left join d$caudittyp t on t.id=a.id_audittyp
    where datumzapisauditu between trunc(sysdate)-7  AND  trunc(sysdate)
    order by  a.datumzapisauditu desc
    )
    group by nazev_organizace order by nazev_organizace ;

--第四

    select  sb.nazev, lg.create_uzivatel, lg.create_cas from Aplikacni_log                   lg 
    join zadavaci_postup zp on zp.id = lg.id_zp
    join subjekt sb on sb.id = zp.id_zadavatel
    where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY')
    order by sb.nazev asc

Answer 1

您可以使用子查询重构来实现这一点。

Edit1:-subjekt.nazev是四个查询之间的关系，那么您可以添加CCD_ 1和其他查询的类似关系。

with first_qry as (select count(*),subjekt.nazev from osoba,subjekt where   
osoba.ID_PATRI_DO=subjekt.ID group by subjekt.nazev order by     
subjekt.nazev),
second_qry as (select count(*),subjekt.nazev from ZADAVACI_POSTUP,subjekt where
ZADAVACI_POSTUP.id_zadavatel=subjekt.ID group by subjekt.nazev order by 
subjekt.nazev),
third_qry as ( select max(trunc(sysdate)-6) 
ode_dne_včetně, max(trunc(sysdate))  
do_dne_včetně,nazev_organizace,count(*) pocet
from(
select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,   
u.username,u.nazev, a.datumzapisauditu   
,to_char(a.datumzapisauditu,'DD.MM.YYYY')  datum , a.id
from d$caudit a
join cuzivatel u on u.id= a.id_uzivatel
join osoba os on u.id_osoba_bridge = os.id
join t$subjekt subj on subj.id = os.id_patri_do
left join d$caudittyp t on t.id=a.id_audittyp
where datumzapisauditu between trunc(sysdate)-7  
AND  trunc(sysdate)
order by  a.datumzapisauditu desc
)
group by nazev_organizace order by nazev_organizace),
fourth_qry as (select  sb.nazev, lg.create_uzivatel, 
lg.create_cas from   Aplikacni_log                   lg 
join zadavaci_postup zp on zp.id = lg.id_zp
join subjekt sb on sb.id = zp.id_zadavatel
where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY')
order by sb.nazev asc)
select distinct a.*,b.*,c.*,d.* 
from first_qry a ,second_qry b,third_qry c,fourth_qry d

Answer 2

昨天你问了一个类似的问题，我用这个答案回答了。

您可以使用Union或Union all的相同方法，只需为没有结果的每列选择null

select count(*) AS subjekt_count,subjekt.nazev ,null,null,null,null,null,null
--null columns represent the results from the other queries
from osoba,subjekt 
where  osoba.ID_PATRI_DO=subjekt.ID
group by subjekt.nazev  
UNION ALL
select null, null,count(*) AS subjekt_nazev_count,subjekt.nazev,null,null,null,null 
from ZADAVACI_POSTUP,subjekt where
ZADAVACI_POSTUP.id_zadavatel=subjekt.ID 
group by subjekt.nazev 
---and so on

还有其他方法使用WITH语句，但语句之间需要一个公共键，我不清楚您的查询是对同一个表的四个独占查询还是四个重叠查询。你希望结果重复吗？

Answer 3

所有查询都包含列nazev，它是分组和顺序键，所以我假设这是字段联接结果。如果是这样，那么您可以使用下面的SQL。如果这不是您想要的，那么请编辑问题，附上一些数据、表定义，最好是SQL Fiddle，并准确解释您的请求。

SQLFiddle

with q1 as (select count(*) cnt, subjekt.nazev
  from osoba,subjekt where osoba.ID_PATRI_DO=subjekt.ID 
  group by subjekt.nazev ),
q2 as (select count(*) cnt, subjekt.nazev 
  from ZADAVACI_POSTUP, subjekt 
  where ZADAVACI_POSTUP.id_zadavatel=subjekt.ID 
  group by subjekt.nazev ),
q3 as (select max(trunc(sysdate)-6) ode_dne_vcetne, 
    max(trunc(sysdate)) do_dne_vcetne, nazev_organizace nazev, count(*) pocet
  from (
    select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,   
        u.username, u.nazev, a.datumzapisauditu,
        to_char(a.datumzapisauditu,'DD.MM.YYYY') datum, a.id
      from d$caudit a
        join cuzivatel u on u.id= a.id_uzivatel
        join osoba os on u.id_osoba_bridge = os.id
        join t$subjekt subj on subj.id = os.id_patri_do
        left join d$caudittyp t on t.id=a.id_audittyp
      where datumzapisauditu between trunc(sysdate)-7  and trunc(sysdate) )
  group by nazev_organizace),
q4 as (select sb.nazev, lg.create_uzivatel, lg.create_cas 
  from aplikacni_log lg join zadavaci_postup zp on zp.id = lg.id_zp
    join subjekt sb on sb.id = zp.id_zadavatel
  where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY') )
select nazev, q1.cnt cnt1, q2.cnt cnt2, q3.ode_dne_vcetne, q3.do_dne_vcetne, 
    q3.pocet, q4.create_uzivatel, q4.create_cas
  from q1 left join q2 using (nazev) left join q3 using (nazev) left join q4 using (nazev)
  order by nazev, create_cas

样本数据输出：

NAZEV      CNT1 CNT2 ODE_DNE_VCETNE DO_DNE_VCETNE POCET CREATE_UZIVATEL CREATE_CAS
---------- ---- ---- -------------- ------------- ----- --------------- ----------
SUBJEKT1      1    1                                                  1 2015-03-20 
SUBJEKT2      2    1                                                  1 2015-03-20