商品以省市方式显示百分比数量

最新推荐文章于 2022-10-20 10:26:02 发布

唐朝小秀才-001

最新推荐文章于 2022-10-20 10:26:02 发布

阅读量287

点赞数 1

分类专栏： sql技术文章标签： sql统计百分比转换行转列

本文链接：https://blog.csdn.net/qq_31764359/article/details/52152592

版权

sql技术专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SET @sql = 'SELECT DISTINCT dbo.ISS_Base_Commodity.Name [商品] ,SUM(Numer) [总销量]'
SELECT  @sql = @sql
        + ' ,CAST(ROUND(CAST(SUM(CASE dbo.ISS_System_Province.Name when '''
        + [省份]
        + ''' THEN Numer ELSE 0 END) AS FLOAT) / CAST(SUM(Numer) AS FLOAT), 4)  * 100 AS VARCHAR(20))+''%''  ['
        + [省份] + ']'
FROM    ( SELECT DISTINCT
                    Name [省份] ,
                    PKID
          FROM      dbo.ISS_System_Province
          GROUP BY  PKID ,
                    Name
        ) a 
SET @sql = @sql
    + 'FROM     ISS_Base_Commodity
				INNER JOIN ISS_Main_OrderInfo ON ISS_Base_Commodity.PKID = ISS_Main_OrderInfo.ISS_Base_Commodity_PKID
				INNER JOIN ISS_Base_Customer ON ISS_Main_OrderInfo.ISS_Base_Customer_PKID = ISS_Base_Customer.PKID
				INNER JOIN ISS_System_Province ON ISS_Base_Customer.ISS_System_Province = ISS_System_Province.PKID
			    GROUP BY ISS_Base_Commodity.Name'
			    PRINT @sql
EXEC(@sql)