Recently, a large-screen display project, similar to the airport, the train station, the kind of display shift information, but the content of the show is more complex, some of the data are as follows:
Front-end mainly with Vue framework, require back-end data in the front-end display, first: Into the factory, the factory indicator is not fixed (may be 6, may also be 7, 8); second: No "value" values are used in place of a slash ; third: In and out of the factory index name must conform to the name specified (back-end database query out the name and the front-end of the specified name is not the same, and the unit with ">1", ">0.3" and the like, so can not directly from the back-end query, name + unit placed in front, need to handle) IV: The order in which the backend data is required to be transmitted must be specified in the front end. Anyway the requirement is that the front end only needs to loop out the data, the back end of all the rest of the demand.
There are two tables: Table_a,table_b
First step: synthesize a table according to Table_a.id=table_b.ids
SQL statement: SELECT * FROM (select * from Table_a a INNER joins Table_b B on A.id=b.ids) A, underline part as a new table A
The second step: found in the database there is no small white dragon and sand enlightenment, need to add:
SQL statement: SELECT * FROM (SELECT * from Table_a a INNER joins Table_b B on A.id=b.ids
UNION All
SELECT 8 ID, ' White Dragon Horse ' name ', ' small white ' sname, ' ' ' age ', 8 IDs, ' value '
UNION All
SELECT 9 ID, ' Sand wukong ' name ', ' General roll ' sname, ' age of 9 ', IDs, 289 ' value '
) A
The dash part is to add some missing data, sometimes for the front-end fixed display needs.
The third step: please follow the Tang priest, Monkey King, pig Eight commandments, sand Wu, small white dragon, spring flowers, Harry, Zhao Liu, Tang seven order
SQL statement: SELECT * FROM (SELECT * from Table_a a INNER joins Table_b B on A.id=b.ids
UNION all
SELECT 8 ID, ' White Dragon Horse ' name ', ' small white ' sname, ' ' "age, 8 IDs, ' value '
UNION all
SELECT 9 ID, ' Sand net ' name ', ' General roll ' sname, ' age ', 9 IDs, 289 ' value ') A
INNER JOIN ( Select 7 IDs, 1 ' order '
union all
select 2 IDs, 2 ' order '
UNION all
SELECT 4 IDs, 3 ' Order '
UNION ALL
SELECT 9 IDs, 4 ' order '
UNION all
SE Lect 8 IDs, 5 ' Order '
UNION ALL
SELECT 6 IDs, 6 ' Order '
UNION ALL
Select 1 IDs, 7 ' Order '
UNION ALL
SELECT 5 IDs, 8 ' Order '
Union All
Select 3 IDs, 9 ' order ' ] B on b.ids=a.id
ORDER by B.order
This type of statement in the underlined section is to add two fields to the entire a table, and then to achieve a fixed sort by the specified way, and also to increase the field to achieve other purposes.
As a rookie level of the novice, this demand I have been on the flop for a day, or under the guidance of others to complete! Tears ran, the road long its repair far.
MySQL query in the specified order of ordering