SQL Server multi-table joint query and multi-table paging query methods (code example)

What this article brings to you is about the methods of SQL Server multi-table joint query and multi-table paging query (code examples). It has certain reference value. Friends in need can refer to it. , hope it helps you.

Multi-table joint query:

select p.*,s.Sheng , i.Shi
from  [dbo].[ProductRecordInfo]   --表名 
p left join [ShengInfo] s on p.ShengInfo = s.ShengId   --使用left join左连接 让两个表中的指定字段产生连接关系
left join [ShiInfo] i on p.ShiInfo = i.ShiId          --使用left join左连接 让三个表中的指定字段产生连接关系

The on here is similar to where, and the following conditions can be written by yourself (recommended course: MySQL tutorial )

The running results are as follows:

##Paging Sql statement:

Use the row_number() function for numbering

select * from (select ROW_NUMBER() over (order by Id ASC) as IDD ,*from ProductRecordInfo) a where a.IDD>=1 and a.IDD<=3

First sort by Id. After sorting, number each piece of data.

In this statement, the ROW_NUMBER() function will number each row returned by the SELECT statement starting from 1 and give it a consecutive number. After applying a sorting criterion during query, only numbering can ensure that the order is consistent. When using the ROW_NUMBER function, a special column is also needed for pre-sorting to facilitate numbering

The results are as follows:

Finally, if you want to use ADO to display the paging function, you need to combine multi-table join query with paging. The spliced ​​statement is as follows:

select * from(
   select *, ROW_NUMBER() OVER(order by Id asc) row from 
     (select p.*,s.Sheng,i.Shi,a.PinPai 
from  [dbo].[ProductRecordInfo] 
p left join [ShengInfo] s on p.ShengInfo = s.ShengId 
left join [ShiInfo] i on p.ShiInfo = i.ShiId 
left join[dbo].[PinPaiInfo] a on p.PinPaiInfo=a.Aid)t1)t2 
where t2.Row between 1 and 3

The result is as follows:

Note: We will definitely use these sql keywords when conducting multi-table joint queries in the database. If we do not understand their differences, we will not be able to write the query conditions required for our project.

The above is the detailed content of SQL Server multi-table joint query and multi-table paging query methods (code example). For more information, please follow other related articles on the PHP Chinese website!