Если ваша система поддерживает оконные функции, это будет работать:
SELECT employee, system1, system2, system3
FROM (
SELECT employee
,system1
,cast(NULL AS text) AS system2
,cast(NULL AS text) AS system3
,count(*) OVER (PARTITION BY system1) AS ct
FROM tbl1
UNION ALL
SELECT employee
,NULL -- cast and column name only needed in first SELECT in Postgres
,system2
,NULL
,count(*) OVER (PARTITION BY system2) AS ct
FROM tbl1
UNION ALL
SELECT employee
,NULL
,NULL
,system3
,count(*) OVER (PARTITION BY system3) AS ct
FROM tbl1
) x
WHERE ct > 1
ORDER BY system1, system2, system3;
Или, возможно, быстрее:
Обратите внимание, что "John Doe", совместно использующий несколько систем, указан только один раз в следующих запросах (в отличие от первого) со всеми его общими системами. Для систем без общего доступа установлено значение NULL
.
SELECT employee
,CASE WHEN ct1 > 1 THEN system1 ELSE NULL END AS system1
,CASE WHEN ct2 > 1 THEN system2 ELSE NULL END AS system2
,CASE WHEN ct3 > 1 THEN system3 ELSE NULL END AS system3
FROM (
SELECT employee, system1, system2, system3
,count(*) OVER (PARTITION BY system1) AS ct1
,count(*) OVER (PARTITION BY system2) AS ct2
,count(*) OVER (PARTITION BY system3) AS ct3
FROM tbl1
) x
WHERE ct1 > 1 OR ct2 > 1 OR ct3 > 1
ORDER BY system1, system2, system3; -- depends on what you want
Или, если ваша анонимная система поддерживает общие табличные выражения:
WITH x AS (
SELECT employee, system1, system2, system3
,count(*) OVER (PARTITION BY system1) AS ct1
,count(*) OVER (PARTITION BY system2) AS ct2
,count(*) OVER (PARTITION BY system3) AS ct3
FROM tbl1
)
SELECT employee
,CASE WHEN ct1 > 1 THEN system1 ELSE NULL END AS system1
,CASE WHEN ct2 > 1 THEN system2 ELSE NULL END AS system2
,CASE WHEN ct3 > 1 THEN system3 ELSE NULL END AS system3
FROM x
WHERE ct1 > 1 OR ct2 > 1 OR ct3 > 1
ORDER BY system1, system2, system3; -- depends
Если у вас нет ни CTE, ни оконных функций:
(Должно работать со всеми основными СУБД, включая MySQL.)
SELECT t.employee, s1.system1, s2.system2, s3.system3
FROM tbl1 t
LEFT JOIN (SELECT system1 FROM tbl1 GROUP BY 1 HAVING count(*) > 1) s1
ON t.system1 = s1.system1
LEFT JOIN (SELECT system2 FROM tbl1 GROUP BY 1 HAVING count(*) > 1) s2
ON t.system2 = s2.system2
LEFT JOIN (SELECT system3 FROM tbl1 GROUP BY 1 HAVING count(*) > 1) s3
ON t.system3 = s3.system3
WHERE s1.system1 IS NOT NULL
OR s2.system2 IS NOT NULL
OR s3.system3 IS NOT NULL
ORDER BY s1.system1, s2.system2, s3.system3; -- depends
Протестировано с PostgreSQL 9.1.4.
person
Erwin Brandstetter
schedule
14.08.2012