时间戳

首页 > 解决方案 > PostgreSQL 将多个查询输出到数组中

sql - PostgreSQL 将多个查询输出到数组中

问题描述

在 postgreSQL 9.6 中工作：

我有许多选择语句，每个语句都输出一行

例如选择 8 作为“ColA”，选择 20 作为“ColB”，选择 13 作为“ColC”

我需要将它们放入一个数组中，例如：

["ColA",8],["ColB",20],["ColC",13]

我已经尝试了很多种 row_to_json、array_to_json 等，但没有任何乐趣，有人可以帮忙吗？

我尝试过的实际代码比上面描述的要复杂一些，但数据的结构不是......这是我现有的代码（只是为了表明我有

SELECT "JobTitle" "name",
    (

    select array_to_json(array_agg(row_to_json(d2))) from

    (SELECT 

    (select row_to_json(d2_1) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '1 hour'::interval) AND "EndDateTime">(_Date_For) THEN "Effect" ELSE 0 END) "00:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_1) 

UNION ALL

(select row_to_json(d2_10) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '11 hour'::interval) AND "EndDateTime">(_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END) "10:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_10) 

UNION ALL

     (select row_to_json(d2_11) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '12 hour'::interval) AND "EndDateTime">(_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END) "11:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_11)

    ) d2

    ) as "data" 

    FROM "tmpDashboardData" x GROUP BY "JobTitle"

标签： sqlpostgresqlpostgres-9.6

解决方案

谢谢 Fdavidov 我最终解决了这个问题......这是我的信息代码：

SELECT "JobTitle" "name",
    (
    select array_to_json(array_agg(row_to_json(d2))) from
    (SELECT  
     (select row_to_json(d2_1) from (select '00:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '1 hour'::interval) AND "EndDateTime">(_Date_For) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_1) UNION ALL
     (select row_to_json(d2_2) from (select '01:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '2 hour'::interval) AND "EndDateTime">(_Date_For + '1 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_2) UNION ALL
     (select row_to_json(d2_3) from (select '02:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '3 hour'::interval) AND "EndDateTime">(_Date_For + '2 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_3) UNION ALL
     (select row_to_json(d2_4) from (select '03:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '4 hour'::interval) AND "EndDateTime">(_Date_For + '3 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_4) UNION ALL
     (select row_to_json(d2_5) from (select '04:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '5 hour'::interval) AND "EndDateTime">(_Date_For + '4 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_5) UNION ALL
     (select row_to_json(d2_6) from (select '05:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '6 hour'::interval) AND "EndDateTime">(_Date_For + '5 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_6) UNION ALL
     (select row_to_json(d2_7) from (select '06:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '7 hour'::interval) AND "EndDateTime">(_Date_For + '6 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_7) UNION ALL
     (select row_to_json(d2_8) from (select '07:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '8 hour'::interval) AND "EndDateTime">(_Date_For + '7 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_8) UNION ALL
     (select row_to_json(d2_9) from (select '08:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '9 hour'::interval) AND "EndDateTime">(_Date_For + '8 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_9) UNION ALL
     (select row_to_json(d2_10) from (select '09:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '10 hour'::interval) AND "EndDateTime">(_Date_For + '9 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_10) UNION ALL
     (select row_to_json(d2_11) from (select '10:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '11 hour'::interval) AND "EndDateTime">(_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_11) UNION ALL
     (select row_to_json(d2_12) from (select '11:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '12 hour'::interval) AND "EndDateTime">(_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_12)
    ) d2
    ) as "data" 
    FROM "tmpDashboardData" x GROUP BY "JobTitle"

推荐阅读