groupByTopN

groupByTopN.md

<?php
// 子查询
ServerNotice::query()
  ->select('*')
  ->whereRaw('(SELECT COUNT(*) FROM xb_server_notice AS sn WHERE xb_server_notice.zone_id = sn.zone_id AND xb_server_notice.updated_at < sn.updated_at) < 2')
  ->orderBy('zone_id')
  ->orderByDesc('updated_at')
  ->get()
  ->dd();

// 左链接
ServerNotice::query()
  ->select('server_notice.*')
  ->leftJoin('server_notice as b', function ($join){
    $join->on('server_notice.zone_id', '=', 'b.zone_id')
      ->on('server_notice.updated_at', '<', 'b.updated_at');
  })
  ->groupBy('server_notice.id')
  ->havingRaw('COUNT(*) < 2')
  ->orderBy('server_notice.zone_id')
  ->orderByDesc('server_notice.updated_at')
  ->get()
  ->dd();