SQL选择不同的子字符串，如mundleup howto [英] SQL select distinct substring where like 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

我想做的是执行SELECT查询，该查询返回每种动物名唯一的一行，返回一种数据集，每种动物每种类型都有一行，我稍后可以解析以给出存在的动物列表在我的桌子上.

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.

所以，在魔法之后，我会拥有

So, after the magic, I'd have

FISH 54
DOG 12
CAT 65
BIRD 10
GOAT 72

...从中列出我的名单.

...from which I would make my list.

到目前为止，我一直在混淆子查询，SELECT DISTINCT和SUBSTRING()，但是我觉得我想出的任何结果都可能与SO蜂巢式思维的能力相比是苍白的.有人可以帮忙吗?

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?

更新

如果有帮助，我的失败尝试说明了我想做什么:

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

推荐答案

这将适用于SQL Server.如果您使用其他东西，则需要找出相应的功能以左和 charindex .当然，可以用子字符串替换Left.

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.

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

结果:

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

在MySQL中，您可以使用左和定位. (代码未经测试)

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

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

这篇关于SQL选择不同的子字符串，如mundleup howto的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！