how to simplify this sql

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

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
  );