Oracle-在一个结果中进行更多查询 [英] Oracle - more queries in one result

问题描述

我有四个不同的查询，我想在同一条语句中(单击一下后)查看所有结果.我不知道.有可能吗?

I've four different queries and I want to see all results in same statement(after one click). I have no idea. Is it possible?

1.query

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

1. 查询

1. query

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

查询

query

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 由nazev_organizace订购 ;

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

推荐答案

您可以使用UNION运算符在一个查询中获得结果.它不能使大型设备具有成本效益，但是可以做到.

You can get the results in one query by using the UNION operator. It does not lend itself to being cost efficient on large sets but does the job.

• 所有查询必须选择相同数据类型的相同列数.
• 如果查询中有唯一列，则可以选择null
• 选择表明数据来源的字符串也很方便
• 结果集将仅使用第一个查询的列名

• 排序适用于整个结果集，而不是单个查询

• all the queries must select the same number of columns of the same data type.
• you can select null if you have a unique column in a query
• it is also handy to select a string indicating the origin of the data
• the result set will only use the names of the columns of the first query

• ordering applies to the whole result set not individual queries

选择count(*)AS R_COUNT，"OSABA" AS SOURCE，subjekt.nazev，null，null，null，null

select count(*) AS R_COUNT, 'OSABA' AS SOURCE,subjekt.nazev , null, null,null,null

来自osoba，主题

其中osoba.ID_PATRI_DO = subjekt.ID

where osoba.ID_PATRI_DO=subjekt.ID

按subjekt.nazev分组

group by subjekt.nazev

UNION

选择count(*)，'ZADAVACI_POSTUP'，subjekt.nazev，null，null，null，null

select count(*),'ZADAVACI_POSTUP', subjekt.nazev , null, null,null,null

来自ZADAVACI_POSTUP，主题

from ZADAVACI_POSTUP,subjekt

其中ZADAVACI_POSTUP.id_zadavatel = subjekt.ID

where ZADAVACI_POSTUP.id_zadavatel=subjekt.ID

按subjekt.nazev分组

group by subjekt.nazev

UNION

选择NULL，'Aplikacni_log'，sb.nazev，lg.create_uzivatel，lg.create_cas，null，null

select NULL,'Aplikacni_log', sb.nazev, lg.create_uzivatel, lg.create_cas,null,null

从Aplikacni_log lg加入zp.id = lg.id_zp上的zadavaci_postup zp

from Aplikacni_log lg join zadavaci_postup zp on zp.id = lg.id_zp

在sb.id = zp.id_zadavatel上加入主题sb

join subjekt sb on sb.id = zp.id_zadavatel

其中lg.create_cas> to_date('08 .11.2014'，'DD.MM.YYYY')

where lg.create_cas > to_date('08.11.2014', 'DD.MM.YYYY')

UNION

选择计数(*)位，'SUBQUERY'，do_dne_včetně，nazev_organizace，max(trunc(sysdate)-6)ode_dne_včetně， max(trunc(sysdate))

select count(*) pocet,'SUBQUERY', do_dne_včetně,nazev_organizace,max(trunc(sysdate)-6) ode_dne_včetně, max(trunc(sysdate))

来自(

选择to_char(t.popis)popis_typu，subj.nazev nazev_organizace，

select to_char(t.popis) popis_typu,subj.nazev nazev_organizace,

u.username，u.nazev，a.datumzapisauditu

u.username,u.nazev, a.datumzapisauditu

，to_char(a.datumzapisauditu，'DD.MM.YYYY')数据，a.id

,to_char(a.datumzapisauditu,'DD.MM.YYYY') datum , a.id

来自d $ caudit a

from d$caudit a

在u.id = a.id_uzivatel上加入cuzivatel u

join cuzivatel u on u.id= a.id_uzivatel

在u.id_osoba_bridge = os.id上加入osoba os

join osoba os on u.id_osoba_bridge = os.id

在subj.id = os.id_patri_do上加入t $ subjekt subj

join t$subjekt subj on subj.id = os.id_patri_do

在t.id = a.id_audittyp上左加入d $ caudittyp t

left join d$caudittyp t on t.id=a.id_audittyp

trunc(sysdate)-7和trunc(sysdate)之间的datumzapisauditu

where datumzapisauditu between trunc(sysdate)-7 AND trunc(sysdate)

由a.datumzapisauditu desc排序 )

order by a.datumzapisauditu desc )

;

这篇关于Oracle-在一个结果中进行更多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！