MySQL uses the Union statement for multi-table join query

I wrote several web pages in PHP and directly used built-in functions to connect to the MySQL database. A practical requirement is that there are several similar tables (including news and announcement articles) that want to make a common query and sorting module for several tables under certain conditions.

For example, dynamically generate a useful sorting list (the latest article, the most popular clicks, etc.) based on the number of clicks of an article and the order of release time ). It is found that the Union of SQL statements can be easily completed without going up to the script level.

The Union statement in standard SQL is as follows:

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

The premise is that the content (table items) of each select statement must be in the same structure. In details, the number of columns in the linked table must be the same and the column attributes must be the same. The column names can be different (with the same structure)

I have the following tables:

News, informs, article, intro

All the items with the same structure are designed. (You do not need to link all the items in the table ):

'Id' int (10) Not null auto_increment, 'title' varchar (100) default null, // Title 'tablenm' varchar (10) default null, // table name, convenient search for 'pageview' int (10) default null, // number of views 'pubtime' timestamp not null default current_timestamp, // release time primary key ('id ')

You can query the connection as follows:

$ Hotnews = mysql_query ("select ID, title, pageview, tablenm from news unionselect ID, title, pageview, tablenm from informsunionselect ID, title, pageview, tablenm from articleunionselect ID, title, pageview, tablenm from introorder by pageview DESC limit 15 ", $ conn); // obtain the first 15 articles sorted by browsing count in four tables

Alternatively, replace the order statement with the following conditions based on the release time:

Order by pubtimew DESC limit $ limit // $ limit can be set to the number to be listed

Note that the structure of a table item must be the same. For example, if one of the two tables has an ID int (10) but cannot be changed to an ID int (9), an error will be returned for the query. However, different names may exist (not recommended). The query result column is output based on the first column name received by the SQL statement.

The entire process is very easy. The powerful SQL method saves you the trouble of raising your work to the script layer. It is no longer done by comparing various complicated strings.

Additionally, the default Union statement only obtains different values in the table. duplicate entries with the same value are ignored. If you want to calculate the same value, the Union all statement is used in the same way.

My use results: