SQL查询中的问题 [英] Problem in SQL query

问题描述

我有这样的查询：

i have a query like that:

SELECT     FYear.Session, FeeHead.FeeHead ,  SUM(FeeItem.Amount) AS Total   
FROM         FeeItem 
INNER JOIN   FYear ON FYear.SessionID = FeeTransItem.SessionID
INNER JOIN   FeeHead ON FeeItem.FeeheadID = FeeHead.FeeHeadID       Where(FeeItem.FeeItemID = 18192)  
GROUP BY FYear.Tag ,FeeHead,FeeHead.Serial  

它的输出如下： -

And its output like:-

Session    FeeHead        Total
20172018   tuition         2000
20172018   Admission(AC)   3000
20172018   Annual(AC)      3000

但我需要的输出如下： -

But i need output like:-

Session    FeeHead        Total
20172018   tuition         2000
20172018   AnnualCharges   6000

这意味着如果费用头包含（AC）则更换费用头与AnnualCharges以及总金额之和。



我的尝试：

It means if feehead contain (AC) then feehead replace with AnnualCharges and also sum of total amount.

What I have tried:

SELECT     FYear.Session,case when FeeHead.FeeHead like '%(AC)%' then  replace(FeeHead.FeeHead,FeeHead.FeeHead,'Annual Charges')  else FeeHead.FeeHead end as FeeHead ,  
SUM(FeeItem.Amount) AS Total   
FROM         FeeItem 
INNER JOIN   FYear ON FeeItem.SessionID = FYear.SessionID 
INNER JOIN   FeeHead ON FeeItem.FeeheadID = FeeHead.FeeHeadID       Where(FeeItem.FeeItemID = 18192)  
GROUP BY FYear.Tag ,FeeHead,FeeHead.Serial  

但是它没有用。

But its not working.

推荐答案

在第一次使用时，也许你可以使用两个单独的查询并使用UNION：

Maybe you can use two separate queries and use UNION, in the first use:

WHERE FeeHead.FeeHead NOT LIKE '%(AC)%'

并在第二个查询中使用：

and in the second query use:

FeeHead.FeeHead LIKE '%(AC)%'

你写的绝对正确。只有我为该输出引入了一个查询。可能它会帮助你。

What you written absolute correct. only i introduced one more query for that output. may be it will help you.

with temp as (
SELECT FYear.Session, case when FeeHead.FeeHead like '%(AC)%' then  replace(FeeHead.FeeHead,FeeHead.FeeHead,'Annual Charges')  else FeeHead.FeeHead end as FeeHead,
SUM(FeeItem.Amount) AS Total   
FROM         FeeItem 
INNER JOIN   FYear ON FYear.SessionID = FeeTransItem.SessionID
INNER JOIN   FeeHead ON FeeItem.FeeheadID = FeeHead.FeeHeadID       Where(FeeItem.FeeItemID = 18192)  
GROUP BY FYear.Tag ,FeeHead,FeeHead.Serial  
)
select Session,FeeHead, SUM(Total) AS Total from temp
GROUP BY FeeHead

这篇关于SQL查询中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！