Mysql Query：表示子查询返回多于1行的错误

Question

我想从两个表中执行复杂查询......这是一个技能测验的问题。

表

**Orders         Customer**s 
    id           id 
    date          first_name 
    shipping_amount       last_name 
    order_status        city 
    customer_id (Customers.id)    state 

输出

-----------+------------+ 
| State  | # Orders | 
+-----------+------------+ 
| NY  | 55  | 
| CA  | 40  | 
| NJ  | 33  | 
| FL  | 21  | 
| MO  | 12  | 
+-----------+------------+ 

我一直在我的查询，它看起来像这样...

select DISTINCT state, (select count(id) Orders 
         from customers 
         group by state 
         ORDER BY Orders DESC) FROM Customers 

它给了我，说子查询的错误返回多于一行

Answer 1

试试这个：

SELECT c.state, COUNT(o.id) AS Orders 
FROM Customers c, Orders o 
WHERE o.customer_id = c.id 
GROUP BY state 
ORDER BY Orders DESC 

子查询是没有必要的。

Answer 2

SELECT `Customers`.`state`, count(`Orders`.`id`) 
as `orders FROM `Customers` 
LEFT JOIN `Orders` ON `Customers`.`id` = `Orders`.`customer_id` 
GROUP BY `Customers`.`state` 
ORDER BY `orders` DESC 

Answer 3

试试这个：

select DISTINCT state, (select count(id) as cnt, Orders from customers group by 
    state ORDER BY Orders DESC) Temp FROM Customers 

Answer 4

SELECT c.state as State, COUNT(o.id) as NumOrders 
FROM orders o 
LEFT JOIN customers c ON (c.id = o.customer_id) 
GROUP BY c.state