Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
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,
    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
                I have a problem with temp tables: you can only look up a temp table once in the same query statement. like:  mysql> SELECT * FROM temp_table, temp_table AS t2; ERROR 1137: Can't reopen table: 'temp_table'  There is a 1137 error here. i quit.../手动滑稽
– bestliuxingtang
                Feb 4, 2021 at 3:19
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.