sql - 将查询转换为需要 10 秒才能显示结果的 SQL

Question

我有这个 ColdFusion 代码

<cfquery name="myqry">
select max(id) as id, count(errorid) as ecount, max(errorid) as errorid, template,line,error_message, error_type 
from mytable 
where 1=1 
group by errorid, template,line,error_message, error_type 
order by max(datein) desc
</cfquery>

<cfoutput query="myrqy">
    <cfquery name="thisQ" datasource="anc">
select viewed,status,errorid,datein,error_message,error_type,
    template,id from mytable where id = #myqry.id#
</cfquery>
#get.ecount# - thisQ.errorid# - #thisQ.Error_Message - #thisQ.template#
</cfoutput>

我尝试使用此代码，但它不会产生结果并且会引发大量错误；我想让一切都在一秒钟之内。

WITH max_id AS 
(
    SELECT
        MAX(id) AS id, 
        COUNT(errorid) AS ecount, 
        MAX(errorid) AS errorid, 
        template, line, 
        error_message,  error_type 
    FROM
        mytable 
    WHERE 
        1=1 
    GROUP BY 
        errorid, template, line, error_message, error_type 
    ORDER BY
        MAX(datein) DESC
)
SELECT
    viewed, status, errorid, datein, 
    error_message, error_type,
    template, id
FROM
    mytable mt
INNER JOIN
    max_id mi ON mt.id = mi.id

标签： sqlsql-servercommon-table-expression

Answer 1

我试图弄清楚你想要完成什么。乍一看，您似乎想要返回最近的错误，可能包含一些历史背景，例如它之前发生了多少次。如果我很接近，我认为不需要 CTE。