主页 / 问题 / 602444
Accepted
maestro
Asked:2020-12-12 12:58:40 +0000 UTC2020-12-12 12:58:40 +0000 UTC

如何与自己加入表格？

  • 772

原始表如下所示：

CREATE TABLE public.psi_humsensor (
  id           serial NOT NULL,
  sensorblock  integer NOT NULL,
  psi_act      integer NOT NULL,
  chamber_hum  real,
  sensor_hum   real,
  /* Keys */
  CONSTRAINT psi_humsensor_pkey
    PRIMARY KEY (id),
  /* Foreign keys */
  CONSTRAINT foreign_key01
    FOREIGN KEY (psi_act)
    REFERENCES public.psi_humsensor_acts(id)
    ON DELETE RESTRICT, 
  CONSTRAINT foreign_key02
    FOREIGN KEY (sensorblock)
    REFERENCES public.sensorblock(id)
) WITH (
    OIDS = FALSE
  );

该表填充了以下数据：

表格中的数据

我正在尝试从此表生成报告。为此，您需要创建一个查询，返回室内传感器的湿度为 25%、被测传感器的湿度为 25%、室内传感器的湿度为 95%、湿度被测传感器的 95%。写了这个查询：

SELECT
      sb.serial_number
     ,t.label_number 
     ,round(CAST(psi1.chamber_hum as numeric), 2) AS ch25
     ,round(CAST(psi1.sensor_hum as numeric), 2) AS sh25
     ,abs(round(CAST((psi1.chamber_hum - psi1.sensor_hum) as numeric), 2)) as delta25
     ,round(CAST(psi2.chamber_hum as numeric), 2) AS ch95
     ,round(CAST(psi2.sensor_hum as numeric), 2) AS sh95
     ,abs(round(CAST((psi2.chamber_hum - psi2.sensor_hum) as numeric), 2)) as delta95
FROM psi_humsensor psi1
     JOIN psi_humsensor psi2 ON (psi1.psi_act = psi2.psi_act AND psi1.sensorblock = psi2.sensorblock)
     JOIN sensorblock sb ON (sb.id = psi1.sensorblock)
     JOIN termistors t ON (t.id = sb.termistor)
WHERE 
      psi1.id <> psi2.id 
  AND psi1.psi_act = 3

运行此查询会导致重复条目，例如：

1 | 1 | 25.2 | 25.3 | 0.1 | 95.3 | 95.5 | 0.2

2 | 2 | 95.3 | 95.5 | 0.2 | 25.2 | 25.3 | 0.1

如果添加到查询SELECT DISTINCT(psi1.chamber_hum + psi2.chamber_hum)中，则重复消失，但有时记录会混淆：表的左侧有 95% 的数据，右侧有 25% 的数据。如何提出这样的要求？

sql

1 个回答

  1. Best Answer

    您根本不需要自我加入。所有必要的信息都已经存在，您只需要添加分组：

    SELECT
      sb.serial_number
     ,t.label_number 
     ,MAX(CASE WHEN psi.sensor_hum < 30 THEN
         round(CAST(psi.chamber_hum as numeric), 2)
      END) AS ch25
     ,MAX(CASE WHEN psi.sensor_hum < 30 THEN
         round(CAST(psi.sensor_hum as numeric), 2) 
      END) as sh25
     ,MAX(CASE WHEN psi.sensor_hum < 30 THEN
         abs(round(CAST((psi.chamber_hum - psi.sensor_hum) as numeric), 2))
      END) as delta25
     ,MAX(CASE WHEN psi.sensor_hum > 90 THEN
         round(CAST(psi.chamber_hum as numeric), 2)
      END) AS ch95
     ,MAX(CASE WHEN psi.sensor_hum > 90 THEN
         round(CAST(psi.sensor_hum as numeric), 2) 
      END) as sh95
     ,MAX(CASE WHEN psi.sensor_hum > 90 THEN
         abs(round(CAST((psi.chamber_hum - psi.sensor_hum) as numeric), 2))
      END) as delta95
FROM psi_humsensor psi
     JOIN sensorblock sb ON (sb.id = psi.sensorblock)
     JOIN termistors t ON (t.id = sb.termistor)
WHERE psi.psi_act = 3
GROUP BY sb.serial_number, t.label_number 
/*тут возможно надо добавить группировку по psi.sensorblock*/
    • 2

相关问题