promotion image of download ymail app
Promoted
匿名 發問時間: 社會與文化語言 · 1 0 年前

SQL多表查詢問題

題目1:

查詢authors資料表中,住在'ca'加州但是不住在'oakland'城市的作者有多少人(不管有沒有寫書),以及這些作者所寫的書一共有多少本?

題目2:

查詢authors資料表中,住在'ca'加州但是不住在'oakland'城市的作者有多少人,以及這些作者所寫的書一共有多少本?

程式碼:

select count(distinct a.au_id) as '作者人數',count(distinct t.title) as '書本'

from authors as a join titleauthor as ti

on a.au_id=ti.au_id

join titles as t

on t.title_id=ti.title_id

where a.state like '%ca%' and a.city not like '%oakland%'

這兩題我都寫到這邊就不知道怎麼寫條件去查了...

題目1正解:

10 跟 11 筆(照題目順序寫的話)

題目2正解:

9 跟 11 筆(照題目順序寫的話)

請會的大大告知如何修改,以及LEFT RIGHT JOIN等語法,到底要在什麼時候適時寫入?

已更新項目:

不好意思喔...你的答案還是都錯的...我已經自己有寫出來了,但不知道程式對不對

題目一:

2 個已更新項目:

select count(distinct a.au_id) as '作者人數',count(t.title) as '書本'

from authors as a left join titleauthor as ti

on a.au_id=ti.au_id

left join titles as t

on ti.title_id=t.title_id

where a.state like '%ca%' and a.city not like '%oakland%'

3 個已更新項目:

題目二的:

select count(distinct a.au_id) as '作者人數',count(t.title) as '書本'

from authors as a join titleauthor as ti

on a.au_id=ti.au_id

join titles as t

on ti.title_id=t.title_id

where a.state like '%ca%' and a.city not like '%oakland%'

如有不對的地方還請多指教

1 個解答

評分
  • 1 0 年前
    最佳解答

    你的 State / City 大小寫有問題

    select a.state, a.city,a.au_id,t.title

    --count(distinct a.au_id) as '作者人數',

    --count(distinct t.title) as '書本'

    from authors as a join titleauthor as ti

    on a.au_id=ti.au_id

    join titles as t

    on t.title_id=ti.title_id

    where a.state like '%CA%'

    and a.city not like '%Oakland%'

    CA

    Menlo Park 172-32-1176 Prolonged Data Deprivation: Four Case Studies CA

    Berkeley 238-95-7766 But Is It User Friendly? CA

    San Jose 267-41-2394 Cooking with Computers: Surreptitious Balance Sheets CA

    San Jose 267-41-2394 Sushi, Anyone? CA

    Berkeley 409-56-7008 The Busy Executive's Database Guide CA

    Palo Alto 427-17-2319 Secrets of Silicon Valley CA

    Covelo 472-27-2349 Sushi, Anyone? CA

    San Francisco 486-29-1786 Net Etiquette CA

    San Francisco 486-29-1786 Emotional Security: A New Algorithm CA

    Walnut Creek 672-71-3249 Sushi, Anyone? CA

    Palo Alto 846-92-7186 Secrets of Silicon Valley

    select count(distinct a.au_id) as '作者人數',

    count(distinct t.title) as '書本'

    from authors as a join titleauthor as ti

    on a.au_id=ti.au_id

    join titles as t

    on t.title_id=ti.title_id

    where a.state like '%CA%'

    and a.city not like '%Oakland%'

    '作者人數','書本'

    9 8

    Call Skype: Adempiere

    MSN: albert_a_chen@yahoo.com

    參考資料: SAP / Oracle / DB2顧問 OpenSource ERP Adempiere 北美與中國(含台灣區)技術支持團隊
    • Commenter avatar登入以對解答發表意見
還有問題?馬上發問,尋求解答。