代码之家 › 专栏 › 技术社区 › Keyne Viana

执行此查询的最佳方法是什么?也许联合

union mysql sql

Keyne Viana · 技术社区 · 15 年前

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

我想从partrners提要中选择所有提要条目,因此我有:

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。

联合是最佳解决方案吗?如果是,如何编写查询?

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)
)

1 回复 | 直到 15 年前

Brett 15 年前

您只需更改查询中的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语句有两个条件,因此,我们只查询一次并检查两个条件,而不是查询同一组表/联接两次!