thinkphp3中SQL语句的转换和count与group的冲突

最新推荐文章于 2023-01-11 09:55:22 发布

梦醒已隔万重秋

最新推荐文章于 2023-01-11 09:55:22 发布

阅读量1.2k

点赞数

分类专栏：数据库 PHP

本文链接：https://blog.csdn.net/qq_36020968/article/details/85092492

版权

PHP 同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

数据库

6 篇文章 1 订阅

订阅专栏

原sql语句，

$sql="SELECT
            	count(*),
            	brand.`name` AS '品牌名称',
            	kb_package_goods.`name` '商品名称',
            	kb_package.price,
            	package_goods_id,
            	date_format(
            		from_unixtime(end_time),
                '%Y-%m-%d'
            
            	) AS days
            FROM
            	kb_package
            LEFT JOIN brand ON brand.id = kb_package.brand_id
            LEFT JOIN kb_package_goods ON package_goods_id = kb_package_goods.id
            WHERE
            	kb_package.brand_id IN (
            		SELECT DISTINCT
            			(brand_id)
            		FROM
            			kb_package
            	)
            AND kb_package.state = 0
            GROUP BY
            	kb_package.brand_id,
            	kb_package.package_goods_id";

转换后的形式，

$data = $m->limit($Page->firstRow.','.$Page->listRows)->join('LEFT JOIN brand ON brand.id = kb_package.brand_id')
        ->join('LEFT JOIN kb_package_goods ON package_goods_id = kb_package_goods.id')
        ->field('count(1) as mun,brand.name as ppname,kb_package_goods.name as goodname,kb_package.price as jiage, package_goods_id,date_format( from_unixtime(end_time), "%Y-%m-%d" ) as time')
        ->group('kb_package.brand_id, kb_package.package_goods_id')
        ->where($map)->select();

count时的子查询避免与group的冲突

$subQuery = $m->join('LEFT JOIN brand ON brand.id = kb_package.brand_id')
        ->join('LEFT JOIN kb_package_goods ON package_goods_id = kb_package_goods.id')
        ->field('count(1) as mun,brand.name as ppname,kb_package_goods.name as goodname,kb_package.price as jiage, package_goods_id,date_format( from_unixtime(end_time), "%Y-%m-%d" ) as time')
        ->group('kb_package.brand_id, kb_package.package_goods_id')->where($map)->select(false);
        $count = $m->table($subQuery . ' a')->count();