SQL选择不同的子串，其中像muddleup howto

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 SELECT query 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 hive心灵的威力相比显得苍白无力。有人可以帮忙吗？

UPDATE

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. :(

只是现在我没有全名，只是一个子串。 :(

4 个解决方案

#1

This will work for SQL Server. If you use something else you need to figure out the corresponding functions to left and charindex. Left could of course be replaced with a substring.

这适用于SQL Server。如果你使用别的东西，你需要找出左边的相应函数和charindex。左边当然可以用子串替换。

select distinct left(T.Animal, charindex(' ', T.Animal, 1)-1)
from YourTable as T

Result:

结果：

-------------------------
BIRD
CAT
DOG
FISH

In MySQL you would use left and locate. (Code not tested)

在MySQL中，您将使用left和locate。（代码未经测试）

select distinct left(T.Animal, locate(' ', T.Animal, 1)-1)
from YourTable as T

4 个解决方案

#1

更多相关文章

随机推荐