向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

我将截断添加到查询中，因为我意识到，如果ji.issuenum的十进制值为4.0，您仍然会得到‘Project4.0’。CONCAT不舍入或修剪值，它只是将它们显示为“原样”，甚至作为字符串的等价物。Truncate(...，0)将在小数点右侧显示0位数字。

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