如何在MySQL中以逗号分隔的字段中获取最大值? [英] How do I get the maximum value in a comma separated field in MySQL?

问题描述

我有一个MySQL表，其中有一个用逗号分隔的值的字段.如何获得最大值?

I have a MySQL table that has a field of comma separated values. How do I get the maximum value?

示例:

CREATE TABLE `test`.`scale` (`scale` VARCHAR(500));

INSERT INTO `test`.scale 
VALUES 
('1,5,0.3,0.8,1'), 
('0.5,0.7,0.1,0.3'), 
('7,0.5,0.7,4');

规模:

1,5,0.3,0.8,1
0.5,0.7,0.1,0.3
7,0.5,0.7,4

answer:

5
0.7
7

如何选择此字符串中的最大值?

How do I select the max value in this string?

不幸的是，我无法更改前一个人实施逗号分隔解决方案的方式.我希望有一个MySQL函数可以帮助我，否则我将不得不做一些凌乱的PHP事情.

Unfortunately I can't change the way the previous person implemented the comma separated solution. I hope there is a MySQL function that can help me out, otherwise I’ll have to do some messy PHP stuff.

推荐答案

我不认为SQL具有拆分字符串的功能. 这里有一些声称可以做到的代码，但我还没有t测试了它.

I don't believe SQL has a function for splitting strings. Here's some code that claims to do it, but I haven't tested it.

更深层的原因是SQL迫切希望您将数据存储在规范化形式中.在这种情况下，这意味着每个字段都应代表有关该行主题的单个数据.

The deeper reason for this is that SQL desperately wants you to store your data in normalized form. In this particular case, this means that every field should represent a single piece of data about the subject of the row.

基本上，您要将scale和answer存储在两个单独的表中，可以使用每个人所属的问题的ID相互匹配.比例尺每个比例尺只有一行，而不是每个比例尺一行:

Essentially you'd store scale and answer in two separate tables, which you could match with one another using an ID for the question each one belongs to. Scale would have one row per scale option, not one row per scale:

OPTIONS                     ANSWERS
question | scaleoption      question | answer
---------|------------      ---------|-------
       1 | 1.5                    1  | 5
       1 | .3                     2  | .7
       1 | .8               ... 
       1 | 1
       2 | .5
       2 | .7
...

然后，您可以使用查询select max(scaleoption) from options where question = 2来获取问题2的最大选项.这种格式还可以让您执行以下操作，例如查找其中一个选项为1的所有问题，或者计算特定选项的数量.问题了.它看起来更复杂，但是它是一种灵活，可扩展，可移植的处理方式.

You could then use the query select max(scaleoption) from options where question = 2 to get the maximum option for question 2. This format also lets you do things like find all questions where one of the options is 1, or get a count of how many options a particular question has. It looks more complicated but it's a flexible, extendible, portable way to do things.

如果要使用当前格式，则必须返回整个字符串并以其他某种编程语言将其分解.

If you want to use your current format, you'll have to return the entire string and break it up in some other programming language.

这篇关于如何在MySQL中以逗号分隔的字段中获取最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！