set search_path='tmp';
DROP TABLE friendship CASCADE;
CREATE TABLE friendship
( id integer not null PRIMARY KEY
, person1 integer not null
, person2 integer not null
, status varchar
, CONSTRAINT pk1 UNIQUE (status,person1,person2)
, CONSTRAINT pk2 UNIQUE (status,person2,person1)
, CONSTRAINT neq CHECK (person1 <> person2)
);
INSERT INTO friendship(id,person1,person2,status) VALUES
(1,1,2,'friend' ) ,(2,1,3,'friend' ) ,(3,2,3,'friend' ) ,(4,3,4,'friend' )
;
CREATE VIEW flip AS (
SELECT person1 AS one
, person2 AS two
FROM friendship WHERE status = 'friend'
UNION
SELECT person2 AS one
, person1 AS two
FROM friendship WHERE status = 'friend'
);
SELECT DISTINCT
f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
;
DROP VIEW flip;
with flip AS (
SELECT person1 AS one
, person2 AS two
FROM friendship WHERE status = 'friend'
UNION
SELECT person2 AS one
, person1 AS two
FROM friendship WHERE status = 'friend'
)
SELECT DISTINCT
f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
;
结果:
SET
DROP TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "friendship_pkey" for table "friendship"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pk1" for table "friendship"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "pk2" for table "friendship"
CREATE TABLE
INSERT 0 4
CREATE VIEW
common
2
(1 row)
DROP VIEW
common
2
(1 row)
Person1 < Person2
还是这只是巧合? - Simon