SQLSERVER行转列以及分页操作

Apr 21, 2021 Eric Guo    675

摘要：记录常用的行转列操作以及原生分页操作，有区别于mysql

Sqlserver行转列

OP 预警相关
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Eric.Guo>
-- Create date: <2021-04-16>
-- Description:    <同步OP预警数据>
-- =============================================
CREATE PROCEDURE [dbo].[sync_sku_alert] 
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    insert into [B2C].[dbo].[ORD_SKU_Alert_Detail]
    (
    [MarketPlace]
      ,[Product]
      ,[OP]
      ,[Week]
      ,[Country]
      ,[Channel]
      ,[InventoryValue]
      ,[WithoutSalesWarning]
      ,[ProfitDeclineWarning]
      ,[SalesDSWarning]
      ,[TrafficWarning]     
      ,[CreateTime]
      ,[CreateUser]
    )
    (SELECT [ORD_SKU_Alert].[MarketPlace]
    ,[ORD_SKU_Alert].[SKU]
      ,[ORD_SKU_Alert].[OP]
      ,[ORD_SKU_Alert].[Week]
      ,[OC_Marketing].Country
      ,[OC_Marketing].Channel
      ,null as InventoryValue
      ,Max(case when [AlertName]='近7天无销售预警' then Duration else '' end) as WithoutSalesWarning
    ,Max(case when [AlertName]='利润率下降预警' then Duration else '' end) as ProfitDeclineWarning
    ,Max(case when [AlertName]='销售与DS差异预警' then Duration else '' end) as SalesDSWarning
    ,Max(case when [AlertName]='流量预警' then Duration else '' end) as TrafficWarning
    ,getDate() as CreateTime
    ,'CrontabJob' as CreateUser
  FROM [B2C].[dbo].[ORD_SKU_Alert] 
  left join [OceaniaERP].[dbo].[OC_Marketing]
  on [ORD_SKU_Alert].[MarketPlace] = [OC_Marketing].Name and [ORD_SKU_Alert].[UpdateTime] is null

  group by [MarketPlace]
    ,[SKU]
      ,[Week]
      ,[OP]
      ,Channel
      ,Country);

    -- 更新原始表信息状态
    update [B2C].[dbo].[ORD_SKU_Alert] set UpdateTime = getDate();

END
GO

分页

SELECT NUM
FROM dbo.Numbers
ORDER BY Num
OFFSET 100 ROWS
FETCH NEXT 5 ROWS ONLY

offset 后面是从多少条数据开始查找
fentch next 后面跟查找多少条数据
和MSQL的limit方法很像
评论