MySQL one-time query two table data returned automatically merged into a table data-CodePudding

Table 1
Id mingzi neirong shijian
1 1 a: I'm Chinese 1990-01-01
2 I is the people the 1990-01-01
3 3 a: I'm Chinese 1990-01-01
4, 4 I am a Chinese 1990-01-01

Table 2
Id mingzi neirong shijian
6 I am a Chinese 1990-01-01
17 I am a Chinese 1990-01-01
3 I is the Chinese 1990-01-01
4 I Japanese nationality 1990-01-01

Two table structure ID is the same growth since

How to use SQL statements a query two tables neirong fuzzy exist China two word % %

The resulting data should be

Table 3
Id mingzi neirong shijian
1 1 a: I'm Chinese 1990-01-01
2, 3, I am a Chinese 1990-01-01
3, 4, I am a Chinese 1990-01-01
4 6 I am a Chinese 1990-01-01
5 7 I am a Chinese 1990-01-01

thank you

CodePudding user response:

No one is the big night

CodePudding user response:

 DECLARE @ TABLE 1 TABLE (id INT IDENTITY NOT NULL, mingzi INT the NOT NULL, neirong NVARCHAR (100) NOT NULL, shijian DATE NOT NULL) 
INSERT @ (table 1 mingzi, neirong, shijian) 
VALUES (1, 'I am a Chinese', '1990-01-01'), (2, 'I is the people', '1990-01-01'), (3, 'I am a Chinese,' 1990-01-01 '), (4, 'I am a Chinese,' 1990-01-01 ') 

DECLARE @ TABLE 2 TABLE (id INT IDENTITY NOT NULL, mingzi INT the NOT NULL, neirong NVARCHAR (100) NOT NULL, shijian DATE NOT NULL) 
INSERT @ table 2 (mingzi, neirong, shijian) 
VALUES (6, 'I am a Chinese', '1990-01-01'), (7, 'I am a Chinese', '1990-01-01'), (9, 'I am the American people', '1990-01-01'), (11, 'I'm Japanese americans',' 1990-01-01 ') 

The SELECT ROW_NUMBER () OVER (ORDER BY mingzi) id, t.m ingzi, t.n eirong, t.s hijian FROM (
SELECT * FROM table 1 @ the WHERE neirong LIKE '% % of China' 
UNION ALL SELECT * FROM @ table 2 where neirong LIKE '% % of China) t 
The ORDER BY t.m ingzi

CodePudding user response:

Share data table merging method

CodePudding user response:

The SELECT ROW_NUMBER () OVER (ORDER BY mingzi) id, *
The FROM (SELECT * FROM table 1 WHERE neirong LIKE '% % of China'
UNION ALL
SELECT * FROM table 2 where neirong LIKE '% % of China) t

CodePudding user response:

Using UNION query,
Select two tables (including like query conditions), as a result of the query result field names are the same, so, should pay attention to the position before and after the same
Then, in between the two Select statements, keyword, add the UNION (if you have any punctuation marks in the middle, will remove the punctuation)
Finally, the overall perform this long statement can,