文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >SQL查询扭转表

问SQL查询扭转表
EN

Stack Overflow用户
提问于 2013-03-22 19:37:24
回答 2查看 618关注 0票数 1

我有一个sql查询，它从几个表中选择数据并进行一些计算。它产生的结果如下所示：

代码语言：javascript
复制
year    month   avg region
2011    1       123 UK
2012    1       0   UK
2013    1       0   UK
2011    2       400 UK
2012    2       200 UK
2013    2       0   UK

但是我希望结果是这样的：

代码语言：javascript
复制
month   year1   year2   year3   region
1       123     0       0       UK
2       400     200     0       UK

下面是我当前的查询：

代码语言：javascript
复制
SELECT TOP (100) PERCENT DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
                         DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
                         AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
                         dbo.Region.GroupName AS Region
FROM dbo.Fixtures
INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
  AND (dbo.Vessel.TypeId = 1)
  AND (dbo.Vessel.Total_BHP >= 15000)
  AND (dbo.Vessel.Total_BHP < 20000)
GROUP BY dbo.Region.GroupName,
         DATEPART(YEAR, dbo.Fixtures.Date),
         DATEPART(MONTH, dbo.Fixtures.Date)
ORDER BY YEAR, MONTH

这个是可能的吗？附注:只会有最近3年的数据。

Edit:这是我的SQL查询的结果。

代码语言：javascript
复制
year        month   avgPC   Region
2011    1   7300    Norway
2011    1   6818    United Kingdom
2011    2   8500    Norway
2011    2   6235    United Kingdom
2011    3   15400   Norway
2011    3   0       South America
2011    3   12545   United Kingdom
2011    4   22887   Norway
2011    4   17231   United Kingdom
2011    5   24033   Norway
2011    5   9730    United Kingdom
2011    6   24249   Norway
2011    6   25000   United Kingdom
2011    7   23310   Norway
2011    7   25495   United Kingdom
2011    8   35890   Norway
2011    8   23000   United Kingdom
2011    9   28055   Norway
2011    9   33510   United Kingdom
2011    10  66437   Norway
2011    10  53713   United Kingdom
2011    11  26300   Norway
2011    11  14264   United Kingdom
2011    12  10800   Norway
2011    12  14553   United Kingdom

这是我在执行下面建议的piv查询时得到的结果：

代码语言：javascript
复制
m   year1   year2   year3   region
1   NULL    0   8500    Norway
2   NULL    NULL    NULL    Norway
3   NULL    NULL    NULL    Norway
4   NULL    NULL    NULL    Norway
5   NULL    NULL    NULL    Norway
6   NULL    NULL    NULL    Norway
7   NULL    NULL    NULL    Norway
8   NULL    NULL    NULL    Norway
9   NULL    NULL    NULL    Norway
10  NULL    NULL    NULL    Norway
11  NULL    NULL    NULL    Norway
12  NULL    NULL    NULL    Norway
3   NULL    NULL    NULL    South America
1   10250   6000    13000   United Kingdom
2   NULL    NULL    NULL    United Kingdom
3   NULL    NULL    NULL    United Kingdom
4   NULL    NULL    NULL    United Kingdom
5   NULL    NULL    NULL    United Kingdom
6   NULL    NULL    NULL    United Kingdom
7   NULL    NULL    NULL    United Kingdom
8   NULL    NULL    NULL    United Kingdom
9   NULL    NULL    NULL    United Kingdom
10  NULL    NULL    NULL    United Kingdom
11  NULL    NULL    NULL    United Kingdom
12  NULL    NULL    NULL    United Kingdom
sql-server
pivot
sql
EN

回答 2

Stack Overflow用户

发布于 2013-03-22 19:43:43

您没有指定您使用的是什么RDBMS，但是由于您使用的是TOP，所以我猜是SQL Server。

在SQL Server中，您可以使用PIVOT函数转换数据：

代码语言：javascript
复制
SELECT month,
  [2011] as year1, 
  [2012] as year2, 
  [2013] as year3,
  region
FROM
(
  SELECT DATEPART(year, dbo.Fixtures.Date) AS Year, 
    DATEPART(Month, dbo.Fixtures.Date) AS Month, 
    dbo.Fixtures.PCRate, 
    dbo.Region.GroupName AS Region
  FROM  dbo.Fixtures 
  INNER JOIN dbo.Vessel 
    ON dbo.Fixtures.VesselId = dbo.Vessel.ID 
  INNER JOIN dbo.Region 
    ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(year, - 2, GETDATE()))) 
    AND (dbo.Vessel.TypeId = 1) 
    AND (dbo.Vessel.Total_BHP >= 15000) 
    AND (dbo.Vessel.Total_BHP < 20000)
) src
pivot
(
  avg(PCRate)
  for year in ([2011], [2012], [2013])
) piv;

如果您不想硬编码年份值，则可以使用类似于以下内容的代码：

代码语言：javascript
复制
SELECT month,
  year1, 
  year2, 
  year3,
  region
FROM
(
  SELECT 
    DATEPART(Month, dbo.Fixtures.Date) AS Month, 
    dbo.Fixtures.PCRate, 
    dbo.Region.GroupName AS Region,
    'year'+cast(row_number() over(partition by DATEPART(year, dbo.Fixtures.Date) 
                      order by DATEPART(year, dbo.Fixtures.Date)) as varchar(4)) year
  FROM  dbo.Fixtures 
  INNER JOIN dbo.Vessel 
    ON dbo.Fixtures.VesselId = dbo.Vessel.ID 
  INNER JOIN dbo.Region 
    ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(year, - 2, GETDATE()))) 
    AND (dbo.Vessel.TypeId = 1) 
    AND (dbo.Vessel.Total_BHP >= 15000) 
    AND (dbo.Vessel.Total_BHP < 20000)
) src
pivot
(
  avg(PCRate)
  for year in (year1, year2, year3)
) piv;

编辑#1，很难从您发布的数据中看出问题是什么，但是如果您不能从子查询中删除avg，那么您应该能够使用：

代码语言：javascript
复制
select month, [2011] as year1, [2012] as year2, [2013] as year3, region
from
(
  SELECT 
    DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
    DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
    AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
    dbo.Region.GroupName AS Region
  FROM dbo.Fixtures
  INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
  INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
  WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
    AND (dbo.Vessel.TypeId = 1)
    AND (dbo.Vessel.Total_BHP >= 15000)
    AND (dbo.Vessel.Total_BHP < 20000)
  GROUP BY dbo.Region.GroupName,
           DATEPART(YEAR, dbo.Fixtures.Date),
           DATEPART(MONTH, dbo.Fixtures.Date)
) src
pivot
(
  max(AveragePCRate)
  for YEAR in ([2011], [2012], [2013])
) piv;
票数 3
EN

Stack Overflow用户

发布于 2013-03-26 18:13:30

尝试：

代码语言：javascript
复制
SELECT 
    Month
    ,MAX(CASE WHEN YEAR = 2011 THEN AveragePCRate ELSE Null END) AS Year1
    ,MAX(CASE WHEN YEAR = 2012 THEN AveragePCRate ELSE Null END) AS Year2
    ,MAX(CASE WHEN YEAR = 2013 THEN AveragePCRate ELSE Null END) AS Year3
    ,Region 
FROM
(
SELECT TOP (100) PERCENT DATEPART(YEAR, dbo.Fixtures.Date) AS YEAR,
                         DATEPART(MONTH, dbo.Fixtures.Date) AS MONTH,
                         AVG(dbo.Fixtures.PCRate) AS AveragePCRate,
                         dbo.Region.GroupName AS Region
FROM dbo.Fixtures
INNER JOIN dbo.Vessel ON dbo.Fixtures.VesselId = dbo.Vessel.ID
INNER JOIN dbo.Region ON dbo.Fixtures.RegionId = dbo.Region.ID
WHERE (YEAR(dbo.Fixtures.Date) >= YEAR(DATEADD(YEAR, - 2, GETDATE())))
  AND (dbo.Vessel.TypeId = 1)
  AND (dbo.Vessel.Total_BHP >= 15000)
  AND (dbo.Vessel.Total_BHP < 20000)
GROUP BY dbo.Region.GroupName,
         DATEPART(YEAR, dbo.Fixtures.Date),
         DATEPART(MONTH, dbo.Fixtures.Date)
) YourQuery
GROUP BY Month, Region

您可以通过使CASE语句使用当前日期进行测试(这样一次只有三年)来使它更有吸引力。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/15569400

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档