WHERE ... IN条件和子查询中的多列 [英] WHERE ... IN condition and multiple columns in subquery
本文介绍了WHERE ... IN条件和子查询中的多列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否可以重写SQL查询
Is it please possible to rewrite the SQL query
SELECT DISTINCT ON (uid)
uid,
female,
given,
photo,
place
FROM words_social
WHERE uid IN (SELECT player1 FROM games)
OR uid IN (SELECT player2 FROM games)
ORDER BY uid, stamp DESC
在子查询中获取第一列player1，然后从同一表中获取列player2?
where first column player1 is fetched in a subquery and then column player2 is fetched from the same table?
我已经搜索了一下，似乎应该在此处使用JOIN而不是2个子查询，但是无法确切地知道如何操作.
I've searched around and it seems that a JOIN should be used here instead of the 2 subqueries, but can not figure out exactly how.
仅提供更多背景信息-以下是有问题的2张桌子
Just to give more context - below are the 2 tables in question
CREATE TABLE words_social (
sid varchar(255) NOT NULL,
social integer NOT NULL CHECK (0 <= social AND social <= 6),
female integer NOT NULL CHECK (female = 0 OR female = 1),
given varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo varchar(255) CHECK (photo ~* '^https?://...'),
place varchar(255),
stamp integer NOT NULL, /* Only the most recent stamp is used */
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
finished timestamptz,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
mid integer /* REFERENCES words_moves */,
score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);
和实际的CTE查询，效果很好，但我想对其进行优化:
and the actual CTE query, which works well, but I would like to optimize it:
CREATE OR REPLACE FUNCTION words_get_games(in_uid integer)
RETURNS TABLE (
out_gid integer,
out_created integer,
out_finished integer,
out_player1 integer,
out_player2 integer,
out_played1 integer,
out_played2 integer,
out_score1 integer,
out_score2 integer,
out_hand1 text,
out_hand2 text,
out_letters varchar[15][15],
out_values integer[15][15],
out_bid integer,
out_last_tiles jsonb,
out_last_score integer,
out_female1 integer,
out_female2 integer,
out_given1 varchar,
out_given2 varchar,
out_photo1 varchar,
out_photo2 varchar,
out_place1 varchar,
out_place2 varchar
) AS
$func$
WITH games AS (
SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.player1,
g.player2, -- can be NULL
EXTRACT(EPOCH FROM g.played1)::int AS played1,
EXTRACT(EPOCH FROM g.played2)::int AS played2,
g.score1,
g.score2,
ARRAY_TO_STRING(g.hand1, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g') AS hand2,
g.letters,
g.values,
g.bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player1 = in_uid
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
UNION SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.player2 AS player1,
g.player1 AS player2, -- can not be NULL
EXTRACT(EPOCH FROM g.played2)::int AS played1,
EXTRACT(EPOCH FROM g.played1)::int AS played2,
g.score2 AS score1,
g.score1 AS score2,
ARRAY_TO_STRING(g.hand2, '') AS hand1,
REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g') AS hand2,
g.letters,
g.values,
g.bid,
m.tiles AS last_tiles,
m.score AS last_score
FROM words_games g LEFT JOIN words_moves m USING(mid)
WHERE g.player2 = in_uid
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
),
social AS (
SELECT DISTINCT ON (uid)
uid,
female,
given,
photo,
place
FROM words_social
WHERE uid IN (SELECT player1 FROM games) /* How to optimize? */
OR uid IN (SELECT player2 FROM games)
ORDER BY uid, stamp DESC
)
SELECT
g.gid,
g.created,
g.finished,
g.player1,
g.player2,
g.played1,
g.played2,
g.score1,
g.score2,
g.hand1,
g.hand2,
g.letters,
g.values,
g.bid,
g.last_tiles,
g.last_score,
s1.female,
s2.female,
s1.given,
s2.given,
s1.photo,
s2.photo,
s1.place,
s2.place
FROM games g
LEFT OUTER JOIN social s1 ON g.player1 = s1.uid
LEFT OUTER JOIN social s2 ON g.player2 = s2.uid;
$func$ LANGUAGE sql;
推荐答案
只是为了证明您不需要CTE，这是您在不使用CTE的情况下重写的查询.
Just to prove that you don't need the CTEs, here is your query rewritten without them.
- 不得不猜测一些表结构，因为问题不完整
- 准备好的语句而不是函数(子句法相似)
- 改写player1<-> player2复制品；这可以通过CASE表达式等轻松地处理
- 使用
not exists重新编写最近的社交记录(也可以通过row_number() OVER (partition by uid ORDER BY tstamp DESC) rn ... where rn=1完成
- 删除了一些装饰性FD字段
- had to guess some table structures, because the question was incomplete
- a prepared statement instead of a function(the subsynstax is similiar)
- rewrote the player1<-->player2 duplication; this can easily be handled via a CASE expression or the like
- rewrote the most recent social record using
not exists(could also be done via arow_number() OVER (partition by uid ORDER BY tstamp DESC) rn ... where rn=1 - removed some decorative FD fields
PREPARE rewrite2(integer) AS
SELECT g.gid
, EXTRACT(EPOCH FROM g.created)::int AS created
, EXTRACT(EPOCH FROM g.finished)::int AS finished
, g.player1
, g.player2 -- can be NULL
, EXTRACT(EPOCH FROM g.played1)::int AS played1
, EXTRACT(EPOCH FROM g.played2)::int AS played2
, g.score1
, g.score2
, ARRAY_TO_STRING(g.hand1, '') AS hand1
, REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g') AS hand2
, g.letters
, g.values
, g.bid
, m.tiles AS last_tiles
, m.score AS last_score
, s1.female AS female1
, s1.given AS given1
, s2.female AS female2
, s2.given AS given2
FROM words_games g
LEFT JOIN words_moves m USING(mid)
LEFT JOIN words_social s1 ON s1.uid = g.player1
AND NOT EXISTS( SELECT *
FROM words_social nx WHERE s1.uid = nx.uid
AND nx.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = g.player2
AND NOT EXISTS( SELECT *
FROM words_social nx WHERE s2.uid = nx.uid
AND nx.stamp > s2.stamp)
WHERE (g.player1 = $1 OR g.player2 = $1)
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day')
;
EXPLAIN EXECUTE rewrite2(1);
这篇关于WHERE ... IN条件和子查询中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
