SQL：一個查詢中的多個選擇陳述句

我想從一個查詢中的三個 SQL 表中選擇資訊。

一個例子可能是以下設定。

朋友

   id | idmother | dayBirth
  --------------------------
    1 |        1 | 09/09/21 
    2 |        2 | 09/09/21 
    3 |        3 | 11/09/21 
    4 |        3 | 11/09/21 
    5 |        4 | 07/09/21
  ... |      ... |      ...

媽媽們

   id |     name 
  --------------- 
    1 |    Alice
    2 | Samantha
    3 | Veronica 
    4 |    Maria
  ... |      ...

tblIsAssignedParty

   idMother | codeParty | price
  ------------------------------
          1 |       231 |    15
          2 |       645 |    28
          3 |       164 |    33
        ... |       ... |   ...

我想要一個查詢，給我以下內容：

  dayBirth |   weekDay | totalFriendsForParty | totalFriendsForPartyPercent | totalFriendsNoParty | totalFriendsNoPartyPercent 
  -----------------------------------------------------------------------------------------------------------------------------
  07/09/21 |   Tuesday |                    0 |                           0 |                   1 |                       0.??    
  09/09/21 |  Thursday |                    2 |                        0.?? |                   0 |                          0  
  11/09/21 |  Saturday |                    2 |                        0.?? |                   0 |                          0

筆記：

• dayBirth = 簡單的出生日期；我需要按這個日期分組的朋友
• weekDay = day 出生名
• totalFriendsForParty = 參加聚會的朋友；我們知道母親是否有指定的派對
• totalFriendsForPartyPercent = 朋友的百分比，占將參加聚會的朋友總數
• totalFriendsNoParty = 不會參加聚會的朋友；我們知道如果母親沒有指定派對
• totalFriendsNoPartyPercent = 朋友的百分比，占不參加聚會的朋友總數

我需要根據他們的母親是否參加聚會來確定朋友的數量。我嘗試在單個查詢中選擇多個陳述句，但以下代碼不起作用：

SELECT 
(SELECT distinct dayBirth, TO_CHAR(dayBirth, 'DAY') from tblFriends) as firstSecondColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as thirdColumn,

(SELECT TRUNC(count(*) / count(thirdColumn.id) , 2) from tblFriends) as quarterColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as fifthColumn,

(SELECT TRUNC(count(*) / count(fifthColumn.id) , 2) from tblFriends) as  sixthColumn,

order by dayBirth

關于這個有什么建議嗎？我努力學習，盡我所能:-(

編輯：我無法添加插入，因為它是檔案上傳，但我可以添加表創建的近似值。

創建表：

  CREATE TABLE tblFriends
   (    
    id NUMBER(*,0),
    idMother CHAR(10 CHAR),  
     CONSTRAINT PK_FRIEND PRIMARY KEY (id, idMother), 
     CONSTRAINT FK_IDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    dayBirth DATE CONSTRAINT NN_DAY NOT NULL
   )


  CREATE TABLE tblMothers
   (    
    id CHAR(10 CHAR) CONSTRAINT PK_MOTHER PRIMARY KEY (id),   
    name VARCHAR2(20 CHAR) CONSTRAINT NN_MNAME NOT NULL
   )


  CREATE TABLE tblIsAssignedParty
   (    
    idMother CHAR(10 CHAR), 
    codeParty CHAR(10 CHAR),
    CONSTRAINT PK_ASSIGNED PRIMARY KEY (idMother, codeParty),  
    CONSTRAINT FK_ASSIGNEDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    CONSTRAINT FK_ASSIGNEDPARTY FOREIGN KEY (codeParty)
      REFERENCES tblParties (codeParty),
    price DECIMAL(10,2)
   )

uj5u.com熱心網友回復：

您似乎想要LEFT JOINfirends 和 party 表，然后使用條件聚合：

SELECT dayBirth,
       TO_CHAR(dayBirth, 'FMDAY', 'NLS_DATE_LANGUAGE=English') AS day,
       COUNT(p.idmother)
         AS totalFriendsForParty,
       COUNT(p.idmother) / COUNT(*) * 100
         AS totalFriendsForPartyPercent,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) AS totalFriendsNoParty,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) / COUNT(*) * 100
         AS totalFriendsNoPartyPercent 
FROM   tblFriends f
       LEFT OUTER JOIN tblIsAssignedParty p
       ON (f.idmother = p.idmother)
GROUP BY dayBirth

其中，對于樣本資料：

CREATE TABLE tblFriends (id, idmother, dayBirth) AS
SELECT 1, 1, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 3, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 4, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 5, 4, DATE '2021-09-07' FROM DUAL;

CREATE TABLE tblIsAssignedParty (idMother, codeParty, price) AS
SELECT 1, 231, 15 FROM DUAL UNION ALL
SELECT 2, 645, 28 FROM DUAL UNION ALL
SELECT 3, 164, 33 FROM DUAL;

輸出：

生日	日	TOTALFRIENDS FORPARTY	TOTALFRIENDS FORPARTTYPERENT	TOTALFRIENDSNOPARTY	TOTALFRIENDSNOPARTYPERENT
09-SEP-21	周四	2	100	0	0
11-SEP-21	周六	2	100	0	0
07-SEP-21	周二	0	0	1	100

db<>在這里擺弄

uj5u.com熱心網友回復：

我們使用了一些連接概念來與 3 個表中的資訊進行互動。

客戶 1 表

客戶 2 表

產品表

從多個表中選擇的示例語法：

SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2  
FROM product AS p  
LEFT JOIN customer1 AS c1  
ON p.cus_id=c1.cus_id  
LEFT JOIN customer2 AS c2  
ON p.cus_id = c2.cus_id

通過這個簡單的例子，我們可以從多個表中提取詳細資訊。

標籤：sql 甲骨文

上一篇：oracle函式知道資料是否為素數。收到警告：創建的函式有編譯錯誤

下一篇：如果從表PLsql創建程序中找到id，則查找id然后分配1