Question

我正试图在多个表格中填写一个PIVOT(管理服务器2008年),没有涉及总的功能。我在这里的深 to中不得不坦率地讲我,在努力确定问题的时候,我只想跳跃,向你们展示我的 st(oo),首先,我有三张表格:

CHARTER_vessels
===============

vesselID    vesselName
--------    ----------
1           The Titanic
2           The Pinafore
3           The Black Pearl


CHARTER_rateDateRange
=====================

rateDateRangeID     rateDateRangeName
---------------     -----------------
1                   Spring 2012
2                   Summer 2012
3                   Fall 2012


CHARTER_rates
=============

vesselID     rateDateRangeID      rateCost
--------     ---------------      --------
1            1                    434
1            2                    445
1            3                    231
2            1                    675
2            2                    545
2            3                    768
3            1                    543
3            2                    654
3            3                    658

And the output I m trying to achieve is that the rates for each boat appear in the column for each season, like this:

vesselName         Spring 2012     Summer 2012     Fall 2012
----------         -----------     -----------     ---------
The Titanic        434             445             231
The Pinafore       675             545             768
The Black Pearl    543             654             658

显然,我希望能够尽可能区分不同栏目确定的结果!

Answer 1

以下假设船只和日期范围的独特性。如果情况如此,你不想把一片烟.混为一谈,不是你们的事。配对器(Cost)是使用SQ服务器机块的要求。需要建立一个SQ服务器机制,以决定如果一艘船舶有同一日期的多个,将如何返回。如果发生这种情况,总合实际上毫无意义。另一种选择是一系列自我结合。让我知道,你们是否需要看到自我加入的解决办法。

SELECT src.vesselName,pvt.[Spring 2012], pvt.[Summer 2012], pvt.[Fall 2012]
FROM
(select vesselName, rateCost, rateDateRangeName 
from CHARTER_rateDateRange crd
inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
inner join CHARTER_vessels cv on cv.vesselID   = crd.vesselID) AS src
PIVOT
(
max(rateCost)
FOR rateDateRangeName IN ([Spring 2012], [Summer 2012], [Fall 2012])
) AS pvt;

为什么没有其他人这样做,就是自我加入的解决办法。审慎态度根本不是最佳的。

with joinMe as (
select vesselName, rateCost, rateDateRangeName 
from CHARTER_rateDateRange crd
inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
inner join CHARTER_vessels cv on cv.vesselID   = crd.vesselID
)

select a.vesselName,a.rateCost as  Spring 2012 ,b.rateCost as  Summer 2012 ,c.rateCost as  Fall 2012 
from joinMe a
inner join joinMe b on b.vesselName= a.vesselName
                   and b.rateDateRangeName =  Summer 2012 
inner join joinMe c on c.cesselName = a.vesselName
                    and c.rateDateRangeName =  Fall 2012 
where a.rateDateRangeName =  Spring 2012

由于篇幅有限,我将在此向各位发出问答。你们的以下回报是多少?

select vesselName, rateDateRangeName,count(rateCost)
    from CHARTER_rateDateRange crd
    inner join CHARTER_rates cr on cr.rateDateRangeID = crd.rateDateRangeID
    inner join CHARTER_vessels cv on cv.vesselID   = cr.vesselID
group by vesselName,rateDateRangeName 
order by count(rateCost) desc

友情链接