子查询案例 [英] Sub querying with a case

问题描述

您好



我对以下查询有疑问。



我需要使用case在子查询中选择有一个group by，但我似乎无法正确地将它链接到主查询，以便正确显示金额。



请看一下：

Hi

I have a problem with the following query.

I need to use a case select in a sub query that has a group by, but i can't seem to correctly link it to the main query, so that the amount display correctly.

Please have a look:

Select SiteID,SiteName,Score as CurrentScore,
(select case @datrange
        WHEN 'Daily'
        THEN (select ss.score from sites ss where Date between 
              dateadd(dd,@Datefrom,-1) and
               DateAdd(dd,@dateTo,-1) 
               Group By ss.SiteID)
       WHEN 'Monthly'
       THEN (select ss.score from sites ss where datefrom between 
              dateadd(mm,@Datefrom,-1) and
               DateAdd(mm,@dateTo,-1) 
               Group By ss.SiteID)
) As previous1,
(select case @datrange
        WHEN 'Daily'
        THEN (select ss.score 
              from sites ss where Date between 
              dateadd(dd,@Datefrom,-2) and
               DateAdd(dd,@dateTo,-2)
               Group By ss.SiteID)
       WHEN 'Monthly'
       THEN (select ss.score 
               from sites ss where datefrom between 
              dateadd(mm,@Datefrom,-2) and
               DateAdd(mm,@dateTo,-2) 
               Group By ss.SiteID)
) As previous2

From Site
Where Date Between @Datefrom and @DateTo
Group by
SiteID

所以当我这样做时，我显然得到了子查询的错误返回多个值，我只需要将Sub查询正确加入主查询。



所以值显示如下：



SiteID | Sitename | CurrentScore |上一页1 |上一页2

1 |杰里科| 3.62 | 2.27 | 2.31

2 |小屋| 4.26 | 5.21 | 5.22



提前感谢您的任何帮助和努力。

So when I do this I obviously get the error of the subquery returns more than one value, I just need to join the Sub query correctly to the main query.

so the values display correctly as follows:

SiteID | Sitename | CurrentScore | Previous1 | Previous2
1 | Jerico | 3.62 | 2.27 | 2.31
2 | Hut | 4.26 | 5.21 | 5.22

Thank you in advance for any help and efforts.

推荐答案

它被称为Pivot查询。



看看：

使用PIVOT和UNPIVOT [ ^ ]

SQL SERVER中的Pivot和Unpivot表 [ ^ ]

< a href =http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=UNPIVOTData>如何使用T-SQL取消隐藏数据 [ ^ ]



在单个查询中使用多个子查询效率很低！

It is called Pivot query.

Have a look:
Using PIVOT and UNPIVOT[^]
Pivot and Unpivot table in SQL SERVER[^]
How to UNPIVOT Data Using T-SQL [^]

Using many subqueries in a single query is inefficient!

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