您最好在客户端中抑制它的重复(例如,在Jasper Reports中取消选中Print Repeated Value
或在XML set中isPrintRepeatedValues="false"
)
WITH ns
AS (SELECT Row_number() OVER (PARTITION BY name ORDER BY id) rn,
id,
name,
registered
FROM t
WHERE registered = 'N')
SELECT t.id,
t.name,
ns.id,
ns.name
FROM ns
LEFT JOIN t
ON ns.name = t.name
AND t.registered = 'Y'
AND ns.rn = 1
WHERE ns.name IN (SELECT name
FROM t
WHERE registered = 'Y')
ORDER BY ns.name
SELECT t.id,
t.name,
ns.id,
ns.name
FROM t ns
LEFT JOIN (SELECT MIN(id) id,
name
FROM t
WHERE registered = 'N'
GROUP BY name) MINNS
ON ns.id = MINNS.id
LEFT JOIN t
ON ns.name = t.name
AND t.registered = 'Y'
AND MINNS.id IS NOT NULL
WHERE ns.registered = 'N'
AND ns.name IN (SELECT name
FROM t
WHERE registered = 'Y')
ORDER BY ns.name,
ns.id