mysql 计数总计来自具有逗号分隔值的字段 [英] mysql count total from a field with comma separated values

问题描述

在我的 mysql 数据库中，我有一个包含此列的表.这一列的值是这样的

In my mysql database I have a table with this column. The value in this column is like this

年份:1974,2000,1976,1977

Year: 1974,2000,1976,1977

如何写一个mysql语句来选择并统计上面的值比1997有多少?

How do I write a mysql statement to select and count total how many of the value above is more than 1997?

我的输出如下:2

Select year FROM my_table

这是我目前的声明:

SELECT  year 
FROM reg2015 
WHERE cancel is NULL AND mealroom = 'Y' ORDER BY regid ASC

我的输出是这样的

year
-------------------------
1974,1976,2005,2008
1954,1958
1987
1946
1956,1959
1944,1946
1974,1975,2005,2007,2001,2002
1971
1978
1955,1969,2000,2003

所以我想知道每行有多少比 1997 大.

So I like to know how many is greater than 1997 for each row.

推荐答案

检查这个我用过 Postgresql，有id"、comma_sep_string"列和表名string_comma"，当有计数时它会显示该记录，如果计数为零，则不会显示该特定记录并查看给定查询输出的屏幕截图

Check this i've used Postgresql, having "id", "comma_sep_string" columns with table name "string_comma", when there is a count it'll show that record and if count is zero it'll not show that particular record and see the screenshot for the output of the given query

select temp1.id, temp1.comma_sep_string, sum(count) from
(select temp.id, temp.comma_sep_string, temp.years, count(*) as count
from
(SELECT
ID, comma_sep_string,
regexp_split_to_table(comma_sep_string, E',') AS years
FROM string_comma) as  temp
where temp.years::int > 1980
group by temp.id, temp.years, temp.comma_sep_string) as temp1
group by temp1.id, temp1.comma_sep_string

这篇关于mysql 计数总计来自具有逗号分隔值的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！