SQL 选择不同的子字符串 where like mudleup howto
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6302027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select distinct substring where like muddleup howto
提问by Ben
I've got a table with a field that is similar to this:
我有一个与此类似的字段的表：
ANIMAL
========
FISH 54
FISH 30
DOG 12
CAT 65
CAT 09
BIRD 10
FISH 31
DOG 10
The field may later have new animals added, such as
该领域以后可能会添加新的动物，例如
GOAT 72
DOG 20
What I'd like to do is make a SELECTquery that returns one unique row per animal name, returning a data set with one row per type of animal, which I can later parse to make a list of animals present in my table.
我想做的是进行一个SELECT查询，为每个动物名称返回一个唯一的行，返回一个数据集，每个类型的动物有一行，稍后我可以对其进行解析以制作表中存在的动物列表。
So, after the magic, I'd have
所以，在魔法之后，我会
FISH 54
DOG 12
CAT 65
BIRD 10
GOAT 72
...from which I would make my list.
...我会从中列出我的清单。
So far, I've been muddling around with subqueries, SELECT DISTINCT, and SUBSTRING(), but I feel that any result I came up with would probably pale in comparison to the might of the SO hive mind. Can someone help?
到目前为止，我一直在处理子查询、SELECT DISTINCT、 和SUBSTRING()，但我觉得我想出的任何结果与 SO 蜂巢思维的力量相比可能会显得苍白无力。有人可以帮忙吗？
UPDATE
更新
If it helps, my failed attempt sort of illustrates what I want to do:
如果有帮助，我失败的尝试说明了我想做的事情：
SELECT DISTINCT substring(animal,1,4) FROM table;
only now I don't have the whole name, just a substring. :(
只是现在我没有全名，只有一个子字符串。:(
回答by Mikael Eriksson
This will work for SQL Server. If you use something else you need to figure out the corresponding functions to leftand charindex. Left could of course be replaced with a substring.
这将适用于 SQL Server。如果您使用其他东西，则需要找出left和charindex的相应函数。Left 当然可以用子字符串替换。
select distinct left(T.Animal, charindex(' ', T.Animal, 1)-1)
from YourTable as T
Result:
结果：
-------------------------
BIRD
CAT
DOG
FISH
In MySQL you would use leftand locate. (Code not tested)
在 MySQL 中，您将使用left和locate。（代码未测试）
select distinct left(T.Animal, locate(' ', T.Animal, 1)-1)
from YourTable as T
回答by Kosta Kontos
You can also use the following to get your list of animal names:
您还可以使用以下内容来获取动物名称列表：
SELECT DISTINCT SUBSTRING_INDEX(animal, ' ', 1) FROM table_name;
回答by Nik
Why not just
为什么不只是
SELECT id, animal, value FROM table GROUP BY animal, id HAVING id = MIN(id)
That should get you a list of the animals in the table, each animal with the first entered value.
这应该会为您提供表中动物的列表，每个动物都具有第一个输入的值。
If you don't need so select the value, then just do:
如果您不需要，请选择该值，然后执行以下操作：
SELECT animal FROM table GROUP BY animal
从表中选择动物 GROUP BY 动物
回答by CristiC
SELECT Name, Max(No)
FROM Table
Group By Name
