sql - 如何根据列值查询重复的行？

Question

我正在尝试弄清楚如何根据通过和失败列复制我的行。下面是我到目前为止查询过的表格。

查询代码如下所示。我正在从 json 查询。

SELECT
    to_date(LotSummary ->> 'Start', 'HH24:MI:SS DD/MM/YY')AS "Date",
    Machine AS "Machine",
    LotSummary ->> 'Pass' AS "Pass",
    LotSummary ->> 'Fail' AS "Fail"
FROM
(
    SELECT
        CASE
            WHEN jsonb_typeof(json_data->'OEESummary'->'LotSummary'->'Lot') = 'array'
            THEN
                jsonb_array_elements(cast(json_data->'OEESummary'->'LotSummary'->'Lot' AS JSONB))
            ELSE
                json_data->'OEESummary'->'LotSummary'->'Lot'
            END
        AS LotSummary,
        json_data->'OEESummary'->>'MachineID' AS Machine
    FROM
    (
        SELECT
        jsonb_array_elements(cast(json_data->>'body' AS JSONB)) AS json_data
      FROM data
    )t
    WHERE
       json_data ->> 'file_name' = 'OEE.xml'
)a
WHERE
    to_date(LotSummary ->> 'Start', 'HH24:MI:SS DD/MM/YY') IS NOT NULL

假设我想将它作为重复项进行查询，以分隔 Pass 和 Fail 值，就像:

+----------------------------+--------------+------+------+
|            Date            |   Machine    | Pass | Fail |
+----------------------------+--------------+------+------+
| "2019-08-04T16:00:00.000Z" | TRS1000i-082 | 5    | NULL |
| "2019-08-04T16:00:00.000Z" | TRS1000i-082 | NULL | 2    |
| "2019-07-01T16:00:00.000Z" | TRS1000i-001 | 0    | NULL |
| "2019-07-01T16:00:00.000Z" | TRS1000i-001 | NULL | 0    |
| "2019-07-01T16:00:00.000Z" | TRS1000i-001 | 20   | NULL |
| "2019-07-01T16:00:00.000Z" | TRS1000i-001 | NULL | 0    |
+----------------------------+--------------+------+------+

以防万一您需要 json 格式(请注意它不是准确的格式，但格式是正确的):

{
  "body": [
    {
      "file_name": "OEE.xml",
      "OEESummary": {
        "MachineID": "TRS1000i-012",
        "LotSummary": {
          "Lot": [
            {
              "@i": "0",
              "Start": "14:52:16 15/08/19",
              "Pass": "3",
              "Fail": "0"
            },
            {
              "@i": "1",
              "Start": "15:40:41 15/08/19",
              "Pass": "3",
              "Fail": "0"
            }
          ]
        },
        "Utilisation": [
          "0:01:42:48",
          "19.04%"
        ],
        "MTTR": "--",
        "IdleTime": "0:07:16:39",
        "MUBA": "57",
        "OEE": "60.55%"
      }
    }
  ],
  "header": {
    "json_metadata_revision": "v1.0.0",
    "json_metadata_datetime_creation": "14-OCT-2019_14:55:57",
    "json_metadata_uuid": "14102019145557_65b425d8-09e5-48ec-be85-e69d9a50d2e3",
    "json_metadata_type": "mvst_xml_to_json"
  }
}

如果您知道我可以用来解决此问题的任何技术，请提供帮助。非常感谢您的帮助!谢谢你。

Answer 1

对于您的表，您可以使用横向连接:

select t.date, t.machine, v.pass, v.fail
from t cross join lateral
     (values (t.pass, null), (null, t.fail)) v(pass, fail);

我不太确定您的查询与问题有什么关系。但您可以将其定义为 CTE，然后将结果用于 t。