有沒有辦法可以將以下查詢縮短為單個查詢？

我有以下查詢。有沒有辦法可以將它放入一個查詢中？如果沒有，我可以進一步縮小它嗎？請指教。

   DECLARE @highRegion TABLE(regionId INT, countR INT)
   DECLARE @lowRegion TABLE(regionId INT, countR INT)
   DECLARE @midRegion TABLE(regionId INT, countR INT)

   INSERT INTO @highRegion
   SELECT c.fRegionID,
          COUNT(1) AS VALUE
   FROM   census.Country c
          INNER JOIN census.IncomeGroup ig
               ON  c.fIncomeGroupID = ig.IncomeGroupID
   WHERE  ig.Name IN ('High income: nonOECD', 'High income: OECD')
   GROUP BY
          c.fRegionID

   INSERT INTO @lowRegion
   SELECT c.fRegionID AS VALUE,
          COUNT(1)
   FROM   census.Country c
          INNER JOIN census.IncomeGroup ig
               ON  c.fIncomeGroupID = ig.IncomeGroupID
   WHERE  ig.Name = 'Upper middle income'
   GROUP BY
          c.fRegionID

   INSERT INTO @midRegion
   SELECT c.fRegionID,
          COUNT(1) AS VALUE
   FROM   census.Country c
          INNER JOIN census.IncomeGroup ig
               ON  c.fIncomeGroupID = ig.IncomeGroupID
   WHERE  ig.Name IN ('Lower middle income', 'Low income')
   GROUP BY
          c.fRegionID

   SELECT r.Name,
          r.NoOfCountries,
          h.countR AS HIGH,
          l.countR AS LOW,
          m.countR AS mid 
   FROM   census.Country c
          INNER JOIN @highRegion h
               ON  c.fRegionID = h.regionId
          INNER JOIN @lowRegion l
               ON c.fRegionID = l.regionId
          INNER JOIN @midRegion m
               ON c.fRegionID = m.regionId
          INNER JOIN census.Region r
               ON  c.fRegionID = r.RegionID

是的，你應該能夠做到這一點，像這樣：

SELECT r.Name,
      r.NoOfCountries,
      sum(case when ig.Name IN ('High income: nonOECD', 'High income: OECD') then 1 else 0 end) AS HIGH,
      sum(case when ig.Name IN ('Lower middle income', 'Low income') then 1 else 0 end) AS LOW,
      sum(case when ig.Name = 'Upper middle income' then 1 else 0 end) AS mid 
FROM   census.Country c
      INNER JOIN census.Region r
           ON  c.fRegionID = r.RegionID
      INNER JOIN census.IncomeGroup ig
           ON  c.fIncomeGroupID = ig.IncomeGroupID
group by 
   r.Name,
   r.NoOfCountries

引用自：https://dba.stackexchange.com/questions/114579