1使用子查询
列出其销售目标超过各位销售人员定额总和的销售点
mysql> SELECT CITY FROM OFFICES WHERE TARGET>(SELECT SUM(QUOTA) FROM SALESREPS WHERE REP_OFFICE=OFFICE);
+-------------+
| CITY |
+-------------+
| Chicage |
| Los Angeles |
+-------------+
SQL子查询一般作为WHERE和HAVING子句中的一部分出现。在WHERE子句中，他们帮助选择在查询结果中显示的各条记录。在HAVING子句中，他们帮助选择在查询结果中显示的记录组。
列出其定额小于全公司销售目标10%的销售人员。
mysql> SELECT NAME FROM SALESREPS WHERE QUOTA<(.1*(SELECT SUM(TARGET) FROM OFFICES));
+-----------+
| NAME |
+-----------+
| Sam Clark |
| Bob Smith |
2子查询比较测试(=,<>,<,<=,>,>=)
列出其销售定额等于或高于Atlanta销售点的销售目标的销售人员。
mysql> SELECT NAME FROM SALESREPS WHERE QUOTA>=(SELECT TARGET FROM OFFICES WHERE CITY='Atlanta');
+-------------+
| NAME |
+-------------+
| Bill Adams |
| Sue Smith |
列出制造商ACI的所有产品中库存数量高于产品ACI-41004的库存数量产品。
mysql> SELECT DESCRIPTION,QTY_ON_HAND FROM PRODUCTS WHERE QTY_ON_HAND>(SELECT QTY_ON_HAND FROM PRODUCTS WHERE MFR_ID='ACI' AND PRODUCT_ID='41004') AND MFR_ID='ACI';
+---------------+-------------+
| DESCRIPTION | QTY_ON_HAND |
+---------------+-------------+
| Size 1 Widget | 277 |
| Size 2 Widget | 167 |
| Size 3 Widget | 207 |
+---------------+-------------+
注意比较表达式不能位(subquery)>A;
3组成员测试(in)
列出其销售额超过销售目标的销售点的销售人员
mysql> SELECT NAME FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE SALES>TARGET);
+-------------+
| NAME |
+-------------+
| Bill Adams |
| Mary Jones |
列出在2008年1月和6月之间订购的ACI部件（制造商ACI，产品号从4100开始）的所有客户。
mysql> SELECT COMPANY FROM CUSTOMERS WHERE CUST_NUM IN (SELECT DISTINCT CUST FROM ORDERS WHERE MFR='ACI' AND PRODUCT LIKE '4100%' AND ORDER_DATE BETWEEN '2008-01-01' AND '2008-06-30');
+-------------------+
| COMPANY |
+-------------------+
| JCP Inc. |
| Acme Mfg. |
4存在测试（EXISTS）
存在测试返回的是true和false，与in测试不通的是，in测试返回的是一组数据集，存在测试比in测试更有效。
列出取得的订单等于或大于25000美元的产品
mysql> SELECT DESCRIPTION FROM PRODUCTS WHERE EXISTS (SELECT * FROM ORDERS WHERE PRODUCT=PRODUCT_ID AND MFR=MFR_ID AND AMOUNT>=25000.00);
+----------------+
| DESCRIPTION |
+----------------+
| Widget Remover |
| Left Hinge |
mysql> SELECT COMPANY FROM CUSTOMERS WHERE CUST_REP=(SELECT EMPL_NUM FROM SALESREPS WHERE NAME='Sue Smith') AND NOT EXISTS (SELECT * FROM ORDERS WHERE CUST=CUST_NUM AND AMOUNT>3000);
+------------------+
| COMPANY |
+------------------+
| Carter & Sons |
| Fred Lewis Corp. |
+------------------+
列出Sue Smith的某些客户，这些客户未订购超过3000美元的订单。
列出其定额超过整个销售点销售目标的55%的销售人员所在的销售点
mysql> SELECT CITY FROM OFFICES WHERE EXISTS (SELECT * FROM SALESREPS WHERE QUOTA>(.55*TARGET) AND REP_OFFICE=OFFICE);
+---------+
| CITY |
+---------+
| Denver |
| Atlanta |
+---------+
5限定测试（any和all）
ANY测试和6个SQL运算比较符（=，<>,>=,>,<=,<)一起使用，用于把一个测试值和由于查询产生的一个字段的数据值比较。
列出取得一个超过其销售目标10%的订单的销售人员。（只要这个销售人员有一张订单超过了销售目标的10%就为true）
mysql> SELECT NAME FROM SALESREPS WHERE .1*QUOTA< ANY(SELECT AMOUNT FROM ORDERS WHERE REP=EMPL_NUM);
+---------------+
| NAME |
+---------------+
| Sam Clark |
| Larry Fitch |
| Nancy Angelli |
+---------------+
列出销售人员中不管理销售点的所有人员的名字和年龄
mysql> SELECT NAME,AGE FROM SALESREPS WHERE NOT (EMPL_NUM=ANY (SELECT MGR FROM OFFICES));
+---------------+------+
| NAME | AGE |
+---------------+------+
| Mary Jones | 31 |
| Sue Smith | 48 |
| Dan Roberts | 45 |
任何一个ANY测试转换成EXSITS测试。
mysql> SELECT NAME,AGE FROM SALESREPS WHERE NOT EXISTS (SELECT * FROM OFFICES WHERE EMPL_NUM=MGR);
+---------------+------+
| NAME | AGE |
+---------------+------+
| Mary Jones | 31 |
| Sue Smith | 48 |
| Dan Roberts | 45 |
6 ALL测试
列出其所有销售人员的销售量超过其销售目标的50%的销售点和它们的销售目标。（对于每一个销售点来说，这个销售点的销售人员的总销售量都超过其销售目标的50%）
mysql> SELECT CITY,TARGET FROM OFFICES WHERE (.5*TARGET) < ALL (SELECT SALES FROM SALESREPS WHERE REP_OFFICE =OFFICE);
+----------+-----------+
| CITY | TARGET |
+----------+-----------+
| Denver | 300000.00 |
| New York | 575000.00 |
7子查询和连接
列出西部地区销售点工作的销售人员的名字和年龄
mysql> SELECT NAME,AGE FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION='WESTERN');
mysql> SELECT NAME,AGE FROM SALESREPS,OFFICES WHERE REP_OFFICE=OFFICE AND REGION='WESTERN';
mysql> SELECT NAME,AGE FROM SALESREPS WHERE EXISTS (SELECT * FROM OFFICES WHERE REGION='WESTERN' AND REP_OFFICE=OFFICE);
+---------------+------+
| NAME | AGE |
+---------------+------+
| Sue Smith | 48 |
| Larry Fitch | 62 |
| Nancy Angelli | 49 |
+---------------+------+
8嵌套子查询
列出东部地区销售点工作的销售人员取得的客户。
mysql> SELECT COMPANY FROM CUSTOMERS WHERE CUST_REP IN (SELECT EMPL_NUM FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION='EASTERN'));
+------------------+
| COMPANY |
+------------------+
| JCP Inc. |
| First Corp. |
| Acme Mfg. |
| Smithson Corp. |
9关联子查询
列出其销售量在平均销售目标之下的销售点。
mysql> SELECT CITY FROM OFFICES WHERE SALES <(SELECT AVG(TARGET) FROM OFFICES);
+---------+
| CITY |
+---------+
| Denver |
| Atlanta |
+---------+
10 HAVING子句中的子查询
列出对于ACI生产的产品，其取得的平均订单大小超过了总的平均订单大小的销售人员
mysql> SELECT NAME,AVG(AMOUNT) FROM SALESREPS,ORDERS WHERE EMPL_NUM=REP AND MFR='ACI' GROUP BY NAME HAVING AVG(AMOUNT) > (SELECT AVG(AMOUNT) FROM ORDERS);
+------------+--------------+
| NAME | AVG(AMOUNT) |
+------------+--------------+
| Bill Adams | 9656.250000 |
| Tom Snyder | 22500.000000 |
+------------+--------------+
列出对于ACI生产的产品，其取得的平均订单大小至少与总平均订单大小一样大的销售人员。
mysql> SELECT NAME,AVG(AMOUNT) FROM SALESREPS,ORDERS WHERE EMPL_NUM=REP AND MFR='ACI' GROUP BY NAME,EMPL_NUM HAVING AVG(AMOUNT) >= (SELECT AVG(AMOUNT) FROM ORDERS WHERE REP=EMPL_NUM);
+------------+--------------+
| NAME | AVG(AMOUNT) |
+------------+--------------+
| Bill Adams | 9656.250000 |
| Tom Snyder | 22500.000000 |
+------------+--------------+
11 CASE的用法
mysql> SELECT COMPANY,
-> CASE WHEN CREDIT_LIMIT >60000 THEN 'A'
-> WHEN CREDIT_LIMIT >30000 THEN 'B'
-> ELSE 'C'
-> END AS CREDIT_RATING
-> FROM CUSTOMERS;
我们想要找到按销售点计的销售人员销售量的总和。如果一名销售人员还未分配到一个销售点去，那么此人应该包括在他的经理的销售点的总和中。
mysql> SELECT CITY,SUM(SALESREPS.SALES) FROM OFFICES,SALESREPS WHERE OFFICE=CASE WHEN(REP_OFFICE IS NOT NULL) THEN REP_OFFICE ELSE (SELECT REP_OFFICE FROM SALESREPS MGRS WHERE MGRS.EMPL_NUM=MANAGER) END GROUP BY CITY;
+-------------+----------------------+
| CITY | SUM(SALESREPS.SALES) |
+-------------+----------------------+
| Atlanta | 367911.00 |
| Chicage | 734997.00 |
| Denver | 186042.00 |
12COALESCE的用法（处理NULL值，可以让NULL值以某个值来显示）
mysql> select NAME,COALESCE(QUOTA,SALES,0.00) FROM SALESREPS;
+---------------+----------------------------+
| NAME | COALESCE(QUOTA,SALES,0.00) |
+---------------+----------------------------+
| Bill Adams | 350000.00 |
| Mary Jones | 300000.00 |
如果QUOTA的值是空，则用SALES中的值代替这个空值，如果SALES中的值也是空，则用0.00代替。