“无效的列名”,按子查询的别名排序 [英] "Invalid column name", Order By on Alias of subquery
本文介绍了“无效的列名”,按子查询的别名排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个存储过程,我想在其中添加替代的order by子句。
问题是对无效的列名'aantal regels'的查询失败了。
I've created a stored procedure where i want to add an alternative order by clause. The problen is that the query failed on a "Invalid column name 'aantal regels'"
这是我现在要查询的内容。
Here is the query I have now.
SELECT
l.lead_id,
l.afdeling_id,
l.advertentie_id,
l.naam,
l.type,
l.status,
l.herkomst,
l.aanmaakdatum,
l.klant_id,
l.overigegegevens,
af.afdelingsnaam,
(SELECT
COUNT(lead_regel_id)
FROM
Lead_regel As lr
Where
Lr.lead_id = l.lead_id And
lr.status <> 100
)
AS aantal_regels,
(SELECT
COUNT(lead_id)
FROM
Lead_unread As lu
Where
lu.lead_id = l.lead_id And
lu.user_id = @uid
)
As lead_ongelezen,
(SELECT
COUNT(lru.lead_regel_id)
FROM
Lead_regel As lr2
INNER JOIN
Lead_regel_unread As lru ON
lr2.lead_regel_id = lru.lead_regel_id
Where
lr2.lead_id = l.lead_id And
lru.user_id = @uid And
lr2.status <> 100
)
As lead_regel_ongelezen
FROM
Lead AS l
INNER JOIN
Afdeling AS af ON
l.afdeling_id = af.afdeling_id
WHERE
l.afdeling_id = @aid AND
l.status <> 100
ORDER BY
CASE WHEN @orderby = 'default' THEN l.aanmaakdatum END DESC,
CASE WHEN @orderby = 'type' THEN l.type END ASC,
CASE WHEN @orderby = 'naam' THEN l.naam END ASC,
CASE WHEN @orderby = 'reacties' THEN aantal_regels END DESC
希望有人可以帮助我!
推荐答案
您不能
第一种选择是重复代码。注意:仅仅因为重复代码,SQL引擎就不会天真地再次执行它,而是重新使用结果。
The first option is to repeat the code. Note: Just because you repeat the code, the SQL Engine isn't so naive as to execute it again, it re-uses the results.
ORDER BY
CASE WHEN @orderby = 'default' THEN l.aanmaakdatum END DESC,
CASE WHEN @orderby = 'type' THEN l.type END ASC,
CASE WHEN @orderby = 'naam' THEN l.naam END ASC,
CASE WHEN @orderby = 'reacties' THEN (SELECT
COUNT(lead_regel_id)
FROM
Lead_regel As lr
WHERE
Lr.lead_id = l.lead_id And
Lr.status <> 100
) END DESC
或者全部使用子查询...
Or so it all using a sub query...
SELECT
*
FROM
(
yourQuery
)
AS sub_query
ORDER BY
CASE WHEN @orderby = 'default' THEN aanmaakdatum END DESC,
CASE WHEN @orderby = 'type' THEN type END ASC,
CASE WHEN @orderby = 'naam' THEN naam END ASC,
CASE WHEN @orderby = 'reacties' THEN aantal_regels END DESC
这篇关于“无效的列名”,按子查询的别名排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文