SQL通过表变量，求百分比的小例子

关注

原创最新推荐文章于 2026-05-24 18:00:00 发布 733 阅读 AI 写同款· GEO 优化 ›

sql需求技术小结—排序后取百分比 取某一字段rank全局排序后前百分之几的数据的需求一般使用ntle函数的方法可以快速实现，但这种方法有瑕疵，原因：ntile函数阅读详情

DECLARE @TempTotalTable TABLE (TotalCount INT ,Task_KeyWord varchar(100),SearchEngine_Name varchar(100),Brand_Name varchar(100))
DECLARE @TempGiveUpTable TABLE (GiveUPCount INT ,Task_KeyWord varchar(100),SearchEngine_Name varchar(100),Brand_Name varchar(100))
INSERT @TempTotalTable
 (TotalCount,Task_KeyWord,SearchEngine_Name,Brand_Name)
 (SELECT 
      COUNT(1) AS 抓取总数
      ,Task_KeyWord
      ,SearchEngine_Name
      ,Brand_Name         
  FROM [SEC_Item] as A ,[IWT_Brand] as B ,SEC_SearchEngine as C
  WHERE A.brand_id=B.brand_id and A.brand_id=30015 and C.SearchEngine_ID=A.SearchEngine_ID   GROUP BY Task_KeyWord,SearchEngine_Name,Brand_Name
 )
 INSERT @TempGiveUpTable
 (GiveUPCount,Task_KeyWord,SearchEngine_Name,Brand_Name)
 (SELECT 
      COUNT(1) AS 放弃数
      ,Task_KeyWord
      ,SearchEngine_Name
      ,Brand_Name          
  FROM [SEC_Item] as A ,[IWT_Brand] as B ,SEC_SearchEngine as C
  where A.brand_id=B.brand_id and A.brand_id=30015 and C.SearchEngine_ID=A.SearchEngine_ID AND Item_State IN (4,6)    GROUP BY Task_KeyWord,SearchEngine_Name,Brand_Name 
  )
  SELECT ISNULL(cast(cast(B.GiveUPCount*1.0/A.TotalCount*100 as decimal(10,1)) as varchar(20))+'%','0%')as [百分比],ISNULL(B.GiveUPCount,0) as 放弃数 , A.TotalCount as 抓取总数,A.Task_KeyWord,A.SearchEngine_Name,A.Brand_Name FROM @TempTotalTable as A  LEFT JOIN @TempGiveUpTable as B ON A.Brand_Name=B.Brand_Name AND  A.Task_KeyWord=B.Task_KeyWord AND A.SearchEngine_Name=B.SearchEngine_Name ORDER BY  A.TotalCount DESC

收录于