如何避免子查询产生多个结果 [英] How to avoid multiple results from sub query

问题描述

大家好，我是 MySQL 的新手，这可能是

Hi guys i am new bie to MySQL,this might be

更容易提问，但我对 mysql 完全陌生.

easier to question but i am totally new for mysql.

我有两张桌子来订购和购买两张的 Desc表看起来像这样....

i have two tables order and shops the Desc of two tables look like this....

订单表.

order id: 
ordername:
shopnum(fk)

商店*

shopname:
shopnum(pk):

我正在使用这样的子查询，以获取订单数量最多的商店名称....喜欢...19 xyzshop.13 hjjddshop.6 锐步店

i am using sub query like this,to get the shops name which have most number of orders.... like...19 xyzshop . 13 hjjddshop . 6 reebok shop

select shopname 
from shopstable 
where shopnum in
    (select count(orderid) as highest ,shopnum 
     from orderTable 
     group by shopnum)

它抛出错误，显示列应该是 1，因为子查询返回 2 个结果...所以我如何避免这种情况并获得适当的结果...帮助将不胜感激...:):)

it throws error,display column should be 1,its because the subquery is returning 2 results...so how do i avoid that and get the appropriate result...help will be appreciated...:):)

推荐答案

它没有抱怨，因为子查询返回 2 个结果但有两列.但即使它只返回一列，它也会返回 2 个结果，主查询也会这样做.

It's not complaining because the subquery returns 2 results but two columns. But even if it did only return a single column, it would return 2 results and the main query would do the same.

在任何情况下都不需要子查询:

No need for a subquery in any case:

SELECT s.shopname 
FROM Shopstable s 
JOIN OrdersTable o ON s.shopnum=o.shopnum 
GROUP BY s.shopname 
ORDER BY count(*) DESC 
LIMIT 1

这篇关于如何避免子查询产生多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！