Mysql列作为sql server中的vColumn语法

Question

我想知道如何在sqlserver中实现这个查询。

Mysql语法:

SELECT (COUNT(parent.Cat_Name) - 1) AS depth FROM tbl AS parent

Microsoft sqlserver 的相同查询是什么？谢谢。

编辑:

我在 sql-server 中运行查询时出现错误:

Error Number: 42000

[Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'category.Cat_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT node.Cat_ID, node.Cat_Name, (COUNT(parent.Cat_Name) - 1) AS depth FROM category AS node, category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.IsActive = 1 GROUP BY node.Cat_Name ORDER BY node.lft

Filename: C:\EasyPHP-DevServer-14.1VC9\data\localweb\timit\system\database\DB_driver.php

Line Number: 330

整个查询是:

SELECT node.Cat_ID, node.Cat_Name, (COUNT(parent.Cat_Name) - 1) AS depth
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.IsActive = 1
GROUP BY node.Cat_Name
ORDER BY node.lft"

Answer 1

您需要将 node.Cat_ID 和 node.lft 添加到 GROUP BY 子句中:

SELECT node.Cat_ID, node.Cat_Name, (COUNT(parent.Cat_Name) - 1) AS depth
FROM category AS node,
category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.IsActive = 1
GROUP BY node.Cat_Name, node.Cat_ID, node.lft
ORDER BY node.lft

查询的 SELECT 或 ORDER BY 部分中使用的每一列必须位于聚合函数(如 count)中，或者位于聚合函数中在 GROUP BY 中 - 不允许异常(exception)，即使 Cat_ID 始终由 Cat_Name 确定。