问题描述
我正在尝试匹配两个表中的数据.一个是发票表，其中包含事物出库和返回的日期.另一个是订单项表，其中包含发票上的每个项目.这两个表的共同点是invoice_number列.
I'm trying to match up data from two tables. One is an invoices table with the date when a thing goes out and when it comes back in. The other is a line item table with each item on an invoice. Both tables have the column invoice_number in common.
我所做的SELECT语句是:
The SELECT statement I made is:
SELECT line_items.invoice_number,date_out,date_due_in,equipment_qty,line_items.equipment_id FROM invoices join line_items
group by sequence;
一些输出的示例是:
invoice_number, date_out, date_due_in, equipment_qty, equipment_id
1 2017-01-06 2017-01-13 3 2
1 2017-01-06 2017-01-13 2 3
2 2017-01-06 2017-01-13 5 2
3 2017-01-06 2017-01-13 1 2
3 2017-01-06 2017-01-13 1 5
问题在于，它会将第一个项目的日期放在所有项目上.
The issue is that it's putting the date of the first item on all of the items.
以下是从中提取表的示例:
Here is an example of the tables it's pulling from:
发票:
invoice_number, invoice_date, customer_id, equipment_total, date_out, date_due_in
1 2017-01-06 1 5 2017-01-06 2017-01-13
2 2017-01-08 2 4 2017-01-17 2017-01-19
3 2017-01-16 2 2 2017-01-16 2017-01-18
订单项:
Sequence, invoice_number, equipment_id, equipment_qty
1 1 2 3
2 1 3 2
3 2 2 5
4 3 2 1
我在SELECT语句中缺少什么?
What am I missing from the SELECT statement?
推荐答案
使用JOIN时，请始终使用ON或USING (fields, ...)部分/doc/refman/5.7/en/join.html"rel =" nofollow noreferrer>语法.
When using JOIN, always use the ON or USING (fields, ...) part of the syntax.
在这种情况下，两个表中的字段名称都相同，即invoice_number，因此USING (invoice_number)是适当的语法.
In this case the field name in both tables was the same, invoice_number, so USING (invoice_number) was an appropriate syntax.
如果可以使用不同名称的ON invoices.ID = line_item.invoice_number语法.
If they where different names ON invoices.ID = line_item.invoice_number syntax could be used.
可以使用多个条件，以便可以以某些方式使用条件，例如SQL的WHERE条件.
Multiple conditions could be used so the criteria can be used in some ways like the the WHERE criteria of SQL.
这篇关于选择语句格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！