sql - 如何在 MS-SQL 中替换 MySQL 代码

Question

我在 Mysql 中编写了以下代码。但现在我想在 MS-SQL 服务器中运行相同的代码。我怎么做？！我不知道 MS-SQL 中的一些语法。

代码：

select 
    t2.gname 
from
(select 
    sum(t1.strength) as sst, 
    group_concat(t1.name order by t1.name) as gname 
    from 
    (select 
         a.name, 
         a.strength, 
         group_concat(d.zone order by d.zone) as zones 
         from Animals a 
         inner join Details d on a.id = d.id 
         group by a.id) t1 
    group by t1.zones) t2 
    order by t2.sst desc limit 1;

我在 MySQL 中得到了正确的输出。但是我怎样才能为 MS-SQL 改变它呢？

请帮忙！与此解决方案有关的注意问题是：问题陈述

标签： sqlsql-server

Answer 1

我会建议：

select top (1) string_agg(a.name, ',') within group (order by a.name) as gname 
from (select a.name, a.strength, 
             string_agg(d.zone, ',') within group (order by d.zone) as zones 
      from Animals a join
           Details d
           on a.id = d.id 
      group by a.id, a.name, a.strength
     ) a
group by a.zones
order by sum(a.strength) desc;

换句话说，在 MySQL 或 SQL Server 中都不需要外部查询。