postgresql - 如何从包含 "CASE WHEN"语句的 Postgresql 查询中检索数据？

Question

我有这样的 postresql 查询:

SELECT * , u.image AS image_name, k.id AS cat_id,
   CASE WHEN s.web_site!="" THEN s.web_site
   ELSE 'Not available' END AS v_site
FROM users u
LEFT JOIN comments s ON s.user_id = u.id WHERE u.id = '1547'

查询结果:

users     | name    | v_site
----------+---------+---------------
ali       | hassan  | www.domain.com
turqut    | sandra  | www.somesite.com
emil      | azizov  | www.website.com

如您所见，我得到的网站名称是 www.domain.com。但我想用 HTML 超链接标签获得它:www.domain.com

我该怎么做？

Answer 1

!= "" 是无效的 SQL 语法。字符串用单个 qutoe 分隔。

SELECT *, 
       u.image AS image_name, 
       k.id AS cat_id,
       CASE 
         WHEN s.web_site != '' THEN '<a href="'||s.web_site||'">'||s.web_site||'</a>'
         ELSE 'Not available' 
       END AS v_site
FROM users u
LEFT JOIN comments s ON s.user_id = u.id WHERE u.id = '1547'