sql问题怎么解决

ljc

A Database Error Occurred

Error Number: 1140

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

SELECT `shops`.*, count(review.catid) as number FROM (`shops`) JOIN `review` ON `shops`.`id`=`review`.`catid`

Filename: D:\AppServ\www\goodreview\system\database\DB_driver.php

Line Number: 330

这个问题怎么解决..

Hex

贴代码

ljc

Hex 发表于 2013-9-11 14:07
贴代码

public function shop_goodreview_order_by(){
                $this->db->select('shops.id ,shops.name,count(review.catid) as number');
                $this->db->from('review');
                $this->db->join('shops','review.catid=shops.id');
                $this->db->order_by('number','desc');
                $this->db->group_by(array('shops.id','shops.name'));
                $query=$this->db->get();
                return $query->result();
        }

谢谢哈 解决了 我是加了group_by就好了

ljc

Hex 发表于 2013-9-11 14:07
贴代码

我还有个问题想问
代码
public function shop_time_order_by(){                $this->db->select('(FROM_UNIXTIME(NOW()-createtime) as diff,name');
                $this->db->order_by('name','asc');
                $query=$this->db->get('shops');
                return $query->result();
        }
错误:
A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as diff, `name` FROM (`shops`) ORDER BY `name` asc' at line 1

SELECT (FROM_UNIXTIME(NOW()-createtime) as diff, `name` FROM (`shops`) ORDER BY `name` asc

Filename: D:\AppServ\www\goodreview\system\database\DB_driver.php

Line Number: 330

这怎么解决呢

ljc

Hex 发表于 2013-9-11 14:07
贴代码

额,不好意思 下面的 我少写个括号,这回好使了

Hex

太复杂的 SQL 语句建议直接用 query

ljc

Hex 发表于 2013-9-11 17:01
太复杂的 SQL 语句建议直接用 query

哦，就是写个$sql='';然后$this->db->query($sql);  对吧 是这意思吧

Hex

ljc 发表于 2013-9-11 17:25
哦，就是写个$sql='';然后$this->db->query($sql);  对吧 是这意思吧

是的。

ljc

Hex 发表于 2013-9-11 18:39
是的。

哦哦,谢啦...