Table query-CodePudding

Table 1

Table 2

City query table 2 provinces of delivery, delivery, reception, provinces, cities and
City of province of table 1 delivery, delivery, receiving provinces, equal data receiving city,

Is directly related the where pick up the four conditions?

CodePudding user response:

From your description, like, probably, it should be, yes...

CodePudding user response:

A table is the delivery list 1, table 2 is what?

CodePudding user response:

If pure associated with these four conditions, the result should be a set of Descartes, the data line many times more than what you want, don't you no main foreign key link to the two tables?

CodePudding user response:

reference weixin_40290083 reply: 3/f

if pure associated with these four conditions, the results should be a set of Descartes, the data line many times more than what you want, don't you these two tables have the main foreign key link,

Related condition also appeared the cartesian product is what the devil?

CodePudding user response:

The primary key of the table must be unique, of course, as the primary key may be a single attribute, it is also possible the combination of several attributes, and your table "reception, provinces, cities provinces, shipment delivery, city" the four attributes to the joint as the primary key is repetitive,
Itself multi-table query is di Carl set is done first, and then filtered according to the where condition, but the relation between the primary key of the where screening is impossible to eliminate duplicate values,

CodePudding user response:

refer to the second floor Dong Xuyang TonyDong response:

1 table is the delivery list, the second table is what?

Table 2 is the area that need to match your idd fields can be ignored, must match out in table 1 at the same time satisfy the table 2 delivery province, city and receiving provinces, the city of the data line

CodePudding user response:

Select table1. Single number, table1. Delivery provinces, table1. Delivery city, table1. Receiving provinces, table1. Receiving city
The from table1 and table2
Where table1. Delivery provinces=table.
2 delivery provincesCity=table2. Delivery and table1. The delivery city
And table1. Receiving provinces=table2. Receiving provinces
And table1. Receiving city=table2. Receiving city

More than 60, ten thousand data in table 1 and table 2 has more than 600, the last to find 7000, distinct order number is 6000, left feeling should be more than, this writing is there a problem?

CodePudding user response:

reference 5 floor weixin_40290083 reply:

the primary key of the table must be unique, of course, as the primary key may be a single attribute, it is also possible the combination of several attributes, and your table "reception, province, city province, shipment delivery, city" the four attributes to the joint as the primary key is repetitive,
Itself multi-table query is di Carl set is done first, and then filtered according to the where condition, but the relation between the primary screening the where it is impossible to eliminate duplicates,

Even the cartesian product have what misunderstanding,
And oracle will not to make the connection, so if any table connection do cartesian product first and then to filter, a large amount of data points, libraries can get points minutes,

CodePudding user response:

refer to 7th floor qq_43101889 response:

select table1. The single number, table1. Delivery provinces, table1. Delivery city, table1. Receiving provinces, table1. Receiving city
The from table1 and table2
Where table1. Delivery provinces=table.
2 delivery provincesCity=table2. Delivery and table1. The delivery city
And table1. Receiving provinces=table2. Receiving provinces
And table1. Receiving city=table2. Receiving city

More than 60, ten thousand data in table 1 and table 2 has more than 600, the last to find 7000, distinct order number is 6000, left feeling should be more than, this writing is there a problem?

Show zero article works, two connection out number of records in the table can be 0 article to the product of two table record number (i.e., the cartesian product)

CodePudding user response:

refer to 6th floor qq_43101889 response:

Quote: refer to the second floor Dong Xuyang TonyDong response:

A table is the delivery list 1, table 2 is what?

Table 2 is the area that need to match your idd fields can be ignored, must match out in table 1 at the same time satisfy the table 2 delivery province, city and receiving provinces, city of data rows

Data query will be a problem, maybe is like this,

CodePudding user response:

references 9 f minsic78 response:

Quote: refer to 7th floor qq_43101889 response:

Select table1. Single number, table1. Delivery provinces, table1. Delivery city, table1. Receiving provinces, table1. Receiving city
The from table1 and table2
Where table1. Delivery provinces=table.
2 delivery provincesCity=table2. Delivery and table1. The delivery city
And table1. Receiving provinces=table2. Receiving provinces
And table1. Receiving city=table2. Receiving city

More than 60, ten thousand data in table 1 and table 2 has more than 600, the last to find 7000, distinct order number is 6000, left feeling should be more than, this writing is there a problem?

Show zero article works, two connection out number of records in the table can be 0 article to the product of two table record number (i.e., the cartesian product)

Table 1 of the province, city field may be missing, such as guangdong province shows that guangdong, according to zhuhai, zhuhai that filter conditions how to write

CodePudding user response:

The deuce, why write to write a test can be sent to,
If table 1 only such problems, the connection conditions can use the substr intercept receiving provinces in table 2 fields of two table 1 again before receiving provinces fields

CodePudding user response:

Won't write a post directly, a wonderful work, stick a picture...

Wrote in the current data of two tables, you describe the should not have performance problems, if the data volume larger variable, it is bad to say, such as two table data volume upside down, as will as the case may be, may need to create a functional index.