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

最新推荐文章于 2024-09-14 21:52:32 发布

ArvinStudy

最新推荐文章于 2024-09-14 21:52:32 发布

阅读量641

点赞数

分类专栏： █小小代码 SQL 文章标签： sql insert table join c

本文链接：https://blog.csdn.net/ArvinStudy/article/details/7732230

版权

SQL 同时被 2 个专栏收录

121 篇文章 0 订阅

订阅专栏

█小小代码

108 篇文章 0 订阅

订阅专栏

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