在SQL Server的同一列中列出MAX和MIN

Question

我试图将贫困率最高的县和贫困率最低的县列在各自所属的州旁边的同一列下。如下所示：

+----------+--------+--------------+
| State    | County | Poverty Rate |
+----------+--------+--------------+
| Delaware | AB     | 0.1234       |
+----------+--------+--------------+
| Delaware | CD     | 0.6789       |
+----------+--------+--------------+
| Virginia | EF     | 0.1357       |
+----------+--------+--------------+
| Virginia | GH     | 0.4680       |
+----------+--------+--------------+

但我只显示了三列:状态、最高贫困率、最低贫困率，代码如下：

;WITH poverty_CTE AS (
    SELECT
        state
        ,County
        ,AVG(pctPoor)    AS 'Poverty'
    FROM [SQLBook].[dbo].[ZipCensus]
    WHERE County IS NOT NULL
    GROUP BY County, state
)
SELECT
    CASE state
        WHEN '10' THEN 'Delaware'
        WHEN '51' THEN 'Virginia'
        ELSE 'Others'
    END                     AS State
    ,MAX(Poverty)           AS 'Highest Poverty Rate'
    ,MIN(Poverty)           AS 'Lowest Poverty Rate'
FROM poverty_CTE
WHERE state IN (10, 51)
GROUP BY State
ORDER BY State

这就是结果：

+------------+----------------------+---------------------+
| State      | Highest Poverty Rate | Lowest Poverty Rate |
+------------+----------------------+---------------------+
| Delaware   | 0.6789               | 0.1234              |
+------------+----------------------+---------------------+
| Washington | 0.4680               | 0.1357              |
+------------+----------------------+---------------------+

有没有可能在不复杂的子查询的情况下实现我想要的结果？

谢谢你的帮助!

Answer 1

这是你问题的答案。使用子查询：

create table dbo.ZipCensus (
  State int,
  County varchar(20),
  pctPoor float
);

insert dbo.ZipCensus (State, County, pctPoor) values
  (10, 'AB', 0.1234),
  (10, 'CD', 0.6789),
  (51, 'EF', 0.1357),
  (51, 'GH', 0.4680);

select State, County, [Poverty Rate]
from (
  select
    S.State,
    County,
    [Poverty Rate],
    LeastPoor = row_number() over (partition by P.State order by [Poverty Rate] asc, County asc),
    MostPoor = row_number() over (partition by P.State order by [Poverty Rate] desc, County asc)
  from (
    select State, County, avg(pctPoor) as [Poverty Rate]
    from dbo.ZipCensus
    where State in (10, 51)
    group by State, County
  ) P
  join (values
    (10, 'Delaware'),
    (51, 'Virginia')
  ) S (StateID, State) on S.StateID=P.State
) R
where LeastPoor=1 or MostPoor=1
order by State, [Poverty Rate]

这将产生以下结果：

State   County  Poverty Rate
Delaware    AB  0.1234
Delaware    CD  0.6789
Virginia    EF  0.1357
Virginia    GH  0.468

Answer 2

使用窗口函数：

SELECT state_name, county, poverty_rate
FROM (SELECT (CASE state WHEN '10' THEN 'Delaware' WHEN '51' THEN 'Virginia' END) as state_name,
             County, AVG(pctPoor) as poverty_rate,
             ROW_NUMBER() OVER (PARTITION BY state ORDER BY AVG(pctPoor) ASC) as seqnum_asc,
             ROW_NUMBER() OVER (PARTITION BY state ORDER BY AVG(pctPoor) DESC) as seqnum_desc
      FROM [SQLBook].[dbo].[ZipCensus]
      WHERE County IS NOT NULL AND state IN ('10', '51')
      GROUP BY County, state
     ) s
WHERE 1 IN (seqnum_asc, seqnum_desc)
ORDER BY state_name, poverty_rate;

注意，子查询做了很多工作--不需要过多的CTE和子查询：

• 它将代码替换为州名称。
• 它在汇总之前筛选所需的两个州的数据(效率更高)。
• 它分配序号以标识最好和最差的县。

您可能应该使用引用表来查找州名称。