The problem of connecting merge two tables:
The table name: January
Name score
Zhang SAN 86
Li si 60
Two pock-marked 61
Liu jie 99
The yellow man 80
Tang Zhifeng 89
Zhou Xingming 100


The table name: February
Name score
Zhang SAN 91
Li si 71
Sun Li 90
Two pock-marked 55
Liu jie 79
Tang Zhifeng 53
XinLiQi 74

How to write a statement to connect two tables that

name January and FebruaryThe yellow man 80
Li si 60 71
Liu jie, 99, 79
Sun Li 90
Tang Zhifeng 53 89
Two pock-marked 61 55
XinLiQi 74
Zhang, 86, 91
Zhou Xingming 100

CodePudding user response:

Name, SELECT ISNULL (a. b. name) AS the name,
A. scores AS January, b. scores AS February
FROM January A
FULL JOIN in February ON a. B name=b. name

CodePudding user response:

I can't, the condition should not be a. name=b. name,

CodePudding user response:

Use first UNION ALL put ALL the data together; Then use the PIVOT
; With CTE) in (name, score, AS
(
SELECT name, score, in January '='
FROM January
UINON ALL
SELECT name, score, in February=' '
FROM February
)
SELECT name, January and February
The FROM CTE
PIVOT (Max (score) FOR a month IN ([month], [February])) AS p;

CodePudding user response:

reference Seagull16 reply: 3/f

with UNION ALL first put ALL the data together; Then use the PIVOT
; With CTE) in (name, score, AS
(
SELECT name, score, in January '='
FROM January
UINON ALL
SELECT name, score, in February=' '
FROM February
)
SELECT name, January and February


The FROM CTE
PIVOT (Max (score) FOR a month IN ([month], [February])) AS p;

Can't SQL directly fix?

CodePudding user response:

Man, this is not a CTE SQL: write it again:
; With CTE ([name], [score], [in]) AS
(
SELECT [name], [score], [in]='month'
The FROM [in]
UINON ALL
SELECT [name], [score], in February=' '
The FROM [February]
)

SELECT [name], [in], [February]
The FROM CTE
PIVOT (Max ([score]) FOR [IN] IN [[month], [February])) AS p;

CodePudding user response:

Man, this is not a CTE SQL?

CodePudding user response:

refer to the second floor m0_37611974 response:

I can't, conditions should not be a. name=b. name.

You this two tables associated fields "name", isn't it