SQL 根据字段开头的内容选择记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6192499/
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
Select records based on what a field begins with?
提问by dzilla
I have a database with a table that has identifiers c1, c2, c3..etc..
我有一个包含标识符 c1、c2、c3.. 等的表的数据库。
Instead of writing a query that has a bunch of ORs in it, how can I modify the below query with something that will catch all the records that begin with a certain letter?
不是编写一个包含一堆ORs的查询，我如何修改下面的查询，以捕获以某个字母开头的所有记录？
SELECT
Person.spineinjuryAdmit,
tblComorbidity.comorbidityexplanation,
Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties
INNER JOIN (tblComorbidity
INNER JOIN (Person
INNER JOIN tblComorbidityPerson
ON Person.PersonID = tblComorbidityPerson.personID)
ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK)
ON tblKentuckyCounties.ID = Person.County
GROUP BY Person.spineinjuryAdmit,
tblComorbidity.comorbidityexplanation
HAVING (((Person.spineinjuryAdmit)="c1" Or
(Person.spineinjuryAdmit)="c2" Or
(Person.spineinjuryAdmit)="c3"));
采纳答案by JK0124
SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County
GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation
HAVING (Person.spineinjuryAdmit LIKE "c*");
回答by Rob
Have you tried using LIKE? As an example:
你试过使用LIKE吗？举个例子：
SELECT * FROM patients WHERE lastName LIKE 'm%';
This would return records where patients.lastNamestarts with 'm'. The '%' character may be '*' for access, I can't remember. In some databases, you can also use '_' which will match a single character (or however many underscores you add).
这将返回patients.lastName以“m”开头的记录。'%' 字符可能是用于访问的 '*'，我不记得了。在某些数据库中，您还可以使用“_”来匹配单个字符（或您添加的多个下划线）。
回答by HansUp
You can use a WHERE clause to exclude the rows you don't want before doing the GROUP BY.
在执行 GROUP BY 之前，您可以使用 WHERE 子句排除不需要的行。
SELECT
p.spineinjuryAdmit,
c.comorbidityexplanation,
Count(c.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties AS k
INNER JOIN (tblComorbidity AS c
INNER JOIN (Person AS p
INNER JOIN tblComorbidityPerson AS cp
ON p.PersonID = cp.personID)
ON c.ID = cp.comorbidityFK)
ON k.ID = p.County
WHERE p.spineinjuryAdmit ALike "c%"
GROUP BY p.spineinjuryAdmit,
c.comorbidityexplanation
If your query is executed in SQL-89 mode, you can use this as your WHERE clause.
如果您的查询以 SQL-89 模式执行，您可以将其用作 WHERE 子句。
WHERE p.spineinjuryAdmit Like "c*"
In SQL-92 mode, you need the standard ANSI wild card.
在 SQL-92 模式下，您需要标准的 ANSI 通配符。
WHERE p.spineinjuryAdmit Like "c%"
I used ALike to tell the database engine to expect ANSI wild cards.
我使用 ALike 告诉数据库引擎期待 ANSI 通配符。
SQL-89 mode is used by DAO ... unless you've set the database option to use SQL-92 mode ("SQL Server compatible syntax).
DAO 使用 SQL-89 模式...除非您已将数据库选项设置为使用 SQL-92 模式（“SQL Server 兼容语法）”。
If you're running a query with ADO, it will always use SQL-92 mode.
如果您使用 ADO 运行查询，它将始终使用 SQL-92 模式。
回答by rskar
You have two options:
您有两个选择：
For example:
例如：
Person.spineinjuryAdmit LIKE "c*"
Person.spineinjuryAdmit IN ("c1", "c2", "c3")
See http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspxfor details about LIKE.
有关 LIKE 的详细信息，请参阅http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx。
Fair warning: The wildcards of LIKE in Access are *and ?instead of %and _(as is the case for most other versions of SQL).
公平警告：Access 中 LIKE 的通配符是*and?而不是%and _（与大多数其他 SQL 版本的情况一样）。
回答by p.campbell
You could modify this to include the filter list in the WHEREclause. The following will find patients whose last name starts with Smith. (i.e. Smithand Smithson, etc), and those whose Admitstart with c.
您可以修改它以在WHERE子句中包含过滤器列表。以下将查找姓氏开头的患者Smith。（即Smith和Smithson等），以及那些Admit以c.
....
WHERE spineinjuryAdmit LIKE 'c*'
AND Patient.FirstName LIKE 'Smith*'
GROUP BY Person.spineinjuryAdmit,
tblComorbidity.comorbidityexplanation;
回答by thodoris
You can use regexp to query all rows that starts with several characters.
您可以使用 regexp 查询以多个字符开头的所有行。
SELECT * FROM table WHERE column REGEXP '^[ c1, c2, c3]';
This query will return all rows where column starts with 'c1' or 'c2' or 'c3'.
此查询将返回列以“c1”或“c2”或“c3”开头的所有行。
