mysql - 从MySQL语法转移两个查询到 PostgreSQL ( tt rss相关)

  显示原文与译文双语对照的内容
0 0

我的Having 安装有点麻烦- 我的数据库太大了,所以现在清理不起作用。

我找到了一个手动解决方案,但是查询是针对MySQL的,我正在使用 PostgreSQL,所以它不工作。

这是一种 Join,但我的基本知识并不是帮助我,而且尝试与文档争用之后,我是:

有问题的查询:


DELETE from ttrss_user_entries USING ttrss_entries, ttrss_user_entries 
WHERE id = ref_id 
 AND date_entered <DATE_SUB(NOW(), INTERVAL 14 DAY) 
 AND marked = false;

第二个( 我想是删除ttrss_entries里剩余的东西):


DELETE FROM ttrss_entries 
WHERE (SELECT COUNT(int_id) 
 FROM ttrss_user_entries 
 WHERE ref_id = id) = 0;

感谢你提前:)

/编辑

抱歉,没有这种结构,它是模糊的:

因此,ttrss_entries看起来像这样:


CREATE TABLE ttrss_entries
(
 id serial NOT NULL,
 title text NOT NULL,
 guid text NOT NULL,
 link text NOT NULL,
 updated timestamp without time zone NOT NULL,
 content text NOT NULL,
 content_hash character varying(250) NOT NULL,
 cached_content text,
 no_orig_date boolean NOT NULL DEFAULT false,
 date_entered timestamp without time zone NOT NULL,
 date_updated timestamp without time zone NOT NULL,
 num_comments integer NOT NULL DEFAULT 0,
 comments character varying(250) NOT NULL DEFAULT ''::character varying,
 plugin_data text,
 author character varying(250) NOT NULL DEFAULT ''::character varying,
 CONSTRAINT ttrss_entries_pkey PRIMARY KEY (id),
 CONSTRAINT ttrss_entries_guid_key UNIQUE (guid)
)

和 ttrss_user_entries:


CREATE TABLE ttrss_user_entries
(
 int_id serial NOT NULL,
 ref_id integer NOT NULL,
 uuid character varying(200) NOT NULL,
 feed_id integer,
 orig_feed_id integer,
 owner_uid integer NOT NULL,
 marked boolean NOT NULL DEFAULT false,
 published boolean NOT NULL DEFAULT false,
 tag_cache text NOT NULL,
 label_cache text NOT NULL,
 last_read timestamp without time zone,
 score integer NOT NULL DEFAULT 0,
 last_marked timestamp without time zone,
 last_published timestamp without time zone,
 note text,
 unread boolean NOT NULL DEFAULT true,
 CONSTRAINT ttrss_user_entries_pkey PRIMARY KEY (int_id),
 CONSTRAINT ttrss_user_entries_feed_id_fkey FOREIGN KEY (feed_id)
 REFERENCES ttrss_feeds (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE CASCADE,
 CONSTRAINT ttrss_user_entries_orig_feed_id_fkey FOREIGN KEY (orig_feed_id)
 REFERENCES ttrss_archived_feeds (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE SET NULL,
 CONSTRAINT ttrss_user_entries_owner_uid_fkey FOREIGN KEY (owner_uid)
 REFERENCES ttrss_users (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE CASCADE,
 CONSTRAINT ttrss_user_entries_ref_id_fkey FOREIGN KEY (ref_id)
 REFERENCES ttrss_entries (id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE CASCADE
)

我不想弄乱上面的查询,因为我不太确定我掌握了什么,我可以能会改变一些重要的:

/EDIT2

我对这里查询具有 Having 成功:


SELECT * FROM ttrss_user_entries
JOIN ttrss_entries ON (ref_id = id)
WHERE ttrss_entries.author = 'something';

但是这个查询显示两个表中的列,所以我不确定这是否会从两个表中删除内容,或者'

ttrss_entries.date_entered <DATE_SUB(NOW(), INTERVAL 14 DAY) 给我带来了麻烦 ERROR: syntax error at or near"14"

时间: 原作者:

0 0

这是第一个删除查询:


DELETE FROM ttrss_user_entries dd
WHERE dd.marked = false
AND EXISTS (
 SELECT *
 FROM ttrss_entries ex
 WHERE ex.id = dd.ref_id
 AND ex.date_entered <now() - '14 day'::interval
 );

这是第二个删除:


 -- remove unreferenced rows from _entries
DELETE FROM ttrss_entries dd
WHERE NOT EXISTS (
 SELECT *
 FROM ttrss_user_entries nx
 WHERE nx.ref_id = dd.id
 );

Update: Posgres还支持( 非标准) USING 语法,这基本上是 JOIN 。 它几乎与mysql语法相似;只需用 USING 替换逗号,然后删除目标表的双条目。 这是第一次删除:


DELETE FROM ttrss_user_entries dd
USING ttrss_entries ex
WHERE ex.id = dd.ref_id
AND dd.marked = false
AND ex.date_entered <now() - '14 day'::interval
 ;

最后查询:


SELECT tue.* FROM ttrss_user_entries tue
JOIN ttrss_entries te ON tue.ref_id = te.id
WHERE te.author = 'something'
 ;

或者( 更清晰,避免被列出两次( 如果 1: n 是另一个方法) ) ):


SELECT *
FROM ttrss_user_entries tue
WHERE EXISTS (
 SELECT *
 FROM ttrss_entries te
 WHERE te.id = tue.ref_id
 AND te.author = 'something'
 ;

原作者:
...