如何在SQL連接中創建多個where子句

我有兩個表colorcast_app_show和colorcast_app_content。

我正在使用 postgreSQL 資料庫。

我想從節目表中獲取所有節目資料，從內容表中獲取內容資料的計數，但草稿=假。

colorcast_app_show表資料：

 id | show_name | description | host_name | host_social_account_link |            thumbnail_image             | publish_date | status |            created_on            | user_id
---- ----------- ------------- ----------- -------------------------- ---------------------------------------- -------------- -------- ---------------------------------- ---------
  1 | Test1     | nil         | nil       | nil                      | media/download_6W1VYGZ.jpg             | 2022-01-04   | f      | 2022-01-04 16:34:55.84046 05:30  |       1
  2 | ttt       | desc        | name      | link                     | media/MicrosoftTeams-image_UMTq0YY.png | 2022-01-19   | f      | 2022-01-05 17:12:39.4206 05:30   |       2
  7 | show 2    | desc        | name      | link                     | media/MicrosoftTeams-image_WLoXCfp.png | 2022-01-12   | f      | 2022-01-13 00:49:04.345571 05:30 |       2
 10 | sdsds     | sdsds       | dssds     | link                     | media/MicrosoftTeams-image_O4BrUjO.png | 2022-01-13   | f      | 2022-01-13 12:02:34.404602 05:30 |       2
 13 | show 1    | desc        | name      | link                     | media/signin_Y68p1uT.jpg               | 2022-01-14   | f      | 2022-01-13 13:43:34.328246 05:30 |      60
 14 | show 2    | njnjsnd     | jjj       | kjkjk                    | media/download_EXIHERo.jpg             | 2022-01-13   | f      | 2022-01-13 14:37:27.999332 05:30 |      60
 15 | show 3    | desc        | name      | link                     | media/MicrosoftTeams-image_nEojgxZ.png | 2022-01-13   | f      | 2022-01-13 17:13:22.050878 05:30 |      60

colorcast_app_content表資料：

 id | content_type | sponsor_link | status |             added_on             |            content_file            | title  | subtitle | description | publish_now | schedule_release | expiration | show_id | user_id | draft
---- -------------- -------------- -------- ---------------------------------- ------------------------------------ -------- ---------- ------------- ------------- ------------------ ------------ --------- --------- -------
 46 | Lecture      | links        | f      | 2022-01-13 13:43:52.959811 05:30 | media/ADP_ver3_-_Copy_Pb8hwPq.xlsx | Title1 | dssd     | ddsds       | Now         | 2022-01-13       | 2022-01-12 |      13 |      60 | f
 48 | Lecture      | dsdsds       | f      | 2022-01-13 14:46:20.242004 05:30 | media/Dancing_Script_GTlu8Cx.zip   | sdsds  | sdsds    | ssdsds      | Later       | 2022-01-13       | 2022-01-27 |      14 |      60 | t
 47 | Seminar      | sss          | f      | 2022-01-13 14:43:32.773371 05:30 | media/Dancing_Script_TU5JCBx.zip   | sdsdsd | sfsfs    | kjkjkjk     | Later       | 2022-01-13       | 2022-01-20 |      15 |      60 | t
 45 | Lecture      | link         | f      | 2022-01-13 12:58:19.745322 05:30 | media/Dancing_Script_Bn1DtxG.zip   | Title1 | nullllll | nsss        | Later       | 2022-01-13       | 2022-01-19 |       7 |       2 | f

我在下面使用了 SQL 連接：

SELECT cas.show_name, COUNT(cac.show_id), cas.status, to_char("created_on", 'DD/MM/YYYY'), to_char("publish_date", 'DD/MM/YYYY'), cas.id FROM colorcast_app_show as cas LEFT JOIN colorcast_app_content as cac ON cac.show_id = cas.id where cas.user_id=60 GROUP BY cas.id, cas.show_name;

并得到以下輸出

 show_name | count | status |  to_char   |  to_char   | id
----------- ------- -------- ------------ ------------ ----
 show 1    |     1 | f      | 13/01/2022 | 14/01/2022 | 13
 show 2    |     1 | f      | 13/01/2022 | 13/01/2022 | 14
 show 3    |     1 | f      | 13/01/2022 | 13/01/2022 | 15

我想要輸出如下：

 show_name | count | status |  to_char   |  to_char   | id
----------- ------- -------- ------------ ------------ ----
 show 1    |     1 | f      | 13/01/2022 | 14/01/2022 | 13
 show 2    |     0 | f      | 13/01/2022 | 13/01/2022 | 14
 show 3    |     0 | f      | 13/01/2022 | 13/01/2022 | 15

如上輸出顯示 2 和顯示 3 內容保存在草稿中，所以我不想計算該資料。

請讓我知道是否有人有解決方案。

提前致謝。

uj5u.com熱心網友回復：

SELECT cas.show_name, 
       COUNT(cac.show_id), 
       cas.status, 
       to_char("created_on", 'DD/MM/YYYY'), 
       to_char("publish_date", 'DD/MM/YYYY'), 
       cas.id
FROM   colorcast_app_show as cas 
       LEFT OUTER JOIN colorcast_app_content as cac 
          ON cac.show_id = cas.id 
             AND cac.draft = false
WHERE  cas.user_id=60 
GROUP  BY cas.id, cas.show_name;

uj5u.com熱心網友回復：

在您的SELECT中，您可以將 a 更改COUNT為 aSUM并在函式內部添加 aCASE以執行以下操作：

SELECT cas.show_name,
SUM(CASE WHEN cac.publish_now = 'Now' THEN 1 ELSE 0 END), 
     cas.status, 
     to_char("created_on", 'DD/MM/YYYY'),
     to_char("publish_date", 'DD/MM/YYYY'),
     cas.id 
FROM colorcast_app_show as cas 
LEFT JOIN colorcast_app_content as cac 
ON cac.show_id = cas.id where cas.user_id=60 
GROUP BY cas.id, cas.show_name;

這將指定您僅在處于已發布/可發布狀態時才向“計數”（技術上是一個總和，但它具有相同的效果）添加一個。

標籤：

上一篇：Rails服務器無法啟動且沒有任何錯誤訊息

下一篇：CTE中的聚合列不能用于PostgreSQL查詢中的WHERE子句