sql - Postgres sql基于整数在结果中显示特定字符​​串

Question

所以我有汽车的桌子和所说的汽车的颜色。颜色显示为整数，但我想在选择查询中映射它，以便显示相应的颜色。映射表不存在，我没有对数据库的写入权限，因此无法创建映射表。该查询仅适用于仅允许 SQL 查询的仪表板。

我有一张这样的桌子：

| **Color**   | **Car**    |     
| 1       | BMW    |    
| 2       | Nissan   |   
| 1       | Tesla   |     

而这个查询

SELECT "Color", count(*)
FROM "Cars"
GROUP BY "Color"

这将给出这个结果：

| Color | Count |    
|  1  |   2  |        
|  2  |   1  |

但我想要的是这样的：

| Color | Count |    
| white  |   2  |    
| green  |   1  |

我怎样才能写这样的查询？

标签： sqlpostgresql

Answer 1

Ok so "said table doesn´t exist and I don´t have write access". There is a very simple solution here: Go talk to your DBA and ask for it. One of the stupidest ideas in IT today is developers not talking to DBAs (almost as stupid as DBA not responding to developers). Contrary to popular belief you are on the same team.
However, in the event she/he doesn't respond (thus you unable to accomplish @Gordon goal of not repeating logic) but there is a work around - at least until the DBA comes to their sinces. You create a template CTE which can be pulled into queries as needed.

with color_names (id, name) as
     ( values (1,'White')
            , (2,'Green')
     ) 

Make sure this is stored in a location accessible to the entire team. This can the be pulled in whenever needed.

Then again, on second thought, you're a developer so develop: create a function that returns a table. You can then use this function almost exactly as if it were at table and it has a massive benefit - a single point of maintenance for all queries using it. Not quite the same as just updating a table, but massively better than having to update multiple queries.

Both are incorporated in fiddle.