下面看例子:
OOrders表结构是这样的
现在要查询Customer的不同名字分别有多少个,并且要如下显示:
1、于是我想到了用group by,那么使用下面的语句:
select COUNT(Customer) as Customers from oorders GROUP by customer
会发现查出来的结果是这样的:
结果不对。
2、既然是需要显示3列,说明我需要3个结果,于是我改成下面的语句:
select (select COUNT(customer) from oorders where customer='Adams') as Adams,
(select COUNT(Customer) from oorders where customer='Carter') as Carter,
(select COUNT(Customer) from oorders where customer='Bush') as Bush
from oorders
查询出来的结果是这样的:
重复列好多,结果还是不对。
3、有很多重复列,于是我想到了使用distinct消除重复列,最后语句改成这样:
select distinct (select COUNT(customer) from oorders where customer='Adams') as Adams,
(select COUNT(Customer) from oorders where customer='Carter') as Carter,
(select COUNT(Customer) from oorders where customer='Bush') as Bush
from oorders
查询结果:
结果正确。
不知道大神的方法是怎么样的,希望多多指点