sql - 需要一个SQL查询来获取基于计数的条件数据

Question

我有名为 areadetail 和 state 的表。

areadetail 有这些列:

State Id
Area Id-rural(1) or urban(2)
Population

示例数据:

Stateid                AreaID                    Population
1                        1                           10
1                        2                           20
1                        2                           20
2                        1                           10
2                        2                           20
3                        1                           10 

State 表有以下列:

State name and state id

示例数据:

State Id                    StateName
1                             Delhi
2                             Mumbai
3                             Jaipur

现在我需要一个查询来显示像这样的记录

State name      Rural               Urban
------------------------------------------
Delhi            3                   2
Mumbai           1                   1
Jaipur           1                   0

Answer 1

我猜 AreaID=1 表示 RURAL 而 2 是 URBAN 然后试试这个查询:

SELECT 
  MAX(s.StateName),
  SUM(CASE WHEN ad.AreaID=1 THEN ad.Population ELSE 0 END) as Rural ,
  SUM(CASE WHEN ad.AreaID=2 THEN ad.Population ELSE 0 END) as Urban


FROM State as s
LEFT JOIN areadetail as ad on s.Stateid=ad.Stateid
GROUP BY s.Stateid

SQLFiddle demo