sql 聚合使用方法-草稿

最新推荐文章于 2024-10-31 07:30:00 发布

Do_Only

最新推荐文章于 2024-10-31 07:30:00 发布

阅读量509

点赞数

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/Do_Only/article/details/106220869

版权

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文探讨了在SQL中使用聚合函数时可能遇到的问题及其解决方案，重点讲解如何进行二次排序以确保数据的准确性和一致性。通过学习，你将掌握更高级的SQL聚合操作技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<?php

if (!defined('IN_IA')) {
	exit('Access Denied');
}

class Recommend_EweiShopV2Page extends WebPage
{
	public function main()
	{
		global $_W;
		global $_GPC;
		$page = $_GPC['page']?$_GPC['page']:1;
		$pindex = max(1, intval($page));
		$psize = 20;
		$condition  = [];

		$start = $_GPC['time']['start']?$_GPC['time']['start'].':00':'';
		$end = $_GPC['time']['end']?$_GPC['time']['end'].':59':'';

		$startTime = $start ? strtotime($start) : 0;
		$endTime =  $end ? strtotime($end) : time();

		$startPrice = $_GPC['startPrice'] ? (float)$_GPC['startPrice'] : 0.01;
		$endPrice = $_GPC['endPrice'] ? (float)$_GPC['endPrice'] : 9999999999;

		var_dump($startTime);
		var_dump($endTime);
		var_dump($startPrice);
		var_dump($endPrice);
		exit();

		$sql1 = 'SELECT m.id,m.realname,m.mobile,m.nickname,t.cc FROM ims_ewei_shop_member m
				LEFT JOIN ( SELECT o.agentid, COUNT(agentid) cc
				FROM ims_ewei_shop_order o
				WHERE o.createtime >= :startTime AND o.createtime <= :endTime AND o.price >= :startPrice  AND o.price <= :endPrice
				GROUP BY o.agentid ) t
				ON t.agentid = m.id order by t.cc desc';

		$sql2 = 'SELECT m.id,m.realname,m.mobile,m.nickname,t.cc FROM ims_ewei_shop_member m
					LEFT JOIN 
					( 
						SELECT o.agentid, COUNT(agentid) cc
						FROM ims_ewei_shop_order o
						WHERE o.createtime >= :startTime AND o.createtime <= :endTime AND o.price >= :startPrice  AND o.price <= :endPrice
						GROUP BY o.agentid 
					) t
					ON t.agentid = m.id order by t.cc desc';


		$sql = 'SELECT list.*,@rank :=@rank + 1 AS current_rank FROM 
				( 
					( 
					SELECT m.id,m.realname,m.mobile,m.nickname,t.cc FROM ims_ewei_shop_member m LEFT JOIN 
						( 
						SELECT o.agentid, COUNT(agentid) cc
						FROM ims_ewei_shop_order o
						WHERE o.createtime >= :startTime AND o.createtime <= :endTime AND o.price >= :startPrice  AND o.price <= :endPrice
						GROUP BY o.agentid 
						) t ON t.agentid = m.id order by t.cc desc 
					) list,
					(SELECT @rank := 0) b
				)';

		if(empty($_GPC["export"]) )
		{
			$sql .= " LIMIT " . ($pindex - 1) * $psize . "," . $psize;
			$sql2 .= " LIMIT " . ($pindex - 1) * $psize . "," . $psize;
		}

		$list = pdo_fetchall($sql2,['startTime'=>$startTime , 'endTime'=>$endTime, 'startPrice'=>$startPrice ,'endPrice'=>$endPrice]);
//
//		dump($list);
//		exit();

		$total = $total = pdo_fetchcolumn('select count(id) from' . tablename('ewei_shop_member'));
		$pager = pagination2($total, $pindex, $psize);
		include $this->template();
	}

}

?>

但是这样子会出现问题，所以需要二次排序。

$sql = 'SELECT list.*,@rank :=@rank + 1 AS current_rank FROM 
				( 
					( 
					SELECT m.id,m.realname,m.mobile,m.nickname,t.cc FROM ims_ewei_shop_member m LEFT JOIN 
						( 
						SELECT o.agentid, COUNT(agentid) cc
						FROM ims_ewei_shop_order o
						WHERE o.status >0 AND refundstate <2 AND o.createtime >= :startTime AND o.createtime <= :endTime AND o.price >= :startPrice  AND o.price <= :endPrice
						GROUP BY o.agentid 
						) t ON t.agentid = m.id order by t.cc desc 
					) list,
					(SELECT @rank := 0) b
				)';

恭喜我获得了新的技能