重写查询并消除子查询

Question

我有以下查询：

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, 
 num_bases_in_singletons 
FROM stats_assembly WHERE ws_id IN (
  SELECT workflow_steps.id 
  FROM workflow_steps, workflows 
  WHERE workflow_steps.workflow_id = workflows.id 
      AND workflows.sample_id = (
            SELECT workflows.sample_id FROM workflows WHERE workflows.id = (
                SELECT workflow_id FROM workflow_steps WHERE id = 462
            )
            AND workflow_steps.submodule_id = 2
      )
)

我想重写它而不使用子查询。到目前为止，我只能做到以下几点：

SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, 
  num_bases_in_singletons 
FROM stats_assembly 
INNER JOIN workflow_steps ON ( stats_assembly.ws_id = workflow_steps.id ) 
INNER JOIN workflows ON ( workflow_steps.workflow_id = workflows.id )

我不知道该怎么完成它。

此外，这个查询可能会返回多个行，但我希望得到具有MAX(ws_id)的行。任何提示都将不胜感激。

Answer 1

我想这会把你带到那里..。我是Server的人..。

SELECT 
  ws_id
, n50
, num_singletons
, num_segments_less_100
, kmer
, num_all_reads
, number_bases_in_contigs
, num_bases_in_singletons 
FROM 
 stats_assembly sa
 join workflow_steps ws1 
   on sa.ws_id = ws1.id 
   and ws1.submodule_id = 2
 join workflows wf1 
   on ws1.workflow_id = wf1.id 
 join workflows wf2 
   on wf1.sample_id = wf2.sample_id 
 join workflow_steps ws2
   on wf2.id = ws2.workflow_id 
   and ws2.id = 462