我有一个查询,返回多个记录,因为我使用或
SELECT m_o.ordernum, m_CD.fieldvalue
FROM measurements.orders m_o
INNER JOIN measurements.custom_data m_cd
ON m_cd.ordernum = m_o.ordernum
WHERE m_o.custnum = 'xxx'
AND (m_cd.fieldname = 'primary_name' or m_cd.fieldname = 'secondary_name');
有一种方法可以让这个返回一行,但是我被难住了
您可以使用case
,然后对ordernum进行聚合,因为您的字段名几乎有2个值。
SELECT m_o.ordernum,
max(case when m_cd.fieldname = 'primary_name' then m_CD.fieldvalue end) as col1,
max(case when m_cd.fieldname = 'secondary_name' then m_CD.fieldvalue end) as col2
FROM measurements.orders m_o
INNER JOIN measurements.custom_data m_cd
ON m_cd.ordernum = m_o.ordernum
WHERE m_o.custnum = 'xxx'
group by m_o.ordernum
要在相同的结果行中获得与主名称关联的值(如果有)和与辅助名称关联的值(如果有),可以将measurements.custom_data
连接两次。如果不能依赖于这些名称中的哪一个具有与其相关联的值,则必须使用外部连接。例如:
SELECT
m_o.ordernum,
m_cd1.fieldvalue AS fieldValue1
m_cd2.fieldvalue AS fieldValue2
FROM
measurements.orders m_o
LEFT JOIN measurements.custom_data m_cd1
ON m_cd1.ordernum = m_o.ordernum AND m_cd1.fieldname = 'primary_name'
LEFT JOIN measurements.custom_data m_cd2
ON m_cd2.ordernum = m_o.ordernum AND m_cd2.fieldname = 'secondary_name'
WHERE
m_o.custnum = 'xxx'
AND (m_cd1.ordernum IS NOT NULL OR m_cd2.ordernum IS NOT NULL)
注意,fieldname
谓词被移动到(外部)连接条件中。它指导哪个字段值进入哪个结果列,并有助于防止重复。
还要注意WHERE
子句中的NOT NULL
条件。这样可以防止返回或名称没有值的结果。