SQL子查询错误 [英] SQL Sub-query error

问题描述

我不确定如何在SQL中进行子查询

I'm not sure exactly how to make a sub-query in SQL

这是我的尝试，但如果工作正常则会出现错误。 

This is my attempt but there is an error that stops if from working. 

Msg 1033，Level 15，State 1，Line 9

ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效，除非还指定了TOP，OFFSET或FOR XML。

Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

SELECT MAX(totalTickets), expiationOffenceLongDescription, MONTH(issueDate), YEAR(issueDate)
FROM
	(
	SELECT COUNT(MONTH(issueDate)) as totalTickets, expiationOffenceLongDescription, 
			MONTH(issueDate) as mnth,  YEAR(issueDate) as yr
	FROM Expiations
	GROUP BY expiationOffenceLongDescription, MONTH(issueDate), YEAR(issueDate)
	ORDER BY totalTickets DESC
	)
GROUP BY YEAR(issueDate)

在我看来，外部查询无法将外部查询视为无效列名的外部查询报告中的列。我有什么建议可以解决这个问题吗？

In my opinion the outer query is unable to see the internal query as the columns in the outside query reports of invalid column name. Any suggestions on what I can do to fix this?

推荐答案

在从中提取一些详细信息后，您已将IssueDate重命名为多个名称，因此外部查询只知道重命名的部分，totalTickets，mnth，yr，expiationOffenceLongDescription列。

you have renamed IssueDate to multiple names after extracting some of details from it, so outer query knows only renamed parts , totalTickets, mnth, yr ,expiationOffenceLongDescription columns.

此外，您不能在子查询中使用order by（除非有top），也可能不能用于排序数据的目的。在外部查询上应用订单。

Also, you cannot use order by with in sub query ( unless with top ), also it may not serve purpose of ordering the data. Apply order by on outer query.

SELECT MAX(totalTickets), expiationOffenceLongDescription, max(mnth)
FROM
	(
	SELECT COUNT(MONTH(issueDate)) as totalTickets, expiationOffenceLongDescription, 
			MONTH(issueDate) as mnth,  YEAR(issueDate) as yr
	FROM Expiations
	GROUP BY expiationOffenceLongDescription, MONTH(issueDate), YEAR(issueDate)
	
	)
GROUP BY yr
ORDER BY totalTickets DESC

这篇关于SQL子查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！