Have a, b two tables (see below), table b left a table, want to get the results of c style, tried to use the select a.x x, b.x x from a left join b on a.x x=b.x x such statements, but the result still is not correct, and does not produce the cartesian product of right!

CodePudding user response:

According to the normal way is to select * from course information form a left join result information table b on Amy polumbo course number=b. K K number
The order by b. name, serial number Amy polumbo K course, so there is no said display a student achievement is null

CodePudding user response:

With TB (course number, course name) as
(
Select 1, 'shuxue' union all
Select 2, 'yuwen' union all
Select 3, 'wuli' union all
Select 4, "huaxue"
)
, tb2 (serial number, name, course number, results) as
(
Select 1, 'zs, 1, 92 union all
Select 2, zs, 2, 95 union all
Select 3, 'zs, 3, 80 union all
Select 4, 'ls', 3, 93 union all
Select 5, 'ls', 4, 85,
)
, tmp1 as
(
Select distinct names from tb2
)
Select tmp1. Name, TB. *, tb2. Result from TB
The join tmp1 on 1=1
Left outer join tb2 on tb2. Name=tmp1. Name and tb2. Course number=TB. The course number

CodePudding user response:

The cartesian product possible produce m * n results, not must produce m * n results,

CodePudding user response:

 
Course name, SELECT name, b. a. c. achievement 
The FROM TABLE_A 
The JOIN (SELECT DISTINCT names FROM TABLE_B) AS B ON 1=1 
JOIN TABLE_B AS C ON a. course number=c. course number AND name=c. b. name 

CodePudding user response:

The SELECT a.s tudent_name, a.c lass_name, Arthur c. ore
The FROM class_table a
The JOIN (SELECT DISTINCT student_name FROM student_core) on 1=1 b
Left the JOIN student_core c ON Anderson, d=Arthur c. lass_id and b.s tudent_name c.s. tudent_name=
The order by b.s tudent_name