mysql group by笔试题_[PBN笔试题]mysql 一个简单查询中的group by 和 order by 的使用

具体我忘记了，大概内容差不多如下。

两张表，内容如下：

+--------+-------+------+

| z_code | mgr | pigs |

+--------+-------+------+

| 021 | zhang | 85 |

| 010 | wang | 70 |

+--------+-------+------+

2 rows in set

查询最终结果

+--------+------+-------+

| z_code | city | mgr |

+--------+------+-------+

| 010 | BJ | wang |

| 021 | HB | zhang |

+--------+------+-------+

2 rows in set

A表

+------+-------+------+

| part | mgr | pigs |

+------+-------+------+

| 001 | wang | 20 |

| 002 | wang | 50 |

| 011 | zhang | 49 |

| 012 | zhang | 36 |

+------+-------+------+

4 rows in set

B表

所用查询语句为：

select z_code, A.mgr mgr, sum(pigs) pigs from A, B where A.mgr = B.mgr group by A.mgr order by pigs desc;

下面是建表和插入语句的SQL：

create database pig_store;

use pig_store;

create table A(

z_code varchar(8),

city varchar(4),

mgr varchar(16)

);

create table B(

part varchar(8),

mgr varchar(16),

pigs int(5)

);

insert into A values ( '010', 'BJ', 'wang'), ( '021', 'HB', 'zhang' );

insert into B values ( '001', 'wang', 20 ), ( '002', 'wang', 50 ), ( '011', 'zhang', 49 ), ( '012', 'zhang', 36 );