时间戳

首页 > 解决方案 > 如何执行内连接

mysql - 如何执行内连接

问题描述

我是 SQL 新手。我有两张桌子代理，订单。问题如下：

“显示在 2008 年 8 月 1 日至 2008 年 9 月 23 日期间在纽约、多伦多或孟买任何一个城市交付订单数量超过 1000 件的代理商的姓名和电话号码”。

代理

+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| agent_code   | varchar(6)    | NO   | PRI | NULL    |       |
| agent_name   | varchar(40)   | YES  |     | NULL    |       |
| working_area | varchar(35)   | YES  |     | NULL    |       |
| commission   | decimal(10,2) | YES  |     | NULL    |       |
| phone_no     | varchar(15)   | YES  |     | NULL    |       |
| country      | varchar(25)   | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+

示例行：

+------------+------------+--------------+------------+--------------+---------+
| agent_code | agent_name | working_area | commission | phone_no     | country |
+------------+------------+--------------+------------+--------------+---------+
| A001       | Subbarao   | Bangalore    |       0.14 | 077-12346674 |   N/A   |
+------------+------------+--------------+------------+--------------+---------+

订单

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| ord_num         | decimal(6,0)  | NO   | PRI | NULL    |       |
| ord_amount      | decimal(12,2) | NO   |     | NULL    |       |
| advance_amount  | decimal(12,2) | NO   |     | NULL    |       |
| ord_date        | date          | NO   |     | NULL    |       |
| cust_code       | varchar(6)    | NO   |     | NULL    |       |
| agent_code      | varchar(6)    | NO   |     | NULL    |       |
| ord_description | varchar(60)   | NO   |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+

示例行：

+---------+------------+----------------+------------+-----------+------------+-----------------+
| ord_num | ord_amount | advance_amount | ord_date   | cust_code | agent_code | ord_description |
+---------+------------+----------------+------------+-----------+------------+-----------------+
|  200100 |    1000.00 |         600.00 | 2008-08-01 | c00013    | a003       | sod             |
+---------+------------+----------------+------------+-----------+------------+-----------------+

这是我尝试过的：

select agent_name, phone_no from agents inner join orders on agents.agent_code=orders.agent_code where working_area="New York" and ord_date>2008-08-01 and ord_date<2008-09-23 and ord_amt>800;

但是我在加入时遇到了错误。请帮我。

错误：1054（42S22）：“on 子句”中的未知列“agents.agent_code”

标签： mysqljoin

解决方案

您需要在日期和 varchar 值周围加上单引号

SELECT agents.agent_name, agents.phone_no, orders.ord_num, orders.ord_amount
FROM agents 
INNER JOIN orders 
    ON agents.agent_code = orders.agent_code 
WHERE
    rd_amt>800
    AND working_area='New York'
    AND ord_date BETWEEN '2008-08-01' AND '2008-09-23';

编辑

再解释一下，在编写查询时，您需要将单引号'和varchar值放在一起，date以便可以解释它们。另外BETWEEN写大于和小于的速记方式例如

ord_date BETWEEN '2008-08-01' AND '2008-09-23'

在功能上与

ord_date >= '2008-08-01' AND ord_date <= '2008-09-23'

推荐阅读