mysql - 在连接表 SQL 中使用 if 条件确定列数据

Question

我有一个运行良好的查询，我需要帮助的是如何合并条件来确定给定列上显示的值。也就是说，如果表的某一列的值为“COA 授权”，则添加的列日期应显示日期，否则该列应显示 null

这是我到目前为止的查询

SELECT r.request_id, r.product_name, r.created_at, r.can,a_s.stat, r.client_id, CONCAT(u.fname,' ',u.lname) 'analyst' , t.date_added (//should show the value of date_added else NULL based on a condition for this column),
    FROM request r
    LEFT OUTER JOIN assigned_samples a_s ON r.request_id = a_s.labref
    LEFT OUTER JOIN user u ON a_s.analyst_id = u.id
    LEFT OUTER JOIN tracking_table t ON r.request_id = t.labref
    WHERE r.client_id='$cid'
    AND r.created_at BETWEEN '$start' AND '$end'
    AND a_s.department_id = '$dept'
    GROUP BY r.request_id
    ORDER BY `r`.`created_at` DESC "

Answer 1

我相信，在这种情况下，一个简单的 CASE 语句就足够了。

SELECT r.request_id, r.product_name, r.created_at, r.can,a_s.stat, r.client_id, 
CONCAT(u.fname,' ',u.lname) 'analyst', 
CASE WHEN ConditionColumn = 'Authorization of COA' THEN t.date_added 
ELSE NULL END AS 'date_added' 
    FROM request r
    LEFT OUTER JOIN assigned_samples a_s ON r.request_id = a_s.labref
    LEFT OUTER JOIN user u ON a_s.analyst_id = u.id
    LEFT OUTER JOIN tracking_table t ON r.request_id = t.labref
    WHERE r.client_id='$cid'