我想撰寫一個回傳表的一部分（而不是回傳新表）的 SQL 查詢。
一個If, Case或任何陳述句應該決定回傳哪些行。
該陳述句應查看特定 ID 的“評論”（型別VARCHAR）列中的特定值，該 ID 可以是“新”或“已播放”。
如果 'comments' 是 LIKE '%new%' 那么查詢應該選擇 ID 1、2 和 3，否則如果 'comments' 是 LIKE '%played%' 那么查詢應該選擇 ID 4。
我無法讓這個運行，因為我只找到改變表中值但不選擇行的示例。
我希望有一個人可以幫助我 ：）
解釋問題的新方法
如果commentsID = 2 的行中列的值是，'new'則查詢應選擇 ID 為 1、2 和 3 的行。
輸入表：
ID title comments
---------------------
1 title1 sth
2 title2 *new*
3 title3
4 title4 sth
預期輸出：
ID title comments
---------------------
1 title1 sth
2 title2 new
3 title3
Else if the value of the column comments in the row with the ID = 2 is 'played' then the query should select the row with the ID 4.
Input table (Note that the value in 'comments' in ID 2 has changed)
ID title comments
---------------------
1 title1 sth
2 title2 *played*
3 title3
4 title4 sth
Expected output:
ID title comments
---------------------
4 title4 sth
This is the code I thought would do the task but it isn't
IF (SELECT comments FROM table WHERE ID = 2) LIKE 'new'
SELECT ID, title, comments
FROM table
WHERE ID = 1 OR ID = 2 OR ID = 3
IF (SELECT comments FROM table WHERE ID = 2) LIKE 'played'
SELECT ID, title, comments
FROM table
WHERE ID = 4
uj5u.com熱心網友回復：
簡單JOIN的表格：
SELECT t1.ID, t1.title, t1.comments
FROM table1 t1
JOIN table1 t2
ON ((t1.ID = 1 OR t1.ID = 2 OR t1.ID = 3) AND t2.comments LIKE 'new') OR
(t1.ID = 4 AND t2.comments LIKE 'played')
WHERE t2.ID = 2;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=593d8e703006322f9a77df60e9985b1f
uj5u.com熱心網友回復：
如果您不想使用帶有流量控制的程式。
你可以隨時切換到動態 sql
SELECT IF ((SELECT comments FROM table_a WHERE ID = 2) LIKE 'new',
@sql := 'SELECT ID, title, comments FROM table_a WHERE ID = 1 OR ID = 2 OR ID = 3',
IF ((SELECT comments FROM table_a WHERE ID = 2) LIKE 'played', @sql :=
'SELECT ID, title, comments FROM table_a WHERE ID = 4', @sql := 'SELECT SLEEP(0)'));
PREPARE Stnt FROM @sql;
EXECUTE Stnt;
DEALLOCATE PREPARE Stnt;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/454875.html
標籤：mysql sql if-statement case
上一篇：變數參考無效