mysql - 我想要获得相同的行名称，但我们想要获得依赖的数据

Question

一个查询输出

"MAO"           "5"
"ADA_REGULAR"   "1"
"JDA_REGULAR"   "1"
"SR_ASST_SRC"   "2"
"SUPDT_SRC"     "1"
"AO_SRC"        "3"
"ADA_SRC"       "2"
"DDA_SEEDS"     "2"
"JDA_SEEDS"     "2"
"ADDDIR_SEEDS"  "2"

第二个查询输出

"MAO"           "4"
"ADA_REGULAR"   "3"
"AO Tech"       "3"
"JDA_REGULAR"   "3"

我想要这样的输出。

column_name      no.of_days         no.of_days

"MAO"               "5"                    4
"ADA_REGULAR"       "1"                    3
"JDA_REGULAR"       "1"                    3
"SR_ASST_SRC"       "2"                    0
"SUPDT_SRC"         "1"                    0
"AO_SRC"            "3"                    0
"ADA_SRC"           "2"                    0
"DDA_SEEDS"         "2"                    0
"JDA_SEEDS"         "2"                    0
"ADDDIR_SEEDS"      "2"                    0
"AO Tech"            0                     3

在此处输入代码

Answer 1

您可以尝试对输出使用联合来获取所需的所有列名称，并将此结果左连接到查询输出以获取第一个和第二个输出的日期

select t1.column_name, ifnull(t2.num_days,0), ifnull(t3.num_days,0) 
from  (
  select column_name 
  from  // your query for output 1 
  union 
  select   column_name 
  from  // your query for output 2
) t1 
left  join  (
  // your query for output 1 
  select column_name,  num_days 
  from my_table1 
)  t2 ON t1.column_name = t2.column_name
left  join  (
   // your query for output 2 
  select column_name,  num_days 
  from my_table1 
)  t3 ON t1.column_name = t3.column_name