sql-server - 如何运行查询以按邮政编码拆分状态？

Question

我有 3 列，State、zip 和收入。

Select State, SUM(Revenue)
FROM table
group by State

我想拆分以 216,218 和 219 开头的邮政编码为东，其余为西的马里兰州，因此结果将是 51 个州的 MD 东和 MD 西值。

标签： sql-server

Answer 1

Select 
 State,
 CASE WHEN
 -- If it's not MD, then just use the state
    State <> 'MD'
 THEN
    State
 ELSE
    -- It is MD, so work out which sort of MD it is
    CASE WHEN LEFT(Zip,3) IN ('216','218', '219') THEN
        CONCAT(State, ' East')
    ELSE
        CONCAT(State, ' West')
    END
  END, 
  SUM(Revenue)
FROM table
--- Make sure you match grouping for aggregate functions
group by 
    State,
    CASE WHEN LEFT(Zip,3) IN ('216','218', '219') THEN
        CONCAT(State, ' East')
    ELSE
        CONCAT(State, ' West')
    END