使用SQL查询统计同名的两个不同列中的重复项
使用SQL查询统计同名的两个不同列中的重复项
提问于 2018-12-02 22:21:28
我有下表：
Team1 Team2 result
---------------------
a b a
b c b
c a a
b a b
b c -
a b a
b c b 我想要计算Team1和Team2列中出现的重复的团队组合，如下所示：
Team1 Team2 count
--------------------
a b 3
b c 3
c a 1我尝试了以下查询：
SELECT TEAM1,TEAM2, COUNT(*) AS COUNT
FROM TABLE_NAME
GROUP BY TEAM1, TEAM2我没有得到想要的输出。相反，我得到了以下内容：
Team1 Team2 Count
---------------------
c a 1
b a 1
a b 2
b c 3有人能帮帮忙吗？
回答 2
Stack Overflow用户
发布于 2018-12-02 22:25:30
您可以使用least()和greatest()
SELECT LEAST(TEAM1, TEAM2) as TEAM1, GREATEST(TEAM1, TEAM2) as TEAM2, COUNT(*) AS CNT
FROM TABLE_NAME
GROUP BY LEAST(TEAM1, TEAM2), GREATEST(TEAM1, TEAM2);Stack Overflow用户
发布于 2018-12-02 23:17:33
您可以使用应用于这些列的ascii函数的按乘法分组(这是一种交换代数操作)，如下所示
with table_name(Team1,Team2,result) as
(
select 'a','b','a' from dual union all
select 'b','c','b' from dual union all
select 'c','a','a' from dual union all
select 'b','a','b' from dual union all
select 'b','c',null from dual union all
select 'a','b','a' from dual union all
select 'b','c','b' from dual
)
select min(team1) as team1,max(team2) as team2,
count(*) as count
from table_name
group by ascii(team1)*ascii(team2)
order by team1;
TEAM1 TEAM2 COUNT
a b 3
b c 3
c a 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53581136
复制相关文章
相似问题
腾讯云开发者
