SQL如何获取分组后每行的对应计数及Laravel查询构建器实现

Got it, let's break this down. You want every row from your tbl_hash table to show up in the results, along with how many times its specific hash1/hash2/hash3 combination appears in the table. Plus, you need to translate this into Laravel's query builder syntax. Let's cover both the raw SQL solution and the Laravel equivalent.

Option 1: Window Functions (Cleanest, MySQL 8.0+) #

If you're using MySQL 8.0 or newer, window functions are the most straightforward way to get this done. They let you calculate the group count directly in the main query without needing a separate join:

SELECT 
    id, hash1, hash2, hash3,
    COUNT(*) OVER (PARTITION BY hash1, hash2, hash3) AS count
FROM tbl_hash;

If you still want to filter results to only include rows where the group count is greater than 2 (matching your original query's HAVING clause), wrap the window function in a subquery:

SELECT *
FROM (
    SELECT 
        id, hash1, hash2, hash3,
        COUNT(*) OVER (PARTITION BY hash1, hash2, hash3) AS count
    FROM tbl_hash
) AS subquery
WHERE subquery.count > 2;

Option 2: Subquery + Join (Works with Older MySQL Versions) #

For older MySQL versions that don't support window functions, you can pre-calculate the group counts in a subquery and join it back to the original table:

SELECT 
    t.id, t.hash1, t.hash2, t.hash3, g.count
FROM tbl_hash t
INNER JOIN (
    SELECT hash1, hash2, hash3, COUNT(*) AS count
    FROM tbl_hash
    GROUP BY hash1, hash2, hash3
    -- Uncomment this line if you want to filter groups with count > 2
    -- HAVING COUNT(*) > 2
) g 
    ON t.hash1 = g.hash1 
    AND t.hash2 = g.hash2 
    AND t.hash3 = g.hash3;

Window Function Approach #

This mirrors the first SQL option. Most modern Laravel versions support raw expressions with window functions, so this should work smoothly:

// Get all rows with their group count
$results = DB::table('tbl_hash')
    ->select(
        'id',
        'hash1',
        'hash2',
        'hash3',
        DB::raw('COUNT(*) OVER (PARTITION BY hash1, hash2, hash3) AS count')
    )
    ->get();

// If you need to filter for groups with count > 2:
$results = DB::table(function ($query) {
    $query->from('tbl_hash')
          ->selectRaw('id, hash1, hash2, hash3, COUNT(*) OVER (PARTITION BY hash1, hash2, hash3) AS count');
}, 'sub')
    ->where('sub.count', '>', 2)
    ->get();

Subquery + Join Approach #

This is compatible with all Laravel versions and older MySQL setups:

// Basic version: all rows with their group count
$results = DB::table('tbl_hash as t')
    ->join(
        DB::raw('(SELECT hash1, hash2, hash3, COUNT(*) AS count FROM tbl_hash GROUP BY hash1, hash2, hash3) as g'),
        function ($join) {
            $join->on('t.hash1', '=', 'g.hash1')
                 ->on('t.hash2', '=', 'g.hash2')
                 ->on('t.hash3', '=', 'g.hash3');
        }
    )
    ->select('t.id', 't.hash1', 't.hash2', 't.hash3', 'g.count')
    ->get();

// With filter for groups where count > 2:
$results = DB::table('tbl_hash as t')
    ->join(
        DB::raw('(SELECT hash1, hash2, hash3, COUNT(*) AS count FROM tbl_hash GROUP BY hash1, hash2, hash3 HAVING count > 2) as g'),
        function ($join) {
            $join->on('t.hash1', '=', 'g.hash1')
                 ->on('t.hash2', '=', 'g.hash2')
                 ->on('t.hash3', '=', 'g.hash3');
        }
    )
    ->select('t.id', 't.hash1', 't.hash2', 't.hash3', 'g.count')
    ->get();

内容的提问来源于stack exchange，提问作者I am L