gpt4 book ai didi

sql - 执行此查询的最佳方式是什么?也许是联盟

转载 作者:行者123 更新时间:2023-11-29 09:17:35 25 4
gpt4 key购买 nike

我有下面描述的这四个表。基本上,我的提要包含与类别相关的条目,每个类别都可以是主类别,也可以不是主类别(标记名为“principal”)。此外,每个 Feed 都可以是合作伙伴 Feed,也可以不是(标记名为“parceiro”)。

我想从合作伙伴 feed 中选择所有 feed 条目,所以我有这个:

SELECT `e` . * , `f`.`titulo` AS `feedTitulo` , `f`.`url` AS `feedUrl`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categorias` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categorias` AS `c` ON ec.categoria_id = c.id
WHERE
e.deleted =0
AND
f.parceiro =1
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
LIMIT 5

现在,我需要在此结果中包含来自主类别中的无合作伙伴提要的所有条目,我的意思是,仅包含主类别中的条目。因此,下面的查询执行此操作:

SELECT `e` . * , `f`.`titulo` AS `feedTitulo` , `f`.`url` AS `feedUrl`
FROM `feed_entries` AS `e`
INNER JOIN `feeds` AS `f` ON e.feed_id = f.id
INNER JOIN `entries_categorias` AS `ec` ON ec.entry_id = e.id
INNER JOIN `categorias` AS `c` ON ec.categoria_id = c.id
WHERE
e.deleted =0
AND
c.principal =1
AND
f.parceiro =0
GROUP BY `e`.`id`
ORDER BY `e`.`date` DESC
LIMIT 5

我需要将这些结果合并到一个查询中,限制为按日期排序 5。

UNION 是最好的解决方案吗?如果是,如何编写查询?

CREATE TABLE categorias (
id int(11) NOT NULL auto_increment,
nome varchar(100) collate utf8_unicode_ci NOT NULL,
principal int(1) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY nome (nome)
)

CREATE TABLE entries_categorias (
id int(11) NOT NULL auto_increment,
entry_id int(11) NOT NULL,
categoria_id int(11) NOT NULL,
PRIMARY KEY (id),
KEY entry_id (entry_id),
KEY categoria_id (categoria_id)
)

CREATE TABLE feeds (
id int(11) NOT NULL auto_increment,
categoria_id int(11) NOT NULL,
titulo varchar(255) collate utf8_unicode_ci NOT NULL,
link varchar(255) collate utf8_unicode_ci NOT NULL,
url varchar(255) collate utf8_unicode_ci NOT NULL,
parceiro int(1) NOT NULL,
PRIMARY KEY (id),
KEY categoria_id (categoria_id)
)

CREATE TABLE feed_entries (
id int(11) NOT NULL auto_increment,
feed_id int(11) NOT NULL COMMENT 'Testando os comentários',
titulo varchar(255) collate utf8_unicode_ci NOT NULL,
descricao text collate utf8_unicode_ci NOT NULL,
slug varchar(255) collate utf8_unicode_ci NOT NULL,
link varchar(255) collate utf8_unicode_ci NOT NULL,
permaLink varchar(255) collate utf8_unicode_ci NOT NULL,
html text collate utf8_unicode_ci NOT NULL,
`date` datetime NOT NULL,
created_at datetime NOT NULL,
deleted int(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY permaLink (permaLink),
KEY feed_id (feed_id)
)

最佳答案

您所要做的就是更改查询中的 WHERE 语句:

SELECT `e`.*, `f`.`titulo` as `feedTitulo`, `f`.`url` as `feedUrl`

FROM `feed_entries` as `e`

INNER JOIN `feeds` as `f`
ON e.feed_id = f.id

INNER JOIN `entries_categorias` as `ec`
ON ec.entry_id = e.id

INNER JOIN `categorias` AS `c`
ON ec.categoria_id=c.id

WHERE (e.deleted = 0 AND f.parceiro = 1)
OR (e.deleted = 0 AND c.principal=1 AND f.parceiro = 0)

GROUP BY `e`.`id`
ORDER BY `e`.`date` desc
LIMIT 5

新的 where 语句有两个条件,因此我们只需查询一次并检查两个条件,而不是查询同一组表/连接两次!

关于sql - 执行此查询的最佳方式是什么?也许是联盟,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3603875/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com