php - Zend_Db_Select 如何从子查询中选择(派生表)

Question

如何使用 Zend_Db_Select 直接从子查询(派生表)中进行选择？

看，我有 5 个具有相同结构的表，我想从中获取所有行，合并它们并删除重复项。我正在使用 UNION 自动删除重复项。问题是我之前为每个表添加了一个静态列，所以有一个列是不同的 => 发生了重复。

到目前为止，这是我的查询:

SELECT `news_main`.*, 'main' as `category` 
FROM `news_main` 
UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` 
UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` 
UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` 
UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` 
ORDER BY `date` DESC LIMIT 8

看看我是如何将静态值添加到新列 category 的？现在其他一切都一样(有重复的行)，但由于它们来自不同的类别，UNION 无法删除它们。

所以我想我可以从这个子查询中SELECT所有行并将它们分组以删除重复项，如下所示:

SELECT * 
FROM (
    SELECT `news_main`.*, 'main' as `category` 
    FROM `news_main` 
    UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics`
    UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` 
    UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` 
    UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` 
    ORDER BY `date` DESC LIMIT 8
) as subtable 
GROUP BY `source` 
ORDER BY `date` DESC

我确实在 MySQL 中运行过它并且它运行得很好..唯一的问题是......

我如何使用 Zend_Db_Select 的奇特函数执行它？

提前致谢!

Answer 1

我不确定您是否可以在 Zend_Db_Select 的 from 构造中使用嵌套选择，或者您是否应该那样做，但另一种解决方案是只获取数据库适配器并手动构建 sql 查询。

$db = Zend_Db_Table::getDefaultAdapter();
$db->query("SELECT * 
    FROM (
        SELECT `news_main`.*, 'main' as `category` 
        FROM `news_main` 
        UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics`
        UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` 
        UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` 
        UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` 
        ORDER BY `date` DESC LIMIT 8
    ) as subtable 
    GROUP BY `source` 
    ORDER BY `date` DESC
");

相关: Zend_Db_Table subquery