mysql - 为sql结果添加前缀

Question

我正在尝试为一个字段(下面的 ji.issuenum)添加一个前缀，它是 decimal(18,0)。这样“4”就变成了“PROJECT-4”正如我从示例中发现的那样，只需添加 +: 就足够了:

SELECT 'PROJECT-' + ji.issuenum AS project_id,
       c.cname                  AS component_name,
       pri.pname                AS issue_priority,
       ist.pname                AS issue_status,
       ji.summary               AS issue_summary,
       ji.created               AS created,
       ji.updated               AS updated,
       ji.resolutiondate        AS resolutiondate
FROM   project p
       left outer join jiraissue ji
                    ON ji.project = p.id
       left outer join priority pri
                    ON ji.priority = pri.id
       left outer join issuestatus ist
                    ON ji.issuestatus = ist.id
       left outer join component c
                    ON c.project = p.id
WHERE  p.pname = 'Project'
       AND Year(resolutiondate) = Year(current_date - interval 1 month)
       AND Month(resolutiondate) = Month(current_date - interval 1 month)
GROUP  BY ji.issuenum;  

但它不起作用。在加入之前，我还尝试将其转换为 char:

SELECT 'PROJECT-' + Cast( ji.issuenum AS CHAR(20)) AS project_id,
       c.cname                                     AS component_name,
       pri.pname                                   AS issue_priority,
       ist.pname                                   AS issue_status,
       ji.summary                                  AS issue_summary,
       ji.created                                  AS created,
       ji.updated                                  AS updated,
       ji.resolutiondate                           AS resolutiondate
FROM   project p
       left outer join jiraissue ji
                    ON ji.project = p.id
       left outer join priority pri
                    ON ji.priority = pri.id
       left outer join issuestatus ist
                    ON ji.issuestatus = ist.id
       left outer join component c
                    ON c.project = p.id
WHERE  p.pname = 'Project'
       AND Year(resolutiondate) = Year(current_date - interval 1 month)
       AND Month(resolutiondate) = Month(current_date - interval 1 month)
GROUP  BY ji.issuenum;  

还有其他方法吗？

谢谢

Answer 1

在 mysql 中使用 CONCAT 很可能会解决您的问题。它设置为使用字符串作为输入，然后连接它们，但如果您包含数值，它将把这些值转换为它们的非二进制字符串等价物。这将适用于整数和小数。

CONCAT('PROJECT-',TRUNCATE(ji.issuenum, 0)) AS Project_ID

我将 TRUNCATE 添加到查询中，因为我意识到如果 ji.issuenum 的十进制值为 4.0，您仍然会得到“PROJECT-4.0”。 CONCAT 不会舍入或修剪值，它只是“按原样”显示它们，即使是作为等效字符串。 Truncate(... , 0) 将在小数点右侧显示 0 位。

Here是 CONCAT 的一些 mysql 特定文档。