如何在SQL中汇总聚合函数 [英] How to sum aggregate function cast in SQL

问题描述

Declare @PeriodType varchar(10)='1'
 Declare @month varchar(10)='03'
 Declare @year varchar(10)='2018'
 
 
 SELECT Shrimp_Name,Grades,Japan,EU,US  
 FROM (  
 SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end)) as 'Price'  from   
 Tbl_Shrimp_Price TSP  
 inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region  
 inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade  
 inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType  
 WHERE TSP.ShrimpType=1 and TSP.PeriodType=@PeriodType AND 
 --PostMonth =cast('01-'+@year1 as Varchar(20))
 DATEPART(year, CONVERT(date, PostMonth, 105)) = @year 
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month
 ) as s  
 PIVOT  
 (  
SUM(Cast(Price) as Decimal(18,5)
 FOR [Region_Name] IN (Japan, EU, US)  
 )AS pvt  
 ORDER BY Shrimp_Name,Grades  
  
 SELECT Shrimp_Name,Grades,Japan,EU,US  
 FROM (  
 SELECT MSN.Shrimp_Name,TMG.ID,TMG.Grades,TMR.Region_Name,(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end) as 'Price'  from    
 Tbl_Shrimp_Price TSP  
 inner join Tbl_Mast_Shrimp_Region TMR On  TMR.ID=TSP.Region  
 inner join Tbl_Mast_Shrimp_Grades TMG On TMG.ID=TSP.Grade  
 inner join Tbl_Mast_Shrimp_Name MSN On MSN.ID=TSP.ShrimpType  
 WHERE TSP.ShrimpType=2  and TSP.PeriodType=@PeriodType  
 AND --PostMonth = cast('01-'+@year1 as Varchar(20))
 DATEPART(year, CONVERT(date, PostMonth, 105)) = @year 
AND DATEPART(month, CONVERT(date, PostMonth, 105)) = @month 
 ) as s  
 PIVOT  
 (  
SUM(Cast(Price) as Decimal(18,5)
 FOR [Region_Name] IN (Japan, EU, US)  
 )AS pvt  
 ORDER BY Shrimp_Name,Grades

我尝试过：



以上是我的查询，我想替换0.00而不是' - '

我收到错误以上是我的查询所以请帮助我 -
$执行后b $ b错误 -

What I have tried:

Above is my Query and i want to Replace instead of 0.00 to '-'
and i am getting error above is my query so please help me-
error after execution-

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'Price'.
Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '('.

推荐答案

Quote：

(case when cast(TSP.Price as nvarchar(50))='0.00' then '-' else cast(TSP.Price as nvarchar(50)) end)) as 'Price'

计算括号 - 你也有一个很多人g表示括号。

Count the parentheses - you have one too many closing parentheses on that expression.

这篇关于如何在SQL中汇总聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！