我想写一个SQL查询,它返回表的一部分(而不是返回新表(。
If, Case
或任何语句应该决定返回哪些行。
语句应查看特定ID的列"comments"(类型为VARCHAR
(中的特定值,该列可以是"new"或"played"。
如果"comments"是LIKE"%new%",则查询应选择ID 1、2和3,否则,如果"commends"是LIKE"%played%",那么查询应选择ID4。
我无法运行此操作,因为我只找到更改表中值而不选择行的示例。
我希望有人能帮助我:(
解释问题的新方法
如果ID为2的行中列comments
的值为'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
否则,如果ID为2的行中的列comments
的值被"播放",则查询应选择ID为4的行。
输入表(注意ID 2中"注释"中的值已更改(
ID title comments
---------------------
1 title1 sth
2 title2 *played*
3 title3
4 title4 sth
预期输出:
ID title comments
---------------------
4 title4 sth
这是我认为可以完成任务的代码,但它不是
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
只需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
Id您不想将procedure与其流控制一起使用。
您总是可以切换到动态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;