idtitlelink 1第一条 2第二条 3第三条 4NULL2怎么写select语句才能将4读到记录是: 4第二条2SELECT CASE WHEN (SELECT title FROM news WHERE link = 2) = NULL THEN (SELECT title FROM news WHERE id = 2) ELSE (SELECT title FROM news WHERE link = 2) END;
SELECT CASE WHEN (SELECT title FROM news WHERE id = 4) = NULL THEN (SELECT title FROM news WHERE id = (SELECT link FORM news WHERE id = 4)) ELSE (SELECT title FROM news WHERE id = 4) END;刚开始没有看清题意,现在首先判断当前id的title内容是否为空,假如为空的话,选择link的值为id选择title,非空的话选择title
mysql> select * from news -> ; +----+-------+------+ | id | title | link | +----+-------+------+ | 1 | test1 | | | 2 | test2 | | | 3 | test3 | | | 4 | NULL | 2 | +----+-------+------+ 4 rows in set (0.00 sec)mysql> SELECT CASE WHEN ((SELECT title FROM news WHERE id = 4) = NULL) THEN (SELECT title FROM news WHERE id = 4) ELSE (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) END; +-------------------------- --------------------------- ------------------------+ | CASE WHEN ((SELECT title OM news WHERE id = 4) ELSE news WHERE id = 4)) END | +-------------------------- --------------------------- ------------------------+ | test2 | +-------------------------- --------------------------- ------------------------+ 上面的逻辑写错了,汗一个 这个正确 SELECT CASE WHEN ((SELECT title FROM news WHERE id = 4) = NULL) THEN (SELECT title FROM news WHERE id = 4) ELSE (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) END;
这种情况你可以做一个自联接select n1.*, n2.title as title2 from news as n1 left join news as n2 on n1.link=n2.id;
发现自己对于判定null还真是不明白,于是又取查了一下手册,发现:NULL值可能令人感到奇怪直到你习惯它。概念上,NULL意味着“没有值”或“未知值”,且它被看作与众不同的值。为了测试NULL,你不能使用算术比较 操作符例如=、<或!=。为了说明它,试试下列查询:mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 很显然你不能通过这些比较得到有意义的结果。相反使用IS NULL和IS NOT NULL操作符:mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+于是 最后正确的是(我真菜。): SELECT CASE WHEN (SELECT title FROM news WHERE id = 4) IS NULL THEN (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) ELSE (SELECT title FROM news WHERE id = 4) END;
>> select n1.*, n2.title as title2 from news as n1 left join news as n2 on n1.link=n2.id;我需要的是根据指向ID替换该记录。 这个方法会产生一个新的输出列title。 我希望的结果其实是: id title link 1 第一条 2 第二条 3 第三条 4 NULL 2 数位为=> id title link 1 第一条 2 第二条 3 第三条 2 第二条 2
select a1.id,a2.title,a1.link from news a1,news a2 where a1.link = a2.id and a1.id= 1208
select a1.id,a2.title,a1.link from news a1,news a2 where a1.link = a2.id and a1.id= 2
1第一条
2第二条
3第三条
4NULL2怎么写select语句才能将4读到记录是:
4第二条2SELECT CASE WHEN (SELECT title FROM news WHERE link = 2) = NULL THEN (SELECT title FROM news WHERE id = 2) ELSE (SELECT title FROM news WHERE link = 2) END;
-> ;
+----+-------+------+
| id | title | link |
+----+-------+------+
| 1 | test1 | |
| 2 | test2 | |
| 3 | test3 | |
| 4 | NULL | 2 |
+----+-------+------+
4 rows in set (0.00 sec)mysql> SELECT CASE WHEN ((SELECT title FROM news WHERE id = 4) = NULL) THEN (SELECT title FROM news WHERE id = 4) ELSE (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) END;
+--------------------------
---------------------------
------------------------+
| CASE WHEN ((SELECT title
OM news WHERE id = 4) ELSE
news WHERE id = 4)) END |
+--------------------------
---------------------------
------------------------+
| test2 |
+--------------------------
---------------------------
------------------------+
上面的逻辑写错了,汗一个
这个正确
SELECT CASE WHEN ((SELECT title FROM news WHERE id = 4) = NULL) THEN (SELECT title FROM news WHERE id = 4) ELSE (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) END;
from news as n1
left join news as n2 on n1.link=n2.id;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
很显然你不能通过这些比较得到有意义的结果。相反使用IS NULL和IS NOT NULL操作符:mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+于是 最后正确的是(我真菜。):
SELECT CASE WHEN (SELECT title FROM news WHERE id = 4) IS NULL THEN (SELECT title FROM news WHERE id = (SELECT link FROM news WHERE id = 4)) ELSE (SELECT title FROM news WHERE id = 4) END;
select n1.*, n2.title as title2
from news as n1
left join news as n2 on n1.link=n2.id;我需要的是根据指向ID替换该记录。
这个方法会产生一个新的输出列title。
我希望的结果其实是:
id title link
1 第一条
2 第二条
3 第三条
4 NULL 2
数位为=>
id title link
1 第一条
2 第二条
3 第三条
2 第二条 2