I have two tables, data structure is as follows:

Table_a

Xh a1, a2, a3 a4
1, 123, 231, 123, 123
2, 234, 123, 345, 754
3, 122, 787, 534, 435

Table_b

Xh a1 a2 b3 b4
1, 123, 231, 444, 555
2, 234, 123, 666, 777
3, 122, 787, 888, 999

Note: table_a and table_b xh associated

Finally want to merge into a

Xh a1, a2, a3 a4 b3 b4
1, 123, 231, 123, 123, 444, 555
2, 234, 123, 345, 754, 666, 777
3, 122, 787, 534, 435, 888, 999

CodePudding user response:

The JOIN directly ON TALBE_A. XH=TABLE_B. XH

CodePudding user response:

That will be two tables for two select statements are as follows:

SELECT a. *, b. * FROM
(
SELECT
ANY_VALUE (s.X glasfischer glastechnik) 'item number',
ANY_VALUE s.X (MMC) 'project name,
ANY_VALUE y.Y (HMC) 'project manager',
CASE ANY_VALUE modes (FBM)
WHEN the '1523870758790016748544' THEN 'environmental monitoring product a'
WHEN '1523870788026016379904' THEN 'environmental monitoring product department 2'
WHEN the '1523870852987015314944' THEN 'environmental monitoring products'
WHEN the '1523870658221015200256' THEN 'environmental monitoring products'
WHEN the '1523870691421015577088' THEN 'environment map products'
WHEN the '1523870997678016932864' THEN 'environmental monitoring products division'
WHEN the '1523870934234015314944' THEN 'Beijing'
WHEN the '1523870963855015314944' THEN 'Shanghai institutes'
The ELSE 'unknown departments'
END
'delivery department,
ANY_VALUE (m.R JJE), amount of 'software',
The SUM (s.G ZSJ)/8 'in the first quarter of the actual people,
Concat (round ((SUM (s.G ZSJ)/8) * 700/ANY_VALUE (m.R JJE)), '%') 'working hours accounted for in the first quarter,
In CASE the WHEN
S.X glasfischer glastechnik in
(
The SELECT m.Y XMBH from t_xmgl_jfgl_project_main m
Where m.X MZT='2'
OR
(
(
M.X MZT='3'
AND quarter (m.Y SHSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (m.Y SHSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
OR
(
M.X MZT='3'
AND m.Y SHSJ=NULL
AND quarter (Margaret spellings JYSSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (Margaret spellings JYSSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
)
OR
(
(
M.X MZT='4'
AND quarter (m.Y SHSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (m.Y SHSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
OR
(
M.X MZT='4'
AND m.Y SHSJ=NULL
AND quarter (Margaret spellings JYSSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (Margaret spellings JYSSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
)
OR
(
(
M.X MZT='5'
AND quarter (m.Y SHSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (m.Y SHSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
OR
(
M.X MZT='5'
AND m.Y SHSJ=NULL
AND quarter (Margaret spellings JYSSJ)=quarter (DATE_SUB (now (), the interval 1 quarter))
AND year (Margaret spellings JYSSJ)=year (DATE_SUB (now (), the INTERVAL 1 QUARTER))
)
)
)
THEN the 'is'
The ELSE 'or'
To examine the END 'whether'

The FROM t_erp_oa_gzrz_ssjl s
Left the join t_xmgl_jfgl_project_main m ON s.X glasfischer glastechnik=m.Y XMBH
Left the join t_admin_rms_yh y
ON m.X MJL=y.Y HID
WHERE quarter (s.R ZSJ)
Not in
(s.R ZSJ>=DATE_FORMAT (DATE_SUB (NOW (), the INTERVAL (MOD (the MONTH (NOW () - 1, 3) + 3) the MONTH), '% Y - % m - 01 00:00:00)

)
AND
(
S.S SBM='environmental monitoring product a'
OR s.S SBM='environmental monitoring product department 2'
The OR s.S SBM='environmental monitoring products'
The OR s.S SBM='environmental monitoring products'
The OR s.S SBM='environment map products'
The OR s.S SBM='Beijing'
The OR s.S SBM='Shanghai institutes'
)
) a
LEFT the JOIN
(
SELECT
ANY_VALUE (s.X glasfischer glastechnik) 'item number',
ANY_VALUE (m.R JJE), amount of 'software',
The SUM (s.G ZSJ)/8 'in the second quarter of the actual people,
Concat (round ((SUM (s.G ZSJ)/8) * 700/ANY_VALUE (m.R JJE)), '%') 'of the second quarter hours accounted,
The FROM t_erp_oa_gzrz_ssjl s
Left the join t_xmgl_jfgl_project_main m ON s.X glasfischer glastechnik=m.Y XMBH
WHERE
Quarter (s.R ZSJ)
Not in
(
QUARTER (curdate ())
AND year (s.R ZSJ)=year (now ())
)
AND
(
S.S SBM='environmental monitoring product a'
OR s.S SBM='environmental monitoring product department 2'
The OR s.S SBM='environmental monitoring products'
The OR s.S SBM='environmental monitoring products'
The OR s.S SBM='environment map products'
The OR s.S SBM='Beijing'
The OR s.S SBM='Shanghai institutes'
)
B)
ON a.x glasfischer glastechnik=b.x glasfischer glastechnik
GROUP BY a.x glasfischer glastechnik, b.x glasfischer glastechnik