计数具有相同值的字段 [英] Counting Fields with Same Value

问题描述

示例:

我有一个带有union的HUGE查询，它带来了很多数据，在这些数据中，我想知道，同一个人的记录只存在多少次字段 name, Like:

I have a HUGE QUery with union that brings a lot of data, among this data, I want to know, how many times records of the same person exists only the field name, Like:

加布里埃尔
- 记录 1
- 记录2
- 记录 3

Gabriel
- Record 1
- Record 2
- Record 3

若昂
- 记录 1
- 记录2
- 记录 3

João
- Record 1
- Record 2
- Record 3

所以 Gabriel 和 João 每个人在我的 RecordSet 中都有 3 个数据行，我需要有另一个字段来计算它重复的次数，但我同时需要所有行，这就是为什么我不能使用groupby.

So Gabriel and João each one has 3 data rows in my RecordSet, I need to have another field that counts that many of times it repeats, but also I need all the rows at same time, that's why I Can NOT use groupby.

更新 MySql 视图

推荐答案

既然你有一个包含复杂逻辑的视图，你可以构造一个这样的查询来获得你想要的内容:

Now that you have a view wrapping your complicated logic, you could structure a query like this to get what you are looking for:

SELECT vt.*, vtij.nome_count
FROM view_teste vt
    INNER JOIN (SELECT Nome, count(1) as nome_count FROM view_teste
        WHERE [additional_filter_conditions]
            GROUP BY Nome) vtij on vtij.Nome = vt.Nome
WHERE [additional_filter_conditions]

附加说明:(1) 这可能不是高效的，但值得一试，因为您不是发起此查询！优化此查询将是一头野兽.(2) 您认为有一个 ORDER BY 子句，这是不必要的性能损失.您应该删除它并在您的查询中执行所需的 ORDER BY，而不是在您的视图中.

Additional notes: (1) This may not be performant, but worth trying since you did not originate this query! Optimizing this query would be a beast. (2) You have an ORDER BY clause in your view which is a performance hit that is unnecessary. You should remove that and do the desired ORDER BY in your query, not inside your view.

这篇关于计数具有相同值的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！