OracleSQL嵌套子查詢

撰寫查詢以顯示“軟體工程”和“計算機編程”中的學科名稱和最低分數的串列。為最小標記列指定別名 MIN_MARK。根據主題名稱按降序對結果進行排序。

 select subject_name , value as MIN_MARK from mark,subject where
 mark.subject_id = subject.subject_id and value in 
 (select min(value) from mark,subject where
 mark.subject_id = subject.subject_id and
 lower(subject_name) = 'computer engineering' and
 lower(subject_name) = 'software engineering')
 order by subject_name desc;

要求的答案是

不要擔心資料庫的內容，幫我在輸出中獲取兩個主題名稱

uj5u.com熱心網友回復：

您的主要錯誤：該WHERE子句一次只查看一行。表格中不能有一行同時匹配兩個主題：

where ...
and lower(subject_name) = 'computer engineering'
and lower(subject_name) = 'software engineering'

肯定是

where ...
  and 
  (
      lower(subject_name) = 'computer engineering'
    or
      lower(subject_name) = 'software engineering'
  )

或者干脆

where ...
and lower(subject_name) in ('computer engineering', 'software engineering')

然后，你讓事情變得比必要的更復雜。如果您希望每個 subject_name 有一個結果行，則GROUP BY subject_name.

您使用的連接語法已被棄用了 30 年。不要使用它。如果您被教過這種語法，請退出該課程、書籍或教程。使用 1992 年在標準 SQL 中引入的顯式 ANSI 連接。

select
  s.subject_name,
  min(m.value) as min_mark
from subject s
left outer join mark m on m.subject_id = s.subject_id
where lower(s.subject_name) in ('computer engineering', 'software engineering')
group by s.subject_name
order by s.subject_name desc;

我在這里使用外連接來處理主題還沒有標記但仍應顯示在輸出中的情況。如果不希望這樣做，請更改LEFT OUTER JOIN為INNER JOIN。

uj5u.com熱心網友回復：

WITH 子句在這里只是為了生成一些示例資料，因此，它不是答案的一部分。樣本資料：

WITH
    subject AS
        (
            Select 1 "SUBJ_ID", 'Software Engineering'     "SUBJ_NAME", 'A1' "SUBJ_CODE", 11 "STAFF_ID" From Dual Union All
            Select 2 "SUBJ_ID", 'Computer Programming'     "SUBJ_NAME", 'A2' "SUBJ_CODE", 12 "STAFF_ID" From Dual Union All
            Select 3 "SUBJ_ID", 'Regenerative Cybernetics' "SUBJ_NAME", 'A3' "SUBJ_CODE", 13 "STAFF_ID" From Dual 
        ),
    mark AS
        (
            Select 61 "VALUE", 1 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 65 "VALUE", 2 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 74 "VALUE", 3 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union ALL
            Select 72 "VALUE", 1 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 69 "VALUE", 2 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 66 "VALUE", 3 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union ALL
            Select 91 "VALUE", 1 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 67 "VALUE", 2 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 70 "VALUE", 3 "SUBJ_ID", 103 "STUDENT_ID" From Dual 
        )

如果您只需要主題名稱和最小值，那么您不必使用嵌套查詢。此外，在您的問題中，排序應該按主題名稱降序排列，但在您要求的答案中，順序是按最小值降序排列。
希望這可以幫助您完成它：

Select 
    s.SUBJ_NAME "SUBJ_NAME",
    Min(m.VALUE) "MIN_MARK"
From 
    MARK m
Inner Join
    SUBJECT s ON(m.SUBJ_ID = s.SUBJ_ID)
Where
    s.SUBJ_NAME = 'Software Engineering' 
    OR 
    s.SUBJ_NAME = 'Computer Programming'
Group By
    s.SUBJ_NAME
Order By
    s.SUBJ_NAME DESC

...結果為：

--  R e s u l t :
--  
--  SUBJ_NAME                  MIN_MARK
--  ------------------------ ----------
--  Software Engineering             61 
--  Computer Programming             65

最終得到相同結果的更好的過濾方法（如果您知道 ID）是：

Where
    s.SUBJ_ID IN(1, 2)

uj5u.com熱心網友回復：

我解決了上面的問題

select subject_name,min(value) as MIN_MARK from mark,subject where
mark.subject_id = subject.subject_id and 
lower(subject_name) in ('computer programming' , 'software engineering')
group by subject_name
order by subject_name desc;

輸出：

標籤：sql甲骨文

上一篇：Oracle強制SYSDATE到PT

下一篇：難以為oracleregexp_like命令模式匹配郵政編碼