mysql - SQL Server 中 Select 的正确格式

Question

我有一个对任何数据库的简单查询，它总是在 MySQL 而不是在 SQL Server 中运行

select
tagalerts.id,
ts,
assetid,
node.zonename,
battlevel
from tagalerts, node
where
ack=0 and
tagalerts.nodeid=node.id
group by assetid
order by ts desc 

错误是:

column tagalerts.id is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

将 tagalerts.id 添加到 group by 子句不是简单的情况，因为 ts 和 会重复错误>assetid 等，这意味着所有选择都需要在一个组中或在聚合函数中......这两者都会导致无意义和不准确的结果。

将选择拆分到子查询中以正确排序和分组(如您所料，这再次适用于 MySQL)使事情变得更糟

SELECT * from
(select
tagalerts.id,
ts,
assetid,
node.zonename,
battlevel
from tagalerts, node
where
ack=0 and
tagalerts.nodeid=node.id
order by ts desc 
)T1
group by assetid

the order by clause is invalid in views, inline functions, derived tables and expressions unless TOP etc is used

“正确的输出”应该是

id     ts                 assetid     zonename     battlevel
1234   a datetime         1569        Reception    0
3182   another datetime   1572        Reception    0

要么我完全错误地阅读了 SQL Server 的规则，要么这是该数据库的一个主要缺陷。

我如何编写它才能在两个系统上工作？

Answer 1

在大多数数据库中，您不能在不使用聚合函数的情况下只包含不在 GROUP BY 中的列。

MySql 是个异常(exception)。但 MS SQL Server 不是。

因此您可以仅使用“assetid”保留GROUP BY。
然后对所有其他列使用适当的聚合函数。

此外，使用 JOIN看在上帝布丁份上的语法。
像 select * from table1, table2 where table1.id2 = table2.id 这样的 SQL 使用的是上个世纪的语法。

SELECT
MAX(node.id) AS id,
MAX(ta.ts) AS ts,
ta.assetid,
MAX(node.zonename) AS zonename,
MAX(ta.battlevel) AS battlevel
FROM tagalerts AS ta
JOIN node ON node.id = ta.nodeid
WHERE ta.ack = 0
GROUP BY ta.assetid
ORDER BY ta.ts DESC;

在 MS SQL Server 中使用的另一个技巧是窗口函数 ROW_NUMBER。
但这可能不是您所需要的。

例子:

SELECT id, ts, assetid, zonename, battlevel
FROM
(
  SELECT
   node.id,
   ta.ts,
   ta.assetid,
   node.zonename,
   ta.battlevel,
   ROW_NUMBER() OVER (PARTITION BY ta.assetid ORDER BY ta.ts DESC) AS rn
  FROM tagalerts AS ta
  JOIN node ON node.id = ta.nodeid
  WHERE ta.ack = 0
) q
WHERE rn = 1
ORDER BY ts DESC;