我在自學Psql函式，只想回傳一行一列名：dep_people，不知道如何解決這一行一列名的問題。請幫助我理解 Psql 函式。
預覽表
SELECT * FROM test_sch.apollo_org_job_function;
|-------------------------------------------|
|organization_id |department |no_people|
|-------------------------------------------|
| 2a |accounting | 3 |
| 1a |engineering | 2 |
| 1a |entrepreneurship| 1 |
| 1a |human resources | 4 |
|-------------------------------------------|
我在這里
SELECT
department, COUNT(*) as "no_people"
FROM test_sch.apollo_org_job_function
GROUP BY department
ORDER BY department;
我的回報是：
|--------------------------|
|department |no_people|
|--------------------------|
|accounting | 3 |
|engineering | 2 |
|entrepreneurship| 1 |
|human resources | 4 |
|--------------------------|
呼叫函式
select * from test_sch.return_dep_people('1a')
as f(dep_people text);
預期輸出
------------------------------------------------------|
|dep_people |
------------------------------------------------------|
|Engineering:2 , human_resources:4, entrepreneurship:1|
-------------------------------------------------------
注意：結果應僅包含名為“dep_people”的 1 列和字串中的僅 1 行，（“Engineering:2 , human_resources:4, Entrepreneurs:1” 是單個字串）
uj5u.com熱心網友回復：
您可以使用JSON_BUILD_OBJECTand實作此目的JSON_AGG：
WITH data AS (
SELECT department, COUNT(*) as "no_people"
FROM apollo_org_job_function GROUP BY department ORDER BY department
) SELECT
JSON_AGG(JSON_BUILD_OBJECT(department, no_people)) as "dep_people"
FROM data;
PostgreSQL JSON_AGG JSON_BUILD_OBJECT
uj5u.com熱心網友回復：
我想，STRING_AGG 可以幫助你。像這樣的東西：
SELECT
STRING_AGG(d.department || ': ' || CAST(d.no_people AS text), ', ') AS dep_people
FROM ([your initial query]) AS d
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363129.html
標籤：sql PostgreSQL的 postgresql-14
下一篇：不同條件的SQL連接