主页 / dba / 问题 / 216029
Accepted
Learning_Learning
Asked: 2018-08-29 00:16:30 +0800 CST

在 Group by StateID 中需要一些帮助 - TSQL 查询

  • 772

任何人都可以在以下情况下帮助我我需要按州 ID 分组的小帮助

下面是我的查询，我试图将所有输出放在一行中。

但我每年都会收到不同的线路。请帮助我在单行中获取所有输出，因为州 ID 相同。

SELECT  StateID,
SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
(Select count(Fileid) where year = '2016' ) AS TotalFs2016,
SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
(Select count(Fileid) where year = '2017' ) AS TotalFs2017,
SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
(Select count(Fileid) where year = '2018' ) AS TotalFs2018,
SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
(Select count(Fileid) where year = '2019' ) AS TotalFs2019
--FileState
FROM
(
SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
FROM #Temp1
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp2
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp3
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp4
)t 
where StateID like '570' 
GROUP BY 
t.StateID , year

当前输出为不同年份提供 4 行，例如 2016 年、2017 年、2018 年、2019 年以及文件的大小。

由于 ID 相同，我只想在哪里获得所有年份数据的一行。

我试图 GROUP BY 但仍然无济于事。

感谢您的帮助。

sql-server t-sql

1 个回答

  1. Best Answer

    不需要分组，因为您只选择一个 stateid

    SELECT  StateID,
  SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
  count(case when year = 2016 then Fileid end ) AS TotalFs2016,
  SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
  count(case when year = 2017 then Fileid end ) AS TotalFs2017,
  SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
  count(case when year = 2018 then Fileid end ) AS TotalFs2018,
  SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
  count(case when year = 2018 then Fileid end ) AS TotalFs2019
--FileState
FROM
(
  SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
  FROM #Temp1
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp2
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp3
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp4
)t
where t.StateID like '570'
    • 1

相关问题