SQL CASE语句设置列值不起作用 [英] SQL CASE statement to set column value not working
本文介绍了SQL CASE语句设置列值不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个连接多个表的查询，并且我试图使用case语句来创建几列，以确定无法正常工作的值（超链接文本）。我想查看联接产生的行，如果左联接之一的值不为null，那么我想用该值连接一些文本，如果为null，我将用另一个值连接文本。查询为：
I have a query that joins several tables and I am trying to create a couple of columns using case statements to determine the value (a hyper link text) that is not working as expected. I want to look at the row resulting from the joins and if a value from one of the left joins is not null, then I want to concatenate some text with the value, if it is null I will concatenate text with a different value. The query is:
SELECT p.*, p.id AS ppe_id, CONCAT(u.last_name, ',', u.first_name)
AS name, sub1.created_at AS last_inspection,
CASE
WHEN (sub1.created_at IS NULL AND man_date < now() - interval '1 year' )
THEN 'over-due'
WHEN (sub1.created_at IS NULL AND man_date < now() - interval '11 months' )
THEN 'due'
WHEN sub1.created_at < now() - interval '11 months'
THEN 'due'
WHEN sub1.created_at < now() - interval '1 year'
THEN 'over-due'
ELSE 'none'
END
AS inspection_due,
CASE
WHEN now() > p.man_date + interval '10 years'
THEN 'over-due'
WHEN now() > p.man_date + interval '119 months'
THEN 'due'
ELSE 'none'
END
AS expiring,
CASE
WHEN cl_sub.ppe_id IS NOT NULL
THEN '<a class="btn btn-xs btn-back" href="/cleanings/' || cl_sub.id || '">Cleaning</a>'
WHEN cl_sub.ppe_id IS NULL
THEN '<a class="btn btn-xs btn-success" href="/ppes/'|| cl_sub.ppe_id || '/cleanings/new">Clean</a>'
END
AS cleaning_button,
CASE
WHEN r_sub.ppe_id IS NOT NULL
THEN '<a class="btn btn-xs btn-back" href="/repairs/' || r_sub.id || '">Repairing</a>'
WHEN r_sub.ppe_id IS NULL
THEN'<a class="btn btn-xs btn-success" href="/ppes/' || r_sub.ppe_id || '/repairs/new">Repair</a>'
END
AS repair_button
FROM ppes p
JOIN users u
ON p.user_id = u.id
LEFT OUTER JOIN (SELECT DISTINCT ppe_id, id FROM cleanings cl WHERE cleaning_date IS NULL ) AS cl_sub
ON p.id = cl_sub.ppe_id
LEFT OUTER JOIN (SELECT DISTINCT ppe_id, id FROM repairs r WHERE completed IS NULL ) AS r_sub
ON p.id = r_sub.ppe_id
LEFT OUTER JOIN (SELECT t.ppe_id, t.created_at
FROM inspections t
INNER JOIN (SELECT ppe_id, max(created_at) as LastInsp
FROM inspections
GROUP BY ppe_id ) tm
ON t.ppe_id = tm.ppe_id AND t.created_at = tm.LastInsp
WHERE (t.advanced = true AND t.passed = true) order by t.ppe_id) AS sub1
ON sub1.ppe_id = p.id;
问题在于，即使两个联接都产生了预期的效果，即它们要么想出一个每个子查询的ID或它们都为null，我只从每个case语句的前半部分得到输出：
The issue is that even though the two joins produce the desired effect, i.e. they either come up with an id for either of the sub query or they come up with null, I only get output from the first half of each case statement:
CASE
WHEN cl_sub.ppe_id IS NOT NULL
THEN '<a class="btn btn-xs btn-back" href="/cleanings/' || cl_sub.id || '">Cleaning</a>'
WHEN cl_sub.ppe_id IS NULL
THEN '<a class="btn btn-xs btn-success" href="/ppes/'|| cl_sub.ppe_id || '/cleanings/new">Clean</a>'
END
AS cleaning_button,
CASE
WHEN r_sub.ppe_id IS NOT NULL
THEN '<a class="btn btn-xs btn-back" href="/repairs/' || r_sub.id || '">Repairing</a>'
WHEN r_sub.ppe_id IS NULL
THEN'<a class="btn btn-xs btn-success" href="/ppes/' || r_sub.ppe_id || '/repairs/new">Repair</a>'
END
AS repair_button
如果我查询表对于这些字段，它表明在清理表或维修表在联接中返回ID的情况下，它会放入所需的值，但否则将其留空，而不是在case语句中放入第二个值：
If I query the table for those fields it shows that the cases where the cleanings table or the repairs table returned an ID in the join, it puts in the desired value, but otherwise it leaves it blank instead of putting in the second value from the case statement:
ppe_development=# select id, cleaning_button, repair_button from test_view_ppes order by cleaning_button, repair_button;
id | cleaning_button | repair_button
-----+------------------------------------------------------------------+-----------------------------------------------------------------
126 | <a class="btn btn-xs btn-back" href="/cleanings/13">Cleaning</a> |
13 | <a class="btn btn-xs btn-back" href="/cleanings/24">Cleaning</a> |
115 | <a class="btn btn-xs btn-back" href="/cleanings/27">Cleaning</a> |
113 | <a class="btn btn-xs btn-back" href="/cleanings/5">Cleaning</a> | <a class="btn btn-xs btn-back" href="/repairs/3">Repairing</a>
114 | <a class="btn btn-xs btn-back" href="/cleanings/6">Cleaning</a> | <a class="btn btn-xs btn-back" href="/repairs/14">Repairing</a>
53 | <a class="btn btn-xs btn-back" href="/cleanings/8">Cleaning</a> |
1 | <a class="btn btn-xs btn-back" href="/cleanings/9">Cleaning</a> | <a class="btn btn-xs btn-back" href="/repairs/13">Repairing</a>
54 | | <a class="btn btn-xs btn-back" href="/repairs/16">Repairing</a>
57 | | <a class="btn btn-xs btn-back" href="/repairs/17">Repairing</a>
56 | | <a class="btn btn-xs btn-back" href="/repairs/22">Repairing</a>
58 | | <a class="btn btn-xs btn-back" href="/repairs/25">Repairing</a>
118 | | <a class="btn btn-xs btn-back" href="/repairs/26">Repairing</a>
7 | | <a class="btn btn-xs btn-back" href="/repairs/28">Repairing</a>
15 | | <a class="btn btn-xs btn-back" href="/repairs/30">Repairing</a>
11 | | <a class="btn btn-xs btn-back" href="/repairs/32">Repairing</a>
120 | | <a class="btn btn-xs btn-back" href="/repairs/33">Repairing</a>
123 | | <a class="btn btn-xs btn-back" href="/repairs/5">Repairing</a>
116 | | <a class="btn btn-xs btn-back" href="/repairs/7">Repairing</a>
78 | |
ppes表：
id | serial | man_date | category | created_at | updated_at | user_id | note | size | manufacturer | active | cost | cbrn_rated
---+--------+------------+----------+----------------------------+----------------------------+---------+------+------+--------------+--------+------+------------
12 | 10007 | 2017-01-25 | jackets | 2017-01-25 17:40:10.652715 | 2017-04-11 00:46:58.154629 | 5 | | | | t | 0.00 |
18 | 10013 | 2017-01-25 | jackets | 2017-01-25 17:40:10.663324 | 2017-04-11 00:46:58.187638 | 6 | | | | t | 0.00 |
20 | 10015 | 2017-01-25 | gloves | 2017-01-25 17:40:10.66659 | 2017-04-11 00:46:58.199712 | 6 | | | | t | 0.00 |
24 | 10019 | 2017-01-25 | jackets | 2017-01-25 17:40:10.673236 | 2017-04-11 00:46:58.228069 | 8 | | | | t | 0.00 |
26 | 10021 | 2017-01-25 | gloves | 2017-01-25 17:40:10.67665 | 2017-04-11 00:46:58.239997 | 8 | | | | t | 0.00 |
清洁表：
id | cleaning_date | user_id | ppe_id | notes | created_at | updated_at | failed_inspection
---+---------------+---------+--------+----------------------------+----------------------------+----------------------------+-------------------
5 | | 1 | 113 | 1 | 2017-04-01 21:11:28.882502 | 2017-04-01 21:11:28.882502 | f
6 | | 1 | 114 | 2 | 2017-04-01 21:11:41.068899 | 2017-04-01 21:11:41.068899 | f
8 | | 1 | 53 | Bamboo | 2017-04-07 19:36:17.48159 | 2017-04-07 19:36:17.48159 | f
13 | | 1 | 126 | testing new routing | 2017-06-25 20:38:16.813986 | 2017-06-25 20:38:16.813986 | f
维修表：
id | order_date | completed | user_id | ppe_id | notes | created_at | updated_at | failed_inspection | cost
---+------------+-----------+---------+--------+----------------------+----------------------------+----------------------------+-------------------+------
5 | | | 1 | 123 | testing photo upload | 2017-04-03 19:20:37.175447 | 2017-04-03 19:20:37.175447 | f | 0.00
7 | | | 1 | 116 | testing large photo. | 2017-04-03 19:56:12.391366 | 2017-04-03 19:56:12.391366 | f | 0.00
13 | | | 1 | 1 | acl test | 2017-04-04 07:35:59.974909 | 2017-04-04 07:35:59.974909 | f | 0.00
14 | | | 1 | 114 | rover1 upload\r +| 2017-04-04 07:57:16.602674 | 2017-04-04 07:58:50.505527 | f | 0.00
16 | | | 1 | 54 | | 2017-04-04 08:53:29.394382 | 2017-04-04 08:53:29.394382 | f | 0.00
id | passed | user_id | ppe_id | created_at | advanced
---+--------+---------+--------+----------------------------+----------
22 | f | 7 | 6 | 2017-06-20 17:39:37.253423 | f
34 | f | 1 | 4 | 2017-06-22 20:07:24.214546 | f
23 | f | 9 | 20 | 2017-06-20 20:00:02.506964 | f
26 | t | 5 | 42 | 2017-06-20 20:07:23.207904 | f
24 | t | 10 | 4 | 2017-06-20 20:01:28.161158 | f
推荐答案
如果 r_sub.ppe_id如果是NULL ，则连接也将变为 NULL ...
If r_sub.ppe_id IS NULL then the concatenation will become NULL, too...
几乎如果 SQL 中的每个函数和操作的任何参数为 NULL ，则返回 NULL 。 （NULL解释为未知值 ...）
As almost every function and operation in SQL returns NULL if any of its arguments is NULL. (NULL interpreted as "unknown value"...)
这篇关于SQL CASE语句设置列值不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
