如何将MSSQL CTE查询转换为MySQL 5.7？
如何将MSSQL CTE查询转换为MySQL 5.7？
提问于 2019-11-05 13:35:55
我有一个SQL查询
with temp as
(
select min(ms_date) as start, max(ms_date) as [end], count(sessionid) 'visitor_count',category,convert(varchar, ms_date , 106) 'ms_date'
from temp_tbltrack where category =@id
group by category,convert(varchar, ms_date , 106)
)
select category,ms_date,CASE WHEN datediff(ss,temp.start,temp.[end]) <>0 THEN datediff(ss,temp.start,temp.[end]) ELSE 45 END 'Timespan',visitor_count from temp
order by convert(datetime,ms_date)我转换成了Mysql
select temp from
(
select min(ms_date) as start, max(ms_date) as `end`, count(sessionid) 'visitor_count',category,date_format (ms_date , 106) 'ms_date'
from temp_tbltrack where category =p_id
group by category,date_format (ms_date , 106)
)
select category,ms_date,CASE WHEN timestampdiff(ss,temp.start,temp.[end]) <>0 THEN timestampdiff(ss,temp.start,temp.[end]) ELSE 45 END 'Timespan',visitor_count from temp
order by convert(ms_date, datetime); 如何在Mysql 5.7版本中转换CTE
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-11-05 20:32:19
我认为不需要子查询：
select category,
format(ms_date, '%d %b %Y') as ms_date,
(case when max(ms_date) = min(ms_date)
then 45
else timestampdiff(second, min(ms_date), max(ms_date))
end) as timestamp
count(sessionid) as `visitor_count`,
from temp_tbltrack
where category = @id
group by category, format(ms_date, '%d %b %Y')
order by min(ms_date);Stack Overflow用户
发布于 2019-11-05 14:19:48
您可以使用子查询，因为您不能在您的mysql版本上使用cte。
select t1.category
, t1.ms_date
, case when datediff(ss, temp.start, temp.[end]) <> 0
then datediff(ss,temp.start,temp.[end]) else 45 end 'Timespan'
, t1.visitor_count
from
(select min(ms_date) as start, max(ms_date) as [end], count(sessionid) 'visitor_count', category, cast(ms_date as date) 'ms_date'
from temp_tbltrack where category =@id
group by category, cast(ms_date as date)) t1
order by cast(t1.ms_date as datetime)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58705059
复制相关文章
相似问题
腾讯云开发者
