一、多表关联查询
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] = 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] = 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] = group by FlightId))t where rowNumber between and 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] = group by FlightId) order by t.FlightId desc
select * from #a where rowNumber between and
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(),
@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] = and Calsign like '%'+@calsign+'%' group by FlightId)
select * from #a where rowNumber between @PageSize*((@PageIndex)-) 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] = 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(),
@StartTime datetime,
@EndTime datetime,
@DepAddr nvarchar(),
@ArrAddr nvarchar(),
@TimeDiff int,
@Recount int output
AS
BEGIN
declare @sql nvarchar()
declare @countSql nvarchar()
declare @where nvarchar() =' WHERE 1=1 '
declare @cols nvarchar() =' 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() =' 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)-))
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