mysql - 使用 Join 和 Group By 计算行数 - mysql 、 knex

Question

我有这张表:

表foo

id | bar_id | user_id
-----------------
1  |    3   |   1
2  |    5   |   1
3  |    6   |   2
4  |    5   |   1
5  |    3   |   2

表格栏

id | title | description
------------------------
3  | hey   |  desc1
5  | ola   |  desc 2
6  | vassup | desc 3

然后我有这个查询

const basequery = knex
  .select('bar.*')
  .from('bar')
  .join('foo', 'foo.bar_id', 'bar.id')
  .groupBy('foo.bar_id')
  .whereIn('foo.user_id', 1);

这将返回:

id | title | description
-----------------------
3  | hey   | desc 1
5  | ola   | desc 2

这是正确的。

基本上，我在文件上多次重复使用该基本查询。

然后我想计算由此返回的行数。

basequery.clone().countDistinct(`bar.id as total`)

我期待的是:

id | title | description | total
--------------------------------
3  | hey   | desc 1      |  2

因为 foo 表中有 2 列 id = 1 并按 bar_id 分组。我想将第一个查询返回的列数计算为“总计”。

但是，它返回:

id | title | description | total
--------------------------------
3  | hey   | desc 1      |  1
5  | ola   | desc 2      |  2

任何帮助将不胜感激

Answer 1

我不了解 Knex，但如果 Knex 与 Laravel 类似，那么您几乎肯定需要使用一些原始查询功能来获取您想要的输出。您想要在输出中显示的 total 只是查询本身的记录总数。不幸的是，我们无法在 MySQL 中使用任何技巧来在不运行实际查询的情况下获取该计数。在下面的原始查询中，我使用非相关子查询来引入总计。

SELECT
    b.*,
    (SELECT COUNT(*) FROM (SELECT b.id FROM bar b INNER JOIN foo f ON f.bar_id = b.id
     WHERE f.user_id IN (1) GROUP BY f.bar_id) t) total
FROM bar b
INNER JOIN foo f
    ON f.bar_id = b.id
WHERE f.user_id IN (1)
GROUP BY f.bar_id

需要明确的是，上面 SELECT 列表中的子查询是这样的:

SELECT COUNT(*)
FROM
(
    SELECT b.id
    FROM bar b
    INNER JOIN foo f
        ON f.bar_id = b.id
    WHERE f.user_id IN (1)
    GROUP BY f.bar_id
) t