mysql - LPAD/RPAD : Align column

Question

我想对齐我的专栏“Gesamt”:

SELECT k.KUNDENID AS ID, 
       vorname AS Vorname, 
       nachname AS Nachname, 
       CONCAT(IFNULL(ROUND(SUM(Betrag), 2), 0), LPAD('Euro', 4, ' ')) AS Gesamt  
FROM kunde k  
LEFT JOIN verkauf v ON k.KUNDENID = v.KUNDENID  
GROUP BY k.KUNDENID;

输出:

+----+---------+-----------+-------------+
| ID | Vorname | Nachname  | Gesamt      |
+----+---------+-----------+-------------+
|  1 | Homer   | Simpson   | 20.95Euro   |
|  2 | Doug    | Heffernan | 120.50Euro  |
|  3 | Dexter  | Morgan    | 350.30Euro  |
|  4 | Sheldon | Cooper    | 1500.00Euro |
|  5 | Maurice | Moss      | 0.00Euro    |
+----+---------+-----------+-------------+

所以我想要的输出看起来像:

+----+---------+-----------+--------------+
| ID | Vorname | Nachname  | Gesamt       |
+----+---------+-----------+--------------+
|  1 | Homer   | Simpson   |   20.95 Euro |
|  2 | Doug    | Heffernan |  120.50 Euro |
|  3 | Dexter  | Morgan    |  350.30 Euro |
|  4 | Sheldon | Cooper    | 1500.00 Euro |
|  5 | Maurice | Moss      |    0.00 Euro |
+----+---------+-----------+--------------+

如何正确对齐？

感谢您的帮助!

Answer 1

通过连接 '<space>Euro' 来简化连接而不是 LPAD('Euro',4,' ')并在整个 concat 中添加一个 LPAD 使其右移(您可以根据您的数据决定需要多少个字符才能对齐):

SELECT k.KUNDENID AS ID, 
       vorname AS Vorname, 
       nachname AS Nachname, 
       LPAD(
         CONCAT(IFNULL(ROUND(SUM(Betrag), 2), 0), ' Euro')
       , 13, ' ') AS Gesamt  
FROM kunde k  
LEFT JOIN verkauf v ON k.KUNDENID = v.KUNDENID  
GROUP BY k.KUNDENID;