Oracle9.i从两个表和三个记录中选择一个记录

Question

我有以下查询(查询1(：

SELECT AVG(a.nnilai)*75/100 
FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'

查询1的结果：点击以预览

(查询2(：

SELECT t.nbobot FROM TRNILAIKINERJA t 
WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT'

查询2的结果：点击以预览

(查询3(：

SELECT p.nbobot FROM TRNILAIKINERJA p 
WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM'

查询3的结果：点击以预览

到目前为止，我已经运行了这个查询：

SELECT AVG(a.nnilai)*75/100,
(SELECT t.nbobot FROM TRNILAIKINERJA t WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT'),
(SELECT p.nbobot FROM TRNILAIKINERJA p WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM')
FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'

但结果是：

ORA-00937：不是单个组组功能

查询的完整记录(*(：

SELECT * 
FROM trangketmhs a 
WHERE cthajar='20172018' 
AND a.csmt='E' 
AND a.kddos='00084'

点击以预览

(2) and (3) 
SELECT * 
FROM TRNILAIKINERJA t 
WHERE t.ckddos='00084' 
AND t.cthajar='20172018' 
AND t.csmt='E' 

点击以预览

我的问题

如何使3个查询的结果变成这样的输出：

| AVG(a.nnilai)*75/100  | NBOBOT1  | NBOBOT2 |
|-----------------------|----------|---------|
|     2,58818181818182  |     0.6  |     0.4 |

Answer 1

我之前的答案的更简单的替代方案：

SELECT a.the_average,
,(SELECT t.nbobot FROM TRNILAIKINERJA t WHERE t.ckddos='00084' AND t.cthajar='20172018' AND t.csmt='E' AND t.ckdnilai='TLT') nbobot1
,(SELECT p.nbobot FROM TRNILAIKINERJA p WHERE p.ckddos='00084' AND p.cthajar='20172018' AND p.csmt='E' AND p.ckdnilai='PKM') nbobot2
FROM (
SELECT AVG(a.nnilai)*75/100 the_average
FROM trangketmhs a WHERE cthajar='20172018' AND a.csmt='E' AND a.kddos='00084'
) a

Answer 2

您可以尝试使用连接表作为

create table trangketmhs(nnilai float,cthajar varchar2(8),csmt varchar2(1),kddos varchar2(8));
insert into trangketmhs values(4.001818181818186,'20172018','E','00084');
insert into trangketmhs values(2.9              ,'20172018','E','00084');
create table trnilaikinerja( ckddos varchar2(8), cthajar varchar2(8), csmt varchar2(1), ckdnilai varchar2(3));
insert into trnilaikinerjavalues('00084','20172018','E','TLT');
insert into trnilaikinerjavalues('00084','20172018','E','PKM');
select avg(t1.nnilai)*75/100 avg_nnilai,
max(decode(t2.ckdnilai,'TLT',.6)) nbobot1,
max(decode(t2.ckdnilai,'TLT',.4)) nbobot2 
from trangketmhs t1
join trnilaikinerja t2 
on t1.cthajar = t2.cthajar
and t1.csmt = t2.csmt
and t1.kddos = t2.ckddos;
AVG_NNILAI      NBOBOT1  NBOBOT2
----------------   -------  --------
2,58818181818182     0,6      0,4

Answer 3

也许我遗漏了一些东西，但您对trangketmhs(=a(的查询和对TRNILAIKINERJA(t和p(的查询之间似乎没有关系。

所以我认为你可以这样做：

SELECT a.the_average, t.nbobot1, p.nbobot2 
FROM (SELECT AVG(a.nnilai)*75/100 the_average
FROM trangketmhs a 
WHERE a.cthajar='20172018' 
AND a.csmt='E' 
AND a.kddos='00084') a
CROSS JOIN (SELECT t.nbobot nbobot1 
FROM TRNILAIKINERJA t 
WHERE t.ckddos='00084' 
AND t.cthajar='20172018' 
AND t.csmt='E' 
AND t.ckdnilai='TLT') t
CROSS JOIN (SELECT p.nbobot nbobot2 
FROM TRNILAIKINERJA p 
WHERE p.ckddos='00084' 
AND p.cthajar='20172018' 
AND p.csmt='E' 
AND p.ckdnilai='PKM') p