需要单个 SQL 查询[关闭]

我有两个表如下

companyid name      
1         pwc       
2         dell      
3         microsoft 
4         google    
5         yahoo     
6         twitter   

companyid  state        month   powerconsumption

1          newyork       jan    240
2          california    jan    130    
3          arizona       jan    210
4          texas         jan    130
5          texas         jan    650
6          california    jan    310
2          arizona       jan    340

我想要查询列出每个州一月份的用电量（从最高到最低）的公司。因此，上述数据的结果将是

arizona    dell      340
arizona    microsoft 210        
california twitter   310
california dell      130
newyork    pwc       240
texas      yahoo     650
texas      google    130

尝试这个：

SELECT t2.state, t1.name,t2.powerconsumption
FROM table1 t1
INNER JOIN table2 t2 ON t1.companyId = t2.companyId
WHERE t2.month = 'jan'
ORDER BY t2.state ASC, t2.powerconsumption DESC;

sqlfiddle demo http://sqlfiddle.com/#!2/0709a/1

这将连接两个表，然后首先按状态升序排序，然后按功耗降序排序。这意味着它将针对每个州按降序排序。