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

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

在实际开发过程中时不时的会遇到一些统计查询的需求,面对复杂的查询需求可以直接手写原生 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思源笔记

    1062 引用 • 3455 回帖 • 149 关注
  • PHP

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

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

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

    19 引用 • 23 回帖 • 739 关注
  • 分享

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

    248 引用 • 1794 回帖

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • RYMCU

    RYMCU 致力于打造一个即严谨又活泼、专业又不失有趣,为数百万人服务的开源嵌入式知识学习交流平台。

    4 引用 • 6 回帖 • 62 关注
  • etcd

    etcd 是一个分布式、高可用的 key-value 数据存储,专门用于在分布式系统中保存关键数据。

    6 引用 • 26 回帖 • 546 关注
  • RESTful

    一种软件架构设计风格而不是标准,提供了一组设计原则和约束条件,主要用于客户端和服务器交互类的软件。基于这个风格设计的软件可以更简洁,更有层次,更易于实现缓存等机制。

    30 引用 • 114 回帖 • 6 关注
  • JVM

    JVM(Java Virtual Machine)Java 虚拟机是一个微型操作系统,有自己的硬件构架体系,还有相应的指令系统。能够识别 Java 独特的 .class 文件(字节码),能够将这些文件中的信息读取出来,使得 Java 程序只需要生成 Java 虚拟机上的字节码后就能在不同操作系统平台上进行运行。

    180 引用 • 120 回帖 • 3 关注
  • 又拍云

    又拍云是国内领先的 CDN 服务提供商,国家工信部认证通过的“可信云”,乌云众测平台认证的“安全云”,为移动时代的创业者提供新一代的 CDN 加速服务。

    20 引用 • 37 回帖 • 572 关注
  • HTML

    HTML5 是 HTML 下一个的主要修订版本,现在仍处于发展阶段。广义论及 HTML5 时,实际指的是包括 HTML、CSS 和 JavaScript 在内的一套技术组合。

    108 引用 • 295 回帖
  • JWT

    JWT(JSON Web Token)是一种用于双方之间传递信息的简洁的、安全的表述性声明规范。JWT 作为一个开放的标准(RFC 7519),定义了一种简洁的,自包含的方法用于通信双方之间以 JSON 的形式安全的传递信息。

    20 引用 • 15 回帖 • 28 关注
  • Node.js

    Node.js 是一个基于 Chrome JavaScript 运行时建立的平台, 用于方便地搭建响应速度快、易于扩展的网络应用。Node.js 使用事件驱动, 非阻塞 I/O 模型而得以轻量和高效。

    139 引用 • 269 回帖 • 1 关注
  • 反馈

    Communication channel for makers and users.

    120 引用 • 906 回帖 • 280 关注
  • 叶归
    12 引用 • 56 回帖 • 23 关注
  • SQLite

    SQLite 是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是全世界使用最为广泛的数据库引擎。

    4 引用 • 7 回帖
  • Lute

    Lute 是一款结构化的 Markdown 引擎,支持 Go 和 JavaScript。

    29 引用 • 202 回帖 • 29 关注
  • Wide

    Wide 是一款基于 Web 的 Go 语言 IDE。通过浏览器就可以进行 Go 开发,并有代码自动完成、查看表达式、编译反馈、Lint、实时结果输出等功能。

    欢迎访问我们运维的实例: https://wide.b3log.org

    30 引用 • 218 回帖 • 643 关注
  • Ant-Design

    Ant Design 是服务于企业级产品的设计体系,基于确定和自然的设计价值观上的模块化解决方案,让设计者和开发者专注于更好的用户体验。

    17 引用 • 23 回帖 • 2 关注
  • 友情链接

    确认过眼神后的灵魂连接,站在链在!

    24 引用 • 373 回帖 • 2 关注
  • ZooKeeper

    ZooKeeper 是一个分布式的,开放源码的分布式应用程序协调服务,是 Google 的 Chubby 一个开源的实现,是 Hadoop 和 HBase 的重要组件。它是一个为分布式应用提供一致性服务的软件,提供的功能包括:配置维护、域名服务、分布式同步、组服务等。

    61 引用 • 29 回帖 • 12 关注
  • 书籍

    宋真宗赵恒曾经说过:“书中自有黄金屋,书中自有颜如玉。”

    83 引用 • 412 回帖
  • Love2D

    Love2D 是一个开源的, 跨平台的 2D 游戏引擎。使用纯 Lua 脚本来进行游戏开发。目前支持的平台有 Windows, Mac OS X, Linux, Android 和 iOS。

    14 引用 • 53 回帖 • 564 关注
  • Facebook

    Facebook 是一个联系朋友的社交工具。大家可以通过它和朋友、同事、同学以及周围的人保持互动交流,分享无限上传的图片,发布链接和视频,更可以增进对朋友的了解。

    4 引用 • 15 回帖 • 442 关注
  • 京东

    京东是中国最大的自营式电商企业,2015 年第一季度在中国自营式 B2C 电商市场的占有率为 56.3%。2014 年 5 月,京东在美国纳斯达克证券交易所正式挂牌上市(股票代码:JD),是中国第一个成功赴美上市的大型综合型电商平台,与腾讯、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。

    14 引用 • 102 回帖 • 313 关注
  • FFmpeg

    FFmpeg 是一套可以用来记录、转换数字音频、视频,并能将其转化为流的开源计算机程序。

    23 引用 • 32 回帖 • 7 关注
  • CongSec

    本标签主要用于分享网络空间安全专业的学习笔记

    1 引用 • 1 回帖 • 37 关注
  • 学习

    “梦想从学习开始,事业从实践起步” —— 习近平

    172 引用 • 540 回帖
  • 服务器

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

    125 引用 • 585 回帖 • 1 关注
  • C++

    C++ 是在 C 语言的基础上开发的一种通用编程语言,应用广泛。C++ 支持多种编程范式,面向对象编程、泛型编程和过程化编程。

    108 引用 • 153 回帖 • 1 关注
  • JetBrains

    JetBrains 是一家捷克的软件开发公司,该公司位于捷克的布拉格,并在俄国的圣彼得堡及美国麻州波士顿都设有办公室,该公司最为人所熟知的产品是 Java 编程语言开发撰写时所用的集成开发环境:IntelliJ IDEA

    18 引用 • 54 回帖
  • SVN

    SVN 是 Subversion 的简称,是一个开放源代码的版本控制系统,相较于 RCS、CVS,它采用了分支管理系统,它的设计目标就是取代 CVS。

    29 引用 • 98 回帖 • 699 关注