如何在SQL中计算不同的列而忽略列顺序

2024-05-05 • 问答

我有一张这样的桌子：


home_city     away_city
-----------------------

Valencia      Madrid        
Barcelona     Madrid
Madrid        Valencia
Alicante      Castellon
Madrid        Valencia
Castellon     Alicante
Valencia      Madrid

我想对每种不同组合的行进行计数，但忽略它是 home_city 还是 away_city 。 我的意思是，通常我会这样查询

SELECT home_city,away_city,COUNT(*) as count_cities
FROM my_table
GROUP BY home_city,away_city

，它将返回以下结果：

home_city     away_city     count_cities
----------------------------------------

Valencia      Madrid        2
Barcelona     Madrid        1        
Madrid        Valencia      2
Alicante      Castellon     1
Castellon     Alicante      1

但是我真正想要的是查询忽略该列，并向我返回类似的内容：

home_city     away_city     count_cities
----------------------------------------

Valencia      Madrid        4
Barcelona     Madrid        1        
Alicante      Castellon     2

返回一列还是两列都没有关系。

预先感谢

使用least()和greatest()：

SELECT LEAST(home_city,away_city) as city1,GREATEST(home_city,away_city) as city2,COUNT(*) as count_cities
FROM my_table
GROUP BY LEAST(home_city,away_city),away_city);

MySQL允许GROUP BY中使用别名，因此您可以将其简化为：

SELECT LEAST(home_city,COUNT(*) as count_cities
FROM my_table
GROUP BY city1,city2;

这是我的la脚版本，但不需要高级SQL知识;）

SELECT CASE WHEN home_city < away_city THEN CONCAT(home_city,',away_city) ELSE CONCAT(away_city,home_city) END as cities,COUNT(*) as count
FROM X2
GROUP BY CASE WHEN home_city < away_city THEN CONCAT(home_city,home_city) END;

--drop table X2;
create table X2 
(home_city varchar(255),away_city varchar(255));


insert into X2 values ('Valencia','Madrid');        
insert into X2 values ('Barcelona','Madrid');
insert into X2 values ('Madrid','Valencia');
insert into X2 values ('Alicante','Castellon');
insert into X2 values ('Madrid','Valencia');
insert into X2 values ('Castellon','Alicante');
insert into X2 values ('Valencia','Madrid');

SELECT CASE WHEN home_city < away_city THEN CONCAT(home_city,home_city) END;

cities  count
Alicante,Castellon  2
Barcelona,Madrid    1
Madrid,Valencia 4

如何在SQL中计算不同的列而忽略列顺序

yxj366 回答：如何在SQL中计算不同的列而忽略列顺序

大家都在问