gpt4 book ai didi

php - 不同/连接两个表

转载 作者:行者123 更新时间:2023-11-29 00:36:20 34 4
gpt4 key购买 nike

商店表:

   +--+-------+--------+
|id|name |date |
+--+-------+--------+
|1 |x |March 10|
+--+-------+--------+
|2 |y |March 10|
+--+-------+--------+

分类表:

+--+-------+
|id|title |
+--+-------+
|1 |tools |
+--+-------+
|2 |foods |
+--+-------+

商店类别表 (shop_cats):

+--+-------+--------+
|id|shop_id|cat_id |
+--+-------+--------+
|1 |1 |1 |
+--+-------+--------+
|2 |1 |2 |
+--+-------+--------+

我想按类别获取商店(类别存储在 $cat 数组中)

     $this->db->select('shops.*');
$this->db->from('shops');
if(!empty($cat))
{
$this->db->join('shop_cats' , 'shop_cats.shop_id = shops.id' );
$this->db->where_in('shop_cats.cat_id' , $cat);
}


$this->db->limit($limit , $offset);
$res = $this->db->get();

我的问题是它返回重复的结果例如在这张表中

+--+-------+--------+
|id|shop_id|cat_id |
+--+-------+--------+
|1 |1 |1 |
+--+-------+--------+
|2 |1 |2 |
+--+-------+--------+

如果我想要 (1,2) 类别的商店,我会得到 id = 1 的商店,两次。我希望它只返回每个商店一次,没有任何重复。

我试过使用分组方式

 if(!empty($cat))
{
$this->db->join('shop_cats' , 'shop_cats.shop_id = shops.id' );
$this->db->group_by('shop_cats.shop_id');
$this->db->where_in('shop_cats.cat_id' , $cat);
}

没用,我也试过了

 if(!empty($cat))
{ $this->db->select('DISTINCT shop_cats.shop_id');
$this->db->join('shop_cats' , 'shop_cats.shop_id = shops.id' );
$this->db->where_in('shop_cats.cat_id' , $cat);
}

但我收到语法错误!

最佳答案

尝试

$this->db->distinct('shops.*');
$this->db->from('shops');
$this->db->join('shop_cats', 'shop_cats.shop_id = shops.id', 'left');
$this->db->where('shop_cats.cat_id', $cat);
$this->db->limit($limit , $offset);
$res = $this->db->get();

关于php - 不同/连接两个表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14110251/

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