SQL statement Special syntax handling for duplicate fields of multiple-table union queries

SQL statement Special syntax handling for duplicate fields of multiple-table union queriesoriginal January 13, 2016 14:22:36

• 5309

[SQL]View PlainCopy

1. SELECT M.*,d_name,r_name,m2.m_account as shangji_account from Member m,member m2,role r,depart d WHERE M.m_ri D=r.id and m.m_did=d.id and m.m_shangji=m2.id ORDER by m.id DESC

In general, we generally have a field with an automatic ID increment in each table. And we need to use all the contents of the M table, except that it has some fields associated with the other table ID need to be restored to the corresponding column, that is, the chain.

It is cumbersome to get results if the query is federated. One way to do this is to use the data to be used in a select after the list. But this is very troublesome, today found a good way, is to use the above. m.* represents all the fields of the M table, and then the other tables do not need to use the ID, and the other tables only take specific fields, so there is no problem with ID duplication. Perfect solution.

To give a chestnut, such as a record of the student ID, if the field is called "Student ID", but the table directly shows the ID does not know who, need to display the student's name, then use this method:

Select Score table. *, student table. Name from the score table, student table where score table. Student id= student table. ID

This is convenient because with the SELECT * from the score table, the student table where the score table. Student id= Student table. ID This is not good for the results get ID. The system defaults to the ID of the next table, and if you want to do so, you can change the order:

SELECT * FROM student table, score table where score table. Student id= student table. ID

This is very convenient, so with the RS ("id") or the score table ID, can also explain the pass, estimated that his principle is stored in one place, the same field, if you encounter the same fields, such as the subsequent ID, then overwrite the table in the following will prevail.

However, if more than the words are inconvenient, the order will be accidentally mistaken. And the following must not be the case, you have to use the table above. * The way it is.

For example, the student table has: ID, name, age and so on, such as there is a field called Help object, also record the ID, is also the ID of this table, assuming that the field is called "Help Object ID", then to list the student tables and display the name of the following statement:

Select A.*,b. Name as helper name from student Table A, student table B wherea. Help Object Id=b.id

So the list is:

ID Age Name Help object name (this column can turn the ID into a name to display it) RS ("helper name")

======================

1 16 Three small sheets

2 15 Little Zhang Xiaogang

3 16 Xiao Zhang

。

。

。

Student table List (example)

ID	Age	name	Help Object name (this column can turn the ID into a name to show)
1	16	Tom	Xiao Zhang
2	17	Xiao Zhang	Xiao Gang
3	16	Xiao Gang	Tom
。
。

perfect!

SQL statement Special syntax handling for duplicate fields of multiple-table union queries