在yii里面我们会经常使用到聚合函数来查询语句,在relations定义好关联关系后基于可以使用了。
比如我们使用 yii CDbCriteria的join , group 来进行连表查询,这种情况下我们是不需要定义relations的关联关系的:
$seq = [89857,89856];
$c = new CDbCriteria();
$c->addInCondition("t.putrec_seqno",$seq);
$c->addCondition("i.ie_flag = 'I'");
$c->addCondition("i.shop_id=".$shopId);
$c->addCondition("i.sta = 2");
$c->addCondition("i.message_status = 30");
$c->join = "LEFT JOIN tbl_shop i ON t.shop_id = i.id";
$c->select= "( t.deal_num ) AS SUM,t.putrec_seqno";
$c->group = "t.putrec_seqno";
$content = Goods::model()->findAll($c);
执行的sql如下:
SELECT SUM
( T.deal_num ) AS SUM,
T.putrec_seqno
FROM
"tbl_goods" "t"
LEFT JOIN tbl_shop i ON T.invt_id = i.ID
WHERE
(
(
( ( T.putrec_seqno IN ( 89857, 89856 ) ) AND ( i.ie_flag = 'I' ) )
AND ( i.shop_id = 19930 )
)
AND ( i.sta = 2 )
)
AND ( i.message_status = 30 )
GROUP BY
T.putrec_seqno
使用with方法,那么我们必须定义关联关系:
$seq = [89857,89856];
$c = new CDbCriteria();
$c->addInCondition("t.putrec_seqno",$seq);
$c->addCondition("i.ie_flag = 'I'");
$c->addCondition("i.shop_id=".$shopId);
$c->addCondition("i.sta = 2");
$c->addCondition("i.message_status = 30");
$c->with = array(
'shop' => array(
'alias' => 'i',
'joinType' => 'left join',
)
);
$c->group = 't.putrec_seqno';
$content = Goods::model()->findAll($c);
直接使用命令执行查批量:
$where = "i.shop_id={$shopId}";
$sql = "SELECT sum(t.deal_num) AS sum,t.putrec_seqno from {{goods}} t
INNER JOIN {{shop}} i ON t.shop_id = i.id
WHERE {$where} AND i.ie_flag='I' AND i.message_status=30 AND i.sta = 2 group by t.putrec_seqno";
$rows = Yii::app()->db->createCommand($sql)->queryAll();
直接使用命令执行查单条:
$where = "i.shop_id={$shopId}";
$sql = "SELECT sum(t.deal_num) AS sum from {{shop_details}} t
INNER JOIN {{shop}} i ON t.shop_id = i.id
WHERE {$where} AND i.ie_flag='E' AND i.message_status='30'";
$row = Yii::app()->db->createCommand($sql)->queryRow();
直接使用命令执行查批量翻页:
public function setShopDetaliSql($shopId, $starttime, $endtime, $page, $pageSize) {
$sql = " select g.putrec_seqno,g.matnr_no,g.hs_code,g.goods_name,g.deal_uom,g.deal_currency,g.deal_price,g.goods_model,i.shop_no,i.shop_dcl_time,g.deal_num,g.goods_seq_no from tbl_shop_goods as g LEFT JOIN tbl_shop as i on g.shop_id = i.id where i.shop_id = {$shopId} AND i.create_time >= {$starttime} AND i.create_time <= {$endtime} AND i.sta = 2 AND i.message_status = 30";
$offset = ($page - 1) * $pageSize;
$sql.=" limit ".$pageSize." offset ".$offset;
$countSql = "select count(*) from (select g.putrec_seqno,g.matnr_no,g.hs_code,g.goods_name,g.deal_uom,g.deal_currency,g.deal_price,g.goods_model from tbl_shop_goods as g LEFT JOIN tbl_shop as i on g.shop_id = i.id where i.shop_id = {$shopId} AND i.create_time >= {$starttime} AND i.create_time <= {$endtime} AND i.sta = 2 AND i.message_status = 30 ) as t";
$total = Yii::app()->db->createCommand($countSql)->queryRow();
$rows = Yii::app()->db->createCommand($sql)->queryAll();
return [$total['count'], $rows];
}