我有5张表A,A1,A2,A3,A4。
A与其余4个表有外键关系。
我想先在A1检查可用性,然后在A2检查可用性,依此类推。
所以结果应该按照它在A1,A2,A3,A4中的可用性顺序显示。
select * from A as main
order by (select abc from A1 where main.abc = A1.abc), (select abc from A2 where main.abc = A2.abc), (select abc from A3 where main.abc = A3.abc), (select abc from A4 where main.abc = A4.abc)
如果一个表中有10条记录,那么我希望得到所有10条记录的结果,但是这些记录应该根据A1、A2、A3和A4中的可用性进行排序
我们不会感激你的帮助。
最佳答案
您可以根据存在情况进行排序:
-- sample data
CREATE TABLE a ( abc integer not null primary key);
INSERT INTO a(abc)
select generate_series(1,25);
CREATE TABLE a2 AS select * FROM a where abc %2 = 0;
CREATE TABLE a3 AS select * FROM a where abc %3 = 0;
CREATE TABLE a5 AS select * FROM a where abc %5 = 0;
CREATE TABLE a7 AS select * FROM a where abc %7 = 0;
ALTER TABLE a2 ADD primary key(abc);
ALTER TABLE a3 ADD primary key(abc);
ALTER TABLE a5 ADD primary key(abc);
ALTER TABLE a7 ADD primary key(abc);
-- Query
select * from a
ORDER BY exists (select * from a2 where abc=a.abc) desc
, exists (select * from a3 where abc=a.abc) desc
, exists (select * from a5 where abc=a.abc) desc
, exists (select * from a7 where abc=a.abc) desc
, a.abc -- tie-breaker
;
结果:
CREATE TABLE
INSERT 0 25
SELECT 12
SELECT 8
SELECT 5
SELECT 3
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
abc
-----
6
12
18
24
10
20
14
2
4
8
16
22
15
21
3
9
5
25
7
1
11
13
17
19
23
(25 rows)
关于sql - 根据另一个表中的可用性排序一个表的结果psql,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48782459/