根据常见标准从两个表中进行查询，并根据出现次数进行排序 [英] Query based on common criteria from two tables and sort based on number of occurrences
问题描述
我有以下表格需要针对每个表格运行查询.
I have the following tables that I need to run query against each.
userA
id name title
---------- ---------- --------
1 john engineer
1 John engineer
2 mike designer
3 laura manager
4 dave engineer
userB
id name title
---------- ---------- --------
1 john engineer
3 laura manager
3 laura manager
3 laura Manager
5 Peter sales
4 Dave engineer
并且我正在使用以下查询来对在两个表(相交)中找到的名称进行grep表示，并根据找到的出现次数进行排序:
and I'm using the following query to to grep the names found in both tables (intersected) and sorted based on the number of occurrences found:
select id, name, title, count(*)
from (
select id, name, title, 'A' as source from userA
union all
select id, name, title, 'B' from userB
)
group by id, name
having count(distinct source) = 2;
上面查询的输出:
id name title count(*)
---------- ---------- -------- --------
1 john engineer 3
3 laura manager 4
4 dave engineer 2
现在，我正试图弄清楚如何构造一个查询以仅显示每个标题类别的最多计数，因此在上面的示例中，工程师类别中应仅显示john，因为他拥有最多的计数类别.基本上，我想显示以下输出:
Now I'm trying to figure out how to construct a query to only show the most count for each title category, so in the above example, only john should be shown in the engineer category since he has the most count for that category. Basically, I'd like to show the following output:
id name title count(*)
---------- ---------- -------- --------
1 john engineer 3
3 laura manager 4
有人可以帮忙吗?
谢谢！
推荐答案
尝试一下:
创建一个合并两个表中的用户的 VIEW .
Create a VIEW that combines users from both tables.
CREATE VIEW userA_B as
select *, 'A' as source from userA
union all
select *, 'B' as source from userB;
此视图中的数据
select * from userA_B;
id name title source
---------- ---------- ---------- ----------
1 john engineer A
1 john engineer A
2 mike designer A
3 laura manager A
4 dave engineer A
1 john engineer B
3 laura manager B
3 laura manager B
3 laura manager B
5 peter sales B
4 dave engineer B
创建一个 VIEW ，仅向您显示同时出现在两个表中的那些用户.
Create a VIEW that shows you only those users who appear in both tables.
CREATE VIEW user_in_both_A_B as
select id, name, title, count(*) as total_appearance
from userA_B
group by id, name, title
having count(distinct source) = 2;
此视图中的数据
select * from user_in_both_A_B;
id name title total_appearance
---------- ---------- ---------- ----------------
1 john engineer 3
3 laura manager 4
4 dave engineer 2
创建一个 VIEW ，向您显示出现次数最多的标题.
Create a VIEW that shows you the title that appears the most.
CREATE VIEW title_appearing_most as
select title, max(total_appearance) as max_total_appearance
from user_in_both_A_B
group by title
此视图中的数据
select * from title_appearing_most;
title max_total_appearance
---------- --------------------
engineer 3
manager 4
现在，仅从 user_in_both_A_B 视图中获得在 title_appearing_most 中具有标题和外观编号匹配的那些记录.
Now, get only those records from user_in_both_A_B view that have title and # of appearances matching in title_appearing_most.
select ab.*
from user_in_both_A_B ab
inner join title_appearing_most m
on ab.title = m.title
and ab.total_appearance = m.max_total_appearance;
最终结果
id name title total_appearance
---------- ---------- ---------- ----------------
1 john engineer 3
3 laura manager 4
视图将帮助您存储可以按需执行且名称较短的查询.子查询中的子查询可以从视觉上避免，从而使阅读更加简单.
Views will help you store a query that can be executed on demand, and with a shorter name. Sub-queries inside sub-queries can be visually avoided, making reading simpler.
这篇关于根据常见标准从两个表中进行查询，并根据出现次数进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
