how to simplify this sql

Question

I have a problem in my sql expression, I want to put different columns in a main select,these different columns has the same where clause.

Here is my sql example:

SELECT t.*,

  (SELECT monitor_value
   FROM sub_message s
   WHERE s.project_name=t.project_name
   ORDER BY monitor_time ASC
   LIMIT 1) AS project_start_val,

  (SELECT monitor_time
   FROM sub_message s
   WHERE s.project_name=t.project_name
   ORDER BY monitor_time ASC
   LIMIT 1) AS project_start_time
FROM sub_message t

Answer 1

Your query shows all sub messages with their oldest monitor time and value. The straight-forward approach to this is using window functions (i.e. aggregate functions with an OVER clause). These are available as of MySQL 8.

The simplest way with FIRST_VALUE:

select 
  sm.*,
  first_value(monitor_value) over oldest_first as project_start_val,
  first_value(monitor_time) over oldest_first as project_start_time
from sub_message sm
window oldest_first as (partition by project_name order by monitor_time);

You can also get the first row per project in the from clause:

select 
  sm.*,
  smm.monitor_value as project_start_val,
  smm.monitor_time as project_start_time
from sub_message sm
join
(
  select sm2.*, min(monitor_time) over (partition by project_name) as min_monitor_time
  from sub_message sm2
) smm on smm.project_name = sm.project_name and smm.monitor_time = smm.min_monitor_time;

Before MySQL 8, window functions where not available. There you had to select from the table again. E.g.:

select 
  sm.*,
  smm.monitor_value as project_start_val,
  smm.monitor_time as project_start_time
from sub_message sm
join sub_message smm
  on smm.project_name = sm.project_name
  and (smm.project_name, smm.monitor_time) in
  (
    select project_name, min(monitor_time)
    from sub_message
    group by project_name
  );

Answer 2

The following should work (untested):

select t.*,
  s2.monitor_value project_start_val,  
  s2.monitor_time project_start_time
from sub_message t
LEFT JOIN (select project_name pn, min(monitor_time) pt from sub_message group by project_name) s1 ON pn=t.project_name
LEFT JOIN sub_message s2 ON s2.project_name=s1.pn and s2.monitor_time=s1.pt

You still need to LEFT JOIN twice and it will only work reliably if the columns project_name,monitor_time are unique in table sub_message. But if this condition is met it provides you with the opportunity of including further columns of s2 into the query without having to add another subquery.