| |

VerySource

 Forgot password?
 Register
Search
View: 954|Reply: 8

Seeking SQL summary statement.. please help

[Copy link]

5

Threads

16

Posts

10.00

Credits

Newbie

Rank: 1

Credits
10.00

 China

Post time: 2020-10-19 09:30:01
| Show all posts |Read mode
Table 1
id name
1 a
2 b
3 c


Table 2
idmc name2 name3
1 a1 hg
1 a2 kj
1 a3 fd
1 a4 tr
2 b1 uy
2 b2 s
3 c4 jk
3 c6 ty

The result to be obtained:
1 a
1 a1 hg
1 a2 kj
1 a3 fd
1 a4 tr
2 b
2 b1 uy
2 b2 s
3 c
3 c4 jk
3 c6 ty

please help
Reply

Use magic Report

0

Threads

5

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 China

Post time: 2020-10-19 09:45:01
| Show all posts
Select * From
   (Select id,name as'Name2','' as'Name3' from Table 1
    union all
    Select idmc as'id',name2,name3 from Table 2) a
   order by a.id
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-10-19 10:00:01
| Show all posts
--If the name column can be sorted
select id,name,'' as name2 from table1
union
select idmc,name2,name3 from table2
order by name

--If it cannot be sorted
select id,name,name2 from
(
select id,name,'' as name2,0 as sort from table1
union
select idmc,name2,name3,1 from table2
)tt
order by id,sort
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-10-19 13:00:01
| Show all posts
select id,Name,Name2,Name3' from
(
Select id,name as Name,'' as Name2,'' as Name3,0 as sort from Table 1
union all
Select idmc as id,'' as NAME,name2,name3,1 as sort from Table 2
) t
order by id,sort,Name2
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-10-19 13:15:01
| Show all posts
select id,Name,Name2,Name3 from
(
Select id,name as Name,'' as Name2,'' as Name3,0 as sort from Table 1
union all
Select idmc as id,'' as NAME,name2,name3,1 as sort from Table 2
) t
order by id,sort,Name2

There is one more in front of'
Reply

Use magic Report

0

Threads

17

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

Post time: 2020-10-19 17:30:01
| Show all posts
select * from
(select id,name,'' from table1
  union all
select idmc,name2,name3 from table2

) a
order by id
Reply

Use magic Report

0

Threads

8

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

Post time: 2020-10-19 19:00:01
| Show all posts
select tmp.* from (select id,name as name2,'' as name3
union
select idmc as id,name2,name3
order by name2) tmp
order by tmp.id
Reply

Use magic Report

0

Threads

8

Posts

7.00

Credits

Newbie

Rank: 1

Credits
7.00

 China

Post time: 2020-10-19 19:15:01
| Show all posts
Missed, ha ha
select tmp.* from (select id,name as name2,'' as name3 from table1
union
select idmc as id,name2,name3 from table2
order by name2) tmp
order by tmp.id
Reply

Use magic Report

1

Threads

13

Posts

12.00

Credits

Newbie

Rank: 1

Credits
12.00

 China

Post time: 2020-10-19 19:30:01
| Show all posts
select id,name,name2 from
(
select id,name,'' as name2 as sort from ta1
union
select idmc,name2,name3 from ta2
)tmp
order by id
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list