在mysql中计数和不同(表中逗号分隔的数据)

I am trying to get the details from mysql database.

我正在尝试从mysql数据库中获取详细信息。

My table have location field. Data is like below

我的表有位置字段。下面的数据是

Location
India
UK,Finland, India
USA, NZ, AUS, Spain

I am trying to get the count for each location.

我想要得到每个位置的计数。

select count(distinct location) from posting

This is the query i have written at the moment for testing purpose. It return me count.

这是我为测试目的而编写的查询。我返回计数。

I want to get the details like below.

我想获得如下细节。

India -2
UK - 1 
Finland -1
USA - 1

Something like that.

就像这样。

Note Please ignore dash as this is just to show you what i want.

注意，请忽略破折号，因为这只是为了告诉你我想要什么。

Please advise me as i am not good in database.

我的数据库不太好，请通知我。

2 个解决方案

#1

SELECT COUNT(location),location FROM posting;

Try this one out. It will give you a similar output.

试试这个。它会给你一个类似的输出。

#2

You should not be storing multiple values in a single columns. Delimited strings are not the right way to store values in SQL. The proper way is a junction table.

您不应该在单个列中存储多个值。分隔字符串不是在SQL中存储值的正确方法。正确的方法是使用连接表。

If I assume that you have a list of locations, then you can do:

如果我假设您有一个位置列表，那么您可以:

select l.location, count(*)
from location l join
     posting p
     on find_in_set(l.location, p.location) > 0;

If you don't have such a table, I would recommend that you focus on fixing your data structure first, rather than trying to get such a query to work.

如果您没有这样的表，我建议您首先关注于修复数据结构，而不是试图让这样的查询工作。

#1

SELECT COUNT(location),location FROM posting;