使用条件语句连接多个表

Question

我有3个mysql表，如下所示：

​

​

基于该表，我想创建一个带有条件语句的表:如果表3中的skor和nilai，则table2中的saran = mayor和minor，否则，表1中的saran = butuh，值大于0，concat by bidang。对于nilai =

selct((max(skor)-min(skor))*0.25)+min(skor) from table 3

所以，结果是这样的：

​

​

怎么做呢？

Answer 1

尝试执行以下查询：

select
NIP,
Bidang,
case when Skor >= ((max(skor)-min(skor))*0.25)+min(skor)
then concat(mayor,',',minor) 
else concat(Butuh,',',Bidang)
end  as Saran
from
(
select table2.*,
table1.Butuh,
table1.Kurang,
table3.Skor
from table2 join table1
on table2.Bidang = table1.Bidang
left join table3 
on table2.NIP = table3.NIP
where table1.Butuh > 0
) tab

Answer 2

假设nilai仅仅意味着一个全局分数阈值，那么您基本上必须...

• 构建一个查询，该查询将不同记录中的nilai nilai数据聚合到单个字符串
• 连接表中，并且aggregate query
• 会选择所需的数据，并根据需要应用函数来创建适当的输出。

您的结果将如下所示：

select t2.Nip
     , t3.bidang
     , case when t3.skor >= t3disc.nilai then t2.mayor || ', ' || t2.minor else base_1.butuh_agg end
                    saran
  from table_2 t2
  join table_3 t3   on ( t3.Nip = t2.Nip and  t3.bidang = t2.bidang )
  join (
                select t11.bidang
                     , t11.butuh
                        || CASE WHEN t12.butuh IS NOT NULL THEN ', ' || t12.butuh ELSE '' END 
                        || CASE WHEN t13.butuh IS NOT NULL THEN ', ' || t13.butuh ELSE '' END 
                        || CASE WHEN t14.butuh IS NOT NULL THEN ', ' || t14.butuh ELSE '' END 
                        || CASE WHEN t15.butuh IS NOT NULL THEN ', ' || t15.butuh ELSE '' END 
                            butuh_agg
                  from (
                            select t1.bidang
                                 , min(t1.kurang)   kmin
                              from table_1  t1
                          group by t1.bidang
                       ) t11
             left join table_1  t12 on ( t12.bidang = t11.bidang AND t12.kurang = t11.kmin + 1)
             left join table_1  t13 on ( t13.bidang = t11.bidang AND t13.kurang = t11.kmin + 2)
             left join table_1  t14 on ( t14.bidang = t11.bidang AND t14.kurang = t11.kmin + 3)
             left join table_1  t15 on ( t15.bidang = t11.bidang AND t15.kurang = t11.kmin + 4)
        ) base_1   on ( base_1.bidang = t2.bidang )
   join (
               select t31.bidang
                    , t3agg.nilai
                 from table_3  t31
           cross join (
                         select (max(t32.skor) - min(t32.skor)) * 0.25 ) + min(t32.skor)   nilai
                           from table_3 t32
                      ) t3agg
        ) t3disc  on ( t3disc.bidang = t2.bidang )
      ;

这个建议假设对于表1中的任何给定bidang，都有最多4个非零值，这些值之间没有重复，并且这些值小于或等于4。否则，您必须在查询中求助于排名函数。