mysql - 将MSSQL转换为MySQL

Hye，我正在尝试将MSSQL查询转换为MYSQL，这导致了我的问题。这超出了我当前的舒适范围。以下是我当前的查询。

WITH n AS (
  SELECT n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n) /* create a numbers table with 10 rows */
)
, d AS ( /* Create a table with a row for each day in the date range */
         /* Use cross join to increase the rows in this table and then use top() to only return the rows we need */
   SELECT top (datediff(day, '2017-07-04', '2018-03-02')+1)
   SessionDate = convert(datetime,dateadd(day,row_number() over(order by (select 1))-1,'2017-07-04'))
   FROM n AS ten
   CROSS JOIN n AS hundred /* cross join the numbers table to create 100 rows */
   CROSS JOIN n AS thousand /* cross join the numbers table to create 1,000 rows */
   CROSS JOIN n AS tenK /* cross join the numbers table to create 10,000 rows */
   CROSS JOIN n AS hundredK /* cross join the numbers table to create 100,000 rows */
   ORDER BY SessionDate
)
, h as ( /* add time ranges to date table */
    SELECT SessionDate, StartDateTime = dateadd(hour,v.s,SessionDate), EndDateTime = dateadd(hour,v.e,SessionDate), v.point
    FROM d
    CROSS APPLY (values
       (0,12,'morning')
       ,(12,17,'afternoon')
       ,(17,24,'evening')
    )
    v (s,e,point)
)
SELECT *
FROM h

它使用数字表并将日期分为不同的时间范围。以下是结果集的示例

SessionDate             | StartDateTime           | EndDateTime             | Point
2017-07-04 00:00:00.000 | 2017-07-04 00:00:00.000 | 2017-07-04 12:00:00.000 | morning
2017-07-04 00:00:00.000 | 2017-07-04 12:00:00.000 | 2017-07-04 17:00:00.000 | afternoon
2017-07-04 00:00:00.000 | 2017-07-04 17:00:00.000 | 2017-07-05 00:00:00.000 | evening
2017-07-05 00:00:00.000 | 2017-07-05 00:00:00.000 | 2017-07-05 12:00:00.000 | morning
2017-07-05 00:00:00.000 | 2017-07-05 12:00:00.000 | 2017-07-05 17:00:00.000 | afternoon
2017-07-05 00:00:00.000 | 2017-07-05 17:00:00.000 | 2017-07-06 00:00:00.000 | evening
2017-07-06 00:00:00.000 | 2017-07-06 00:00:00.000 | 2017-07-06 12:00:00.000 | morning
2017-07-06 00:00:00.000 | 2017-07-06 12:00:00.000 | 2017-07-06 17:00:00.000 | afternoon
2017-07-06 00:00:00.000 | 2017-07-06 17:00:00.000 | 2017-07-07 00:00:00.000 | evening

最佳答案

该查询太聪明了-它使用CTE来生成数字序列而不是日历表，它使用CROSS APPLY来生成小时数，而不是使用用于小时数和名称的查找表。结果执行计划将很糟糕。

一个非常简单的日历表和一个“会话”表将使您可以创建一个更简单的T-SQL查询，例如：

CREATE TABLE Calendar (Date date primary key not null)
GO
--Omit code to fill the calendar
CREATE TABLE Sessions (StartTime int,EndTime int, Name nvarchar(20))
GO
insert into Sessions (StartTime,EndTime,Name)
VALUES
(0,12,'morning'),
(12,17,'afternoon'),
(17,24,'evening')


select Date as SessionDate,
    dateadd(hour,StartTime,Date) as StartTime,
    dateadd(hour,EndTime,Date) as EndTimeTime,
    Name
from Calendar,Sessions
where Date between @startDate and @endDate

范围查询将非常快，因为Date列已建立索引。执行计划将仅返回所有适用的日期行，并将它们与会话行合并。

可以轻松地将其转换为MySQL的方言，例如，使用DATE_ADD而不是DATEADD