我有两个类别,我想获取每个类别的三个记录,后来我找到了这个链接使用 Codeigniter 活动记录模式进行 UNION 查询 https://stackoverflow.com/questions/2040655/union-query-with-codeigniters-active-record-pattern#answer-14658160之后我更改我的 DB_Active_rec 文件并添加此代码
var $unions = array();
public function union_push($table = '') {
if ($table != '') {
$this->_track_aliases($table);
$this->from($table);
}
$sql = $this->_compile_select();
array_push($this->unions, $sql);
$this->_reset_select();
}
public function union_flush() {
$this->unions = array();
}
public function union() {
$sql = '(' . implode(') union (', $this->unions) . ')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
public function union_all() {
$sql = '(' . implode(') union all (', $this->unions) . ')';
$result = $this->query($sql);
$this->union_flush();
return $result;
}
然后我创建基于 codeigniter 的函数的查询,如下所示
$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$this->db->select("*");
$this->db->from("media m");
$this->db->join("category c", "m.category_id=c.id", "INNER");
$this->db->order_by("m.media_files", "DESC");
$this->db->limit(3);
$this->db->union_push();
$getMedia = $this->db->union_all();
创建这个
(SELECT * FROM media m INNER JOIN category c ON
m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
UNION ALL
(SELECT * FROM media m INNER JOIN category c ON
m.category_id = c.id ORDER BY m.media_files DESC LIMIT 3)
现在它正在获取记录,但不正确,我只想使用查询,它显示 6 条记录,第一个查询获取 3 条记录,第二个查询获取 3 条记录,现在记录是重复的我检查记录的 id,它是 6,5,4,再次是 6 ,5,4。也可以通过以下方式完成PHP
但我想使用查询。提前致谢