CODEWITHSUNDEEP
mysql
bestliuxingtang
bestliuxingtang

Reputation: 23

Does mysql 5.6 support the [ with.. as() ] syntax?

navicat picture

with detail as(
select id,end_time,status from t_wl_record_repairs_detail where end_time is null
)
select COUNT(1) as sum, 'today' as name from detail where end_time is not null and end_time >= '2021-02-04 00:00:00' and `status` > 2
UNION
select COUNT(1) , 'd1' as name from detail where end_time is not null and end_time < '2021-02-04 00:00:00' and `status` < 3
UNION
select COUNT(1), 'd7' as name from detail where end_time is not null and end_time < '2021-01-29 00:00:00' and `status` < 3
UNION
select COUNT(1), 'd30' as name from detail where end_time is not null and end_time < '2021-01-06 00:00:00' and `status` < 3;

I want to write sql like this,but it didn't work.Does mysql 5.6 support the [ with.. as() ] syntax?

Upvotes: 1

Views: 622

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

No, Common Table Expressions (the name of the WITH clause) were introduced in MySQL 8. They cannot be used in 5.6. Instead we typically would join subqueries in older MySQL versions.

For a query like yours where you are reusing the same common table expression across several UNION components, instead of a subquery I would recommend selecting it into a temporary table which you then use in your UNION query.

CREATE TEMPORARY TABLE detail 
  SELECT id, end_time, status
  FROM t_wl_record_repairs_detail
  WHERE end_time IS NULL;

SELECT COUNT(1) as sum, 'today' as name FROM detail...
UNION
SELECT COUNT(1) as sum, 'd1' as name....
UNION...

Update:

MySQL has documented that temporary tables cannot be referenced more than once, such as in a UNION.

This still might be doable with a clever chain of CASE and no temporary table or CTE.

SELECT
  COUNT(1) AS sum,
  CASE 
    WHEN end_time end_time >= '2021-02-04 00:00:00' and `status` > 2 THEN 'today'
    WHEN end_time < '2021-02-04 00:00:00' and `status` < 3 THEN 'd1'
    WHEN end_time < '2021-01-29 00:00:00' and `status` < 3 THEN 'd7'
    WHEN end_time < '2021-01-06 00:00:00' and `status` < 3 THEN 'd30'
    ELSE 'other'
  END AS name
FROM t_wl_record_repairs_detail
GROUP BY name

Upvotes: 1

Related Questions