随笔 - 45  文章 - 0  评论 - 11  阅读 - 15万

SQL-Server多表关联查询并分页

一、多表关联查询


1,left join

RelaTimeLog表 和 ValidFlight表关联查询
select t.FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd  FROM [CDM].[dbo].[ValidFlight] t  left join [CDM].[dbo].[RelaTimeLog] f on t.FlightId = f.FlightId where f.[Type] = 5 order by t.FlightId desc 

2,与group by连用

select t.FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd FROM [CDM].[dbo].[ValidFlight] t where FlightId in (select FlightId from [CDM].[dbo].[RelaTimeLog] where [Type] =5 group by FlightId) order by t.FlightId desc

二、分页

1,查询0到10条

select  t.FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd from (select *,ROW_NUMBER() over (order by FlightId Desc) as rowNumber FROM [CDM].[dbo].[ValidFlight] 
where FlightId in (select FlightId from [CDM].[dbo].[RelaTimeLog] where [Type] =5 group by FlightId))t where rowNumber between 0 and 10 order by t.Etd desc

2,BETWEEN *  AND *  ,  Row_Number() OVER(ORDER BY *) AS rowNum 

select t.FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd,ROW_NUMBER() over (order by FlightId Desc) as rowNumber into #a FROM [CDM].[dbo].[ValidFlight] t 
where FlightId in (select FlightId from [CDM].[dbo].[RelaTimeLog] where [Type] =5 group by FlightId) order by t.FlightId desc select * from #a where rowNumber between 0 and 10 select COUNT(*) as totalCount from #a drop Table #a

3,以 BETWEEN *  AND *  ,  Row_Number() OVER(ORDER BY *) AS rowNum为例的分页存储过程

复制代码
USE [CDM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_GetModifyTOBTFlight]
@PageIndex int,
@PageSize int,
@calsign nvarchar(50),
@Recount int output
AS
BEGIN
     
 if @calsign<>''
    BEGIN
     select CONVERT(int,t.FlightId) as FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd, ROW_NUMBER() over (order by Etd Desc) as rowNumber into #a FROM [CDM].[dbo].[ValidFlight] t 
                 where FlightId in (select FlightId from [CDM].[dbo].[RelaTimeLog] where [Type] =5 and Calsign like '%'+@calsign+'%' group by FlightId)  
     select * from #a  where rowNumber between @PageSize*((@PageIndex)-1) and @PageSize*@PageIndex 
     set @Recount = (select COUNT(*) from #a)
     drop Table #a
    END
 else
   BEGINselect CONVERT(int,t.FlightId) as FlightId,t.Calsign,t.DepAddrName,t.ArrAddrName,t.Etd,t.Atd,t.Ata, t.Eta,ROW_NUMBER() over (order by etd Desc) as rowNumber into #b FROM [CDM].[dbo].[ValidFlight] t 
           where FlightId in (select FlightId from [CDM].[dbo].[RelaTimeLog] where [Type] =5 group by FlightId)
if@pageIndex >1
begin
    select * from #b  where rowNumber between (@PageSize*((@PageIndex)-1)+1) and @PageSize*@PageIndex 
  end
else
begin
    select * from #b  where rowNumber between @PageSize*((@PageIndex)-1) and @PageSize*@PageIndex 
  end    set @Recount = (select COUNT(*) from #b)
    print @Recount
    drop Table #b 
  END
END
复制代码

4,拼接SQL语句

复制代码
USE [CDM]
GO
/****** Object:  StoredProcedure [dbo].[p_GetHistoryFlight]    Script Date: 2018/12/29 14:32:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_GetHistoryFlight]
@PageIndex int,
@PageSize int,
@GsName nvarchar(20),
@StartTime datetime,
@EndTime datetime,
@DepAddr nvarchar(25),
@ArrAddr nvarchar(25),
@TimeDiff int,
@Recount int output
AS
BEGIN
declare @sql nvarchar(1000)
declare @countSql nvarchar(1000)
declare @where nvarchar(1000) =' WHERE  1=1 '
declare @cols nvarchar(1000) =' Id,CONVERT(INT,FlightId) AS FlightId,Calsign,FlyTypeName,GsName,AcfTypeName,Stand,Etd,CTOT,Atd,Eta,Ata,TOBT,ApplicateTime,DepAddrName,ArrAddrName,1 AS TimeDiff '

declare @sort nvarchar(50) =' ORDER BY Etd desc'
if @StartTime IS NOT NULL
    BEGIN
        set @where=@where+' AND Etd>='''+CONVERT(nvarchar,@StartTime)+''''
    END
if @EndTime IS NOT NULL
    BEGIN
        set @where=@where+' AND Etd<='''+CONVERT(nvarchar,@EndTime)+''''
    END
if @DepAddr<>''
    BEGIN
    SET @where=@where + ' AND DepAddrName LIKE ''%'+@DepAddr+'%'''
    END
if @DepAddr<>''
BEGIN
    SET @where=@where + ' AND ArrAddrName LIKE ''%'+@ArrAddr+'%'''
END
print(@where)
if @GsName<>''
    BEGIN
        set @where=@where+' AND GsName LIKE ''%'+@GsName+'%'''
    END
set @sql=N'SELECT TOP '+STR(@PageSize) +' * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Id desc) as rowid, ' + @cols +' FROM [CDM].[dbo].[ValidFlightHistory] ' +@where +') AS A  WHERE rowid>'+STR(@PageSize*((@PageIndex)-1))
print @sql
exec(@sql)


set @countSql=N'SELECT @total=count(*) FROM [CDM].[dbo].[ValidFlightHistory] '+@where
print @countSql

exec sp_executesql @countSql,N'@total int out',@total=@Recount out
END
复制代码

 

posted on   沫丶灬沫  阅读(3644)  评论(0编辑  收藏  举报
编辑推荐:
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行:
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· ollama系列1:轻松3步本地部署deepseek,普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 按钮权限的设计及实现
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

点击右上角即可分享
微信分享提示