DECLARE @CustomerID         INT= 1,
        @VideoGenerateState INT= 2,
        @IsUpLoad           BIT= 0,
        @IsDelete           BIT= 0,
        @StartTime          DATETIME= '2022/5/1 0:00:00',
        @EndTime            DATETIME= '2024/3/27 23:59:59';

drop table if exists #tabMain;
SELECT  a.Id,
                a.DataSource,
                a.OrderSourceId,                
                a.OrderSourceCountry,
                a.Asin       AS ASIN,
                a.SKU,                
                a.VideoTemplateId,
                a.VideoTemplateName,                
                a.VideoUrl,
                a.OrderSourceSKUID,
                a.CustomerID,
                a.BatchId,
                a.OrderSourceSKU
                into #tabMain
FROM   dbo.RB_ProductVideo_List a WITH(NOLOCK)       
WHERE  a.CustomerID = @CustomerID
       AND a.VideoGenerateState = @VideoGenerateState
       AND a.IsUpLoad = @IsUpLoad
       AND a.IsDelete = @IsDelete
       AND a.VideoCheckTime >= @StartTime
       AND a.VideoCheckTime <= @EndTime
       AND a.SKU IN( '16543854', '16513508', '15882797', '16857008',
                     '16848393', '16513477', '16023119', '15920974',
                     '11704105', '16845593', '14114052', '13303610',
                     '15461343', '16509209', '16114087', '15911370',
                     '15442922', '16844964', '11701267', '15426448',
                     '15889764', '13301992', '16841857', '14094559',
                     '15845990', '15393252', '16838722', '16063457',
                     '16490646', '16035793', '14087394', '16023359' );

create index idx_OrderSourceId_BatchId_OrderSourceSKU_CustomerID on #tabMain(OrderSourceId,BatchId,OrderSourceSKU,CustomerID);

drop table if exists #tabBrand;
select distinct e.OrderSourceId,e.OrderSourceSKU,e.CustomerId,e.BrandName,f.ID AS BrandId into #tabBrand
from RB_Amazon_Product_Base_Brand e(NOLOCK)              
 LEFT JOIN dbo.RB_Product_Brand f(NOLOCK)
        ON e.CustomerId = f.CustomerID
            AND e.BrandName = f.BrandName
            AND f.IsActive = 1;
create index idx_OrderSourceId_OrderSourceSKU_CustomerId on #tabBrand(OrderSourceId,OrderSourceSKU,CustomerId);


SELECT DISTINCT a.Id,
                a.DataSource AS VideoType,
                a.OrderSourceId,
                c.OrderSourceName,
                a.OrderSourceCountry,
                a.Asin       AS ASIN,
                a.SKU,
                CASE
                  WHEN d.id IS NULL THEN 1 --没有策略的默认是腾讯   
                  ELSE d.ServiceProvider
                END          AS ServiceProvider,
                a.VideoTemplateId,
                a.VideoTemplateName,
                e.BrandId,
                e.BrandName,
                a.VideoUrl,
                a.OrderSourceSKUID
FROM   #tabMain a WITH(NOLOCK)
       LEFT JOIN dbo.RB_OrderSource c(NOLOCK)
              ON a.OrderSourceId = c.ID
                 AND a.CustomerID = c.CustomerID
       LEFT JOIN dbo.RB_ProductVideo_Strategy_History d(NOLOCK)
              ON a.CustomerID = d.CustomerID
                 AND a.BatchId = d.BatchId
       left join #tabBrand e        
              ON a.OrderSourceId = e.OrderSourceId
                 AND a.OrderSourceSKU = e.OrderSourceSKU
                 AND a.CustomerID = e.CustomerId;
drop table if exists #tabMain;
drop table if exists #tabBrand;

功能：erp webservice 旧版接口 查询sku视频广告数据；

请求方式：下面这个url链接直接打开：（测正式环境替换域名即可）

https://test-trunk.zhcxkj.com/Api/Index.ashx?Method=GetProductVideoList&VideoType=0&StartTime=2022-05-01&EndTime=2024-03-27&skus=16543854,16513508,15882797,16857008,16848393,16513477,16023119,15920974,11704105,16845593,14114052,13303610,15461343,16509209,16114087,15911370,15442922,16844964,11701267,15426448,15889764,13301992,16841857,14094559,15845990,15393252,16838722,16063457,16490646,16035793,14087394,16023359

由于测试环境缺少对应环境，查询sql后面会提示mongodb授权无效，代表前面数据查询没有报错