$date = $this->db->select('crv.date')
            ->from('commodities com')
            ->where('com.id = ' . $row->id)
            ->join('curves crv', 'com.curve_id = crv.profile_id', 'left')
            ->group_by('crv.date')
            ->having('COUNT(DISTINCT crv.job_id) = ' . $nr_commodities)
            ->order_by('crv.date DESC')
            ->limit(1)
            ->get()->row();

我需要在mysql中转换上述查询：
我试过以下方法。但是有些语法错误。任何帮助都将不胜感激。

select crv.date
from commodities com
  where com.id = 100
left join curves crv on com.curve_id = crv.profile_id
group by crv.date
  having COUNT(DISTINCT crv.job_id) = 4
order by crv.date DESC
           limit 1