假设我有一张桌子：
ID,City1,City2,City3,Country,….(不重要)
该应用程序询问人们他们希望住在哪里,让我们说法国.
因此,必须添加至少一个城市,但您可以添加3个最大城市.
例如,我们在表数据中有：
ID City1 City2 City3 Country UserID -------------------------------------------------- 1 Paris / / France 1 2 Paris Nice / France 2 3 Paris Nice / France 3 4 Nice Paris Lyon France 4 5 Lyon Paris Nice France 5 6 Cannes Nice Paris France 6 7 Paris Cannes Lyon France 7 --------------------------------------------------
现在,当有人点击法国时,我会在页面上显示所有用户.
然后以上用户我想显示所有城市的数字,如巴黎(n)为
例.
所以,如果我写：
select City1 as city,count(1) as num
from table_c
where Country = "France" group by City1;
你可以尝试这个sql,让我知道这是否有效
select city,count(1) as num from ( select City1 as city from table_c where Country = "France" and city1 is not null UNION ALL select City2 as city from table_c where Country = "France" and city2 is not null UNION ALL select City3 as city from table_c where Country = "France" and city3 is not null ) tbl group by city
