Notes on SQL multi condition fuzzy query, in, custom function (1)

Time:2020-7-14

Some problems encountered in the work about SQL query, sort out the records, and implement the environment sqlservice 2014

1、 On the same field, several ways to query multiple keywords

Basic grammar:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

Tbuser:

Id LastName FirstName
1 Adams John
2 Bush George
3 Carter Thomas

 

Query statement implementation:

1. Single keyword fuzzy query

        SELECT * FROM TbUser WHERE  LastName LIKE ‘%d%’   

2. Multiple keyword queries

Implementation 1: Select*

FROM TbUser

WHERE LastName like ‘%d%’   or   LastName LIKE ‘%e%’   or   LastName LIKE ‘%d%’  

 

Realization 2:SELECT TbUser.LastName,temp.m

FROMTbUser,(values(‘%a%’),(‘%b%’),(‘%c%’)) as temp(m)

FROM TbUser.LastName LIKE temp.m

 

3SELECT LastName 

FROM TbUser 

WHERE LastName LIKE any(array[‘%a%’,’%b%’,’%e%’])

 

Realization 4:SELECT LastName  

FROM TbUser  

WHERE LastName LIKE  ‘%[a|b|e]%’

 

2、 The disguised realization of in keyword in SQL

When there are many matching data, in needs to be compared one by one, which is inefficient. Constructing left join structure will greatly improve the efficiency.

Grammar:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Implementation 1:

SELECT *

FROM TbUser

WHERE Id in (1,2,3,4)

 

Realize the second 

SELECT  x FROM TbUser  u

LEFT JOIN  TbDetails as d on u.Id=d.FkUserId

 WHERE d.FkUserId is not null

 

3

If another array needs in query, you can construct a temporary table for inner join query as follows

SELECT u.*

FROM bUser  u

INNER JOIN ( VALUES(‘a’),(‘b’),(‘c’),(‘d’),(‘e’) ) as  temp(m) ON u.LastName=temp.m

 

 

 

Recommended Today

What is rustc

Hello, World! – The Rust Programming Language In the Hello world example given in the official website document, I see that the compiled command uses rustc. How did rustc get here Take windows 10 as an examplerustup- init.exe 64 bitAnd the correct installation, rustc will also be installed. deposit rustc.exe What’s the location of this%HomePath%\.cargo\bin […]