sql - SQL 查询检索某个名字

Question

编写 SQL 查询以检索位于 CA、TX、FL、NV、NM 州但不包括以下城市（达拉斯、旧金山、奥克兰）的贷款的贷款编号、州和城市、客户名字，并且只返回贷款其中客户的名字以 John 开头。

我尝试了什么：

SELECT [LoanNumber], [State], [City], [CustomerFname]
FROM dbo.Loan
WHERE STATE IN ('CA','TX','FL','NV','NM') 
AND CITY NOT IN ('DALLAS','SANFRANCISCO','OAKLAND')
HAVING [CustomerFname] = ' Mr.John'

标签： sql

Answer 1

仅在字符串末尾添加通配符条目 (%) 以获取以 John 开头的客户。

SELECT [LoanNumber],[State],[City],[CustomerFname]
    FROM dbo.Loan
    WHERE STATE IN( 'CA','TX','FL','NV','NM') 
    AND CITY NOT IN('DALLAS','SANFRANCISCO','OAKLAND')
    AND [CustomerFname] LIKE 'Mr.John%'