选择语句格式 [英] Select statement formatting

问题描述

我正在尝试匹配两个表中的数据.一个是发票表，其中包含事物出库和返回的日期.另一个是订单项表，其中包含发票上的每个项目.这两个表的共同点是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.

这篇关于选择语句格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！