Laravel 复杂聚合查询 sql 构建,备忘小例子

本贴最后更新于 2497 天前,其中的信息可能已经东海扬尘

在实际开发过程中时不时的会遇到一些统计查询的需求,面对复杂的查询需求可以直接手写原生 sql 之后直接 DB::select,当然也可以使用 Illuminate\Database\Query\Builder 组织查询程序,为了使代码更具可读性还是推荐使用 Query\Builder 较为优雅。
下面是备忘小例子

$query = DB::table('erp_cutover_statistic0')->whereBetween('statistic_at',[$this->start_date,$this->end_date])  
    ->select('qty_hs_new_r', 'qty_hs_new_s', 'qty_cu_new_r', 'qty_cu_new_s', 'qty_hs_follow', 'qty_cu_follow','qty_sh_seen_r','qty_sh_cu_seen_r','qty_sh_seen_s','qty_sh_cu_seen_s','qty_nh_seen','emp_id');  
$queryToday = DB::table('erp_cutover_statistic0_today')->whereBetween('statistic_at',[$this->start_date,$this->end_date])  
    ->select('qty_hs_new_r', 'qty_hs_new_s', 'qty_cu_new_r', 'qty_cu_new_s', 'qty_hs_follow', 'qty_cu_follow', 'qty_sh_seen_r','qty_sh_cu_seen_r','qty_sh_seen_s','qty_sh_cu_seen_s','qty_nh_seen','emp_id');  
$query->unionAll($queryToday);  
$unionAllSql = $query->toSql();  
$start = $this->dateToKey($this->start_date);  
$end = $this->dateToKey($this->end_date);  
$data = DB::table('employee_dim')->join('department_tree',function ($join){  
    $join->on('employee_dim.dept_id','department_tree.dept_id')  
        ->where($this->dept_filed,$this->dept_id);  
})->leftJoin(DB::raw('('.$unionAllSql.') sub'),'sub.emp_id','employee_dim.emp_key')  
    ->addBinding($query->getBindings())  
    ->whereIn('position_id',$this->statPositionIds)  
    ->groupBy('employee_dim.emp_key')  
    ->orderBy('dept_name','ASC')  
    ->orderBy('emp_name','ASC')  
    ->selectRaw('emp_name,dept_name,SUM(qty_hs_new_r) qty_hs_new_r,SUM(qty_hs_new_s) qty_hs_new_s,SUM(qty_cu_new_r) qty_cu_new_r,SUM(qty_cu_new_s) qty_cu_new_s,SUM(qty_hs_follow) qty_hs_follow, SUM(qty_cu_follow) qty_cu_follow,SUM(qty_sh_seen_r) qty_sh_seen_r,SUM(qty_sh_cu_seen_r) qty_sh_cu_seen_r, SUM(qty_nh_seen) qty\_nh_seen,fn_get_stereo_visit_r(emp_key,?,?,\'e\') as stereo_visit_r,  
 fn_get_stereo_visit_s(emp_key,?,?,\'e\') as stereo_visit_s',[$start,$end,$start,$end])  
    ->skip($this->offset)->take($this->rows)->get();

实际查询时生成 sql

SELECT
    emp_name,
	dept_name,
	SUM(qty_hs_new_r) qty_hs_new_r,
	SUM(qty_hs_new_s) qty_hs_new_s,
	SUM(qty_cu_new_r) qty_cu_new_r,
	SUM(qty_cu_new_s) qty_cu_new_s,
	SUM(qty_hs_follow) qty_hs_follow,
	SUM(qty_cu_follow) qty_cu_follow,
	SUM(qty_sh_seen_r) qty_sh_seen_r,
	SUM(qty_sh_cu_seen_r) qty_sh_cu_seen_r,
	SUM(qty_nh_seen) qty_nh_seen,
	fn_get_stereo_visit_r (
		emp_key,
		20160101,
		20180909,
		'e'
	) AS stereo_visit_r,
	fn_get_stereo_visit_s (
		emp_key,
		20160101,
		20180909,
		'e'
	) AS stereo_visit_s
FROM
	`employee_dim`
INNER JOIN `department_tree` ON `employee_dim`.`dept_id` = `department_tree`.`dept_id`
AND `dept1_id` = 394
LEFT JOIN (
	(
		SELECT
			`qty_hs_new_r`,
			`qty_hs_new_s`,
			`qty_cu_new_r`,
			`qty_cu_new_s`,
			`qty_hs_follow`,
			`qty_cu_follow`,
			`qty_sh_seen_r`,
			`qty_sh_cu_seen_r`,
			`qty_sh_seen_s`,
			`qty_sh_cu_seen_s`,
			`qty_nh_seen`,
			`emp_id`
		FROM
			`erp_cutover_statistic0`
		WHERE
			`statistic_at` BETWEEN 2016 - 01 - 01
		AND 2018 - 09 - 09
	)
	UNION ALL
		(
			SELECT
				`qty_hs_new_r`,
				`qty_hs_new_s`,
				`qty_cu_new_r`,
				`qty_cu_new_s`,
				`qty_hs_follow`,
				`qty_cu_follow`,
				`qty_sh_seen_r`,
				`qty_sh_cu_seen_r`,
				`qty_sh_seen_s`,
				`qty_sh_cu_seen_s`,
				`qty_nh_seen`,
				`emp_id`
			FROM
				`erp_cutover_statistic0_today`
			WHERE
				`statistic_at` BETWEEN 2016 - 01 - 01
			AND 2018 - 09 - 09
		)
) sub ON `sub`.`emp_id` = `employee_dim`.`emp_key`
WHERE
	`position_id` IN (
		5307,
		1926,
		1678,
		5308,
		5311,
		5312,
		5313,
		5314,
		5315,
		1791,
		1726,
		1728,
		5310,
		5316,
		5317,
		5318,
		5319
	)
GROUP BY
	`employee_dim`.`emp_key`
ORDER BY
	`dept_name` ASC,
	`emp_name` ASC
LIMIT 20 OFFSET 0```
  • B3log

    B3log 是一个开源组织,名字来源于“Bulletin Board Blog”缩写,目标是将独立博客与论坛结合,形成一种新的网络社区体验,详细请看 B3log 构思。目前 B3log 已经开源了多款产品:SymSoloVditor思源笔记

    1063 引用 • 3455 回帖 • 159 关注
  • PHP

    PHP(Hypertext Preprocessor)是一种开源脚本语言。语法吸收了 C 语言、 Java 和 Perl 的特点,主要适用于 Web 开发领域,据说是世界上最好的编程语言。

    180 引用 • 408 回帖 • 487 关注
  • Laravel

    Laravel 是一套简洁、优雅的 PHP Web 开发框架。它采用 MVC 设计,是一款崇尚开发效率的全栈框架。

    20 引用 • 23 回帖 • 740 关注
  • 分享

    有什么新发现就分享给大家吧!

    248 引用 • 1794 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • 外包

    有空闲时间是接外包好呢还是学习好呢?

    26 引用 • 233 回帖 • 4 关注
  • React

    React 是 Facebook 开源的一个用于构建 UI 的 JavaScript 库。

    192 引用 • 291 回帖 • 379 关注
  • BookxNote

    BookxNote 是一款全新的电子书学习工具,助力您的学习与思考,让您的大脑更高效的记忆。

    笔记整理交给我,一心只读圣贤书。

    1 引用 • 1 回帖 • 6 关注
  • GitBook

    GitBook 使您的团队可以轻松编写和维护高质量的文档。 分享知识,提高团队的工作效率,让用户满意。

    3 引用 • 8 回帖
  • 小说

    小说是以刻画人物形象为中心,通过完整的故事情节和环境描写来反映社会生活的文学体裁。

    32 引用 • 108 回帖
  • 导航

    各种网址链接、内容导航。

    44 引用 • 177 回帖
  • webpack

    webpack 是一个用于前端开发的模块加载器和打包工具,它能把各种资源,例如 JS、CSS(less/sass)、图片等都作为模块来使用和处理。

    42 引用 • 130 回帖 • 246 关注
  • Office

    Office 现已更名为 Microsoft 365. Microsoft 365 将高级 Office 应用(如 Word、Excel 和 PowerPoint)与 1 TB 的 OneDrive 云存储空间、高级安全性等结合在一起,可帮助你在任何设备上完成操作。

    5 引用 • 34 回帖
  • BND

    BND(Baidu Netdisk Downloader)是一款图形界面的百度网盘不限速下载器,支持 Windows、Linux 和 Mac,详细介绍请看这里

    107 引用 • 1281 回帖 • 34 关注
  • LeetCode

    LeetCode(力扣)是一个全球极客挚爱的高质量技术成长平台,想要学习和提升专业能力从这里开始,充足技术干货等你来啃,轻松拿下 Dream Offer!

    209 引用 • 72 回帖 • 3 关注
  • PWL

    组织简介

    用爱发电 (Programming With Love) 是一个以开源精神为核心的民间开源爱好者技术组织,“用爱发电”象征开源与贡献精神,加入组织,代表你将遵守组织的“个人开源爱好者”的各项条款。申请加入:用爱发电组织邀请帖
    用爱发电组织官网:https://programmingwithlove.stackoverflow.wiki/

    用爱发电组织的核心驱动力:

    • 遵守开源守则,体现开源&贡献精神:以分享为目的,拒绝非法牟利。
    • 自我保护:使用适当的 License 保护自己的原创作品。
    • 尊重他人:不以各种理由、各种漏洞进行未经允许的抄袭、散播、洩露;以礼相待,尊重所有对社区做出贡献的开发者;通过他人的分享习得知识,要留下足迹,表示感谢。
    • 热爱编程、热爱学习:加入组织,热爱编程是首当其要的。我们欢迎热爱讨论、分享、提问的朋友,也同样欢迎默默成就的朋友。
    • 倾听:正确并恳切对待、处理问题与建议,及时修复开源项目的 Bug ,及时与反馈者沟通。不抬杠、不无视、不辱骂。
    • 平视:不诋毁、轻视、嘲讽其他开发者,主动提出建议、施以帮助,以和谐为本。只要他人肯努力,你也可能会被昔日小看的人所超越,所以请保持谦虚。
    • 乐观且活跃:你的努力决定了你的高度。不要放弃,多年后回头俯瞰,才会发现自己已经成就往日所仰望的水平。积极地将项目开源,帮助他人学习、改进,自己也会获得相应的提升、成就与成就感。
    1 引用 • 487 回帖 • 7 关注
  • OneDrive
    2 引用
  • 开源

    Open Source, Open Mind, Open Sight, Open Future!

    411 引用 • 3588 回帖
  • 钉钉

    钉钉,专为中国企业打造的免费沟通协同多端平台, 阿里巴巴出品。

    15 引用 • 67 回帖 • 284 关注
  • Python

    Python 是一种面向对象、直译式电脑编程语言,具有近二十年的发展历史,成熟且稳定。它包含了一组完善而且容易理解的标准库,能够轻松完成很多常见的任务。它的语法简捷和清晰,尽量使用无异义的英语单词,与其它大多数程序设计语言使用大括号不一样,它使用缩进来定义语句块。

    556 引用 • 675 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    693 引用 • 537 回帖
  • 新人

    让我们欢迎这对新人。哦,不好意思说错了,让我们欢迎这位新人!
    新手上路,请谨慎驾驶!

    52 引用 • 228 回帖
  • 生活

    生活是指人类生存过程中的各项活动的总和,范畴较广,一般指为幸福的意义而存在。生活实际上是对人生的一种诠释。生活包括人类在社会中与自己息息相关的日常活动和心理影射。

    230 引用 • 1454 回帖
  • Caddy

    Caddy 是一款默认自动启用 HTTPS 的 HTTP/2 Web 服务器。

    12 引用 • 54 回帖 • 173 关注
  • FreeMarker

    FreeMarker 是一款好用且功能强大的 Java 模版引擎。

    23 引用 • 20 回帖 • 459 关注
  • 资讯

    资讯是用户因为及时地获得它并利用它而能够在相对短的时间内给自己带来价值的信息,资讯有时效性和地域性。

    56 引用 • 85 回帖 • 2 关注
  • WebComponents

    Web Components 是 W3C 定义的标准,它给了前端开发者扩展浏览器标签的能力,可以方便地定制可复用组件,更好的进行模块化开发,解放了前端开发者的生产力。

    1 引用 • 9 关注
  • QQ

    1999 年 2 月腾讯正式推出“腾讯 QQ”,在线用户由 1999 年的 2 人(马化腾和张志东)到现在已经发展到上亿用户了,在线人数超过一亿,是目前使用最广泛的聊天软件之一。

    45 引用 • 557 回帖 • 3 关注
  • 架构

    我们平时所说的“架构”主要是指软件架构,这是有关软件整体结构与组件的抽象描述,用于指导软件系统各个方面的设计。另外还有“业务架构”、“网络架构”、“硬件架构”等细分领域。

    143 引用 • 442 回帖 • 4 关注
  • 服务器

    服务器,也称伺服器,是提供计算服务的设备。由于服务器需要响应服务请求,并进行处理,因此一般来说服务器应具备承担服务并且保障服务的能力。

    125 引用 • 585 回帖
  • NGINX

    NGINX 是一个高性能的 HTTP 和反向代理服务器,也是一个 IMAP/POP3/SMTP 代理服务器。 NGINX 是由 Igor Sysoev 为俄罗斯访问量第二的 Rambler.ru 站点开发的,第一个公开版本 0.1.0 发布于 2004 年 10 月 4 日。

    315 引用 • 547 回帖
  • Electron

    Electron 基于 Chromium 和 Node.js,让你可以使用 HTML、CSS 和 JavaScript 构建应用。它是一个由 GitHub 及众多贡献者组成的活跃社区共同维护的开源项目,兼容 Mac、Windows 和 Linux,它构建的应用可在这三个操作系统上面运行。

    15 引用 • 136 回帖 • 1 关注