这将帮助你找到正确的逻辑。您需要使用嵌套选择。

SELECT id, 
       visit, 
       date, 
       weight, 
       height 
FROM   (SELECT id, 
               visit, 
               date, 
               Sum(weight) AS weight, 
               0           AS height 
        FROM   table 
        WHERE  type = ‚weight‘ 
        GROUP  BY id, 
                  visit, 
                  date 
        UNION ALL 
        SELECT id, 
               visit, 
               date, 
               0           AS weight, 
               Sum(height) AS height 
        FROM   table 
        WHERE  type = ‚height‘ 
        GROUP  BY id, 
                  visit, 
                  date);

您正在处理一个EAV表，Member_id、Visit_no和Date的组合是实体，Type是属性，Value是值。
一种选择是聚合数据，以便从四个源行获得两个结果行。

select
  member_id, visit_no, date,
  max(case when type = 'weight' then value end) as weight,
  max(case when type = 'height' then value end) as height
from mytable
group by member_id, visit_no, date
order by member_id, visit_no, date;

这被称为“条件聚合”，因为我们在条件上聚合。组中具有属性“weight”的所有行的最大值只是一个权重值，因为每个组和属性只有一行。这就是为什么你说你没有聚合，但在SQL中，这是一个聚合，我们必须使用MIN或MAX（如果我们想混淆读者，甚至可以使用SUM或AVG）来获得一个值。
PIVOT子句也可以这样做。
另一种方法是将这两个数据集，一个用于重量，一个用于高度，然后连接：

with
  weights as
  (
    select member_id, visit_no, date, value as weight
    from mytable
    where type = 'weight'
  ),
  heights as
  (
    select member_id, visit_no, date, value as height
    from mytable
    where type = 'height'
  )
select *
from weights full outer join heights using (member_id, visit_no, date)
order by member_id, visit_no, date;