选择子查询的最大总和和最大计数 [英] Select max of sum and max of count of a subquery

问题描述

我有一个查询，它为我提供了每个类别的总和和计数.它是降序排列的.第一个值是最大值，所以总和最高，计数最高.

I have a query which gives me the sum and counts per category. It is sorted descending. The first value is the max value, so the highest sum and highest count.

SELECT SUM(score) AS calcsum, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY SUM(score) DESC;

分别

SELECT count(*) as counted, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY count(*) DESC;

然而，现在我不想要一个列表，我只想要最大值.我想先用最大计数和最大总和来实现它.第二种方法是只提取列表的第一个观察值.

However, now I do not want a list, I just want the maximium value. I want to implement it first with max of count and max of sum. And second way is to just extract the first observation of the list.

我尝试的第一种方法:

SELECT category, MAX(calcsum)
FROM (
SELECT SUM(score) AS calcsum, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY SUM(score) DESC);

分别

SELECT category, MAX(counted)
FROM (
SELECT count(*) AS counted, category
INTO newdataset
FROM table1 INNER JOIN
     table2
     ON table1.keys  =table2.ID
WHERE table2.filter = "Value"
GROUP BY category
ORDER BY count(*) DESC);

这会导致错误消息操作查询不能用作行源".

Which leads to an error message "An action query cannot be used as a row source".

我尝试的第二种方法:

SELECT TOP 1 * 
 FROM (
    SELECT SUM(score) AS calcsum, category
    INTO newdataset
    FROM table1 INNER JOIN
         table2
         ON table1.keys  =table2.ID
    WHERE table2.filter = "Value"
    GROUP BY category
    ORDER BY SUM(score) DESC);

分别

SELECT TOP 1 * 
 FROM (
    SELECT SUM(score) AS calcsum, category
    INTO newdataset
    FROM table1 INNER JOIN
         table2
         ON table1.keys  =table2.ID
    WHERE table2.filter = "Value"
    GROUP BY category
    ORDER BY SUM(score) DESC);

同样的错误.这里有什么问题，为什么它不起作用，我不明白错误信息?我想要两种方式的工作解决方案，工作解决方案会是什么样子?

Same error here. What is the problem here, why is it not working and I do not understand the error message? I would like to have working solutios for both ways, how would a working solution look like?

更新:我根据答案尝试:

SELECT category, max(calcsum)
INTO newdataset
FROM (SELECT SUM(score) AS calcsum, category      
      FROM table1 INNER JOIN
           table2
           ON table1.keys = table2.ID
      WHERE table2.filter = "Value"
      GROUP BY category
     ) t;

但是，我收到一条错误消息(类别不是聚合函数的一部分，已翻译)?

However, I get an error message (category is not part of the aggregate function, translated)?

我也试过:

 SELECT t.category, max(calcsum)
    INTO newdataset
    FROM (SELECT SUM(score) AS calcsum, category      
          FROM table1 INNER JOIN
               table2
               ON table1.keys = table2.ID
          WHERE table2.filter = "Value"
          GROUP BY category
         ) t;

但同样的错误.那么使用 max 方式而不是 top 的工作解决方案会是什么样子?

But same error. So how would a working solution using the max way and not top look like?

当我尝试这个时:

 SELECT category, calcsum
    INTO newdataset
    FROM (SELECT SUM(score) AS calcsum, category      
          FROM table1 INNER JOIN
               table2
               ON table1.keys = table2.ID
          WHERE table2.filter = "Value"
          GROUP BY category
         ) t;

它有效，但是当我添加 max(calcsum) 时，出现错误(类别不是聚合函数的一部分，已翻译)?

It works, but when I add max(calcsum), I get the error (category is not part of the aggregate function, translated)?

推荐答案

你的版本不起作用的原因是因为 INTO 只允许在 outermost 查询中使用.您正试图将其放入子查询中.另外:

The reason your versions do not work is because INTO is only allowed in the outermost query. You are trying to put it in a subquery. In addition:

• ORDER BY 在子查询中通常是不允许的.
• FROM 子句中的子查询需要一个表别名.

• ORDER BY is generally not allowed in a subquery.
• Subqueries in the FROM clause need a table alias.

所以，错误很多.

例如，您可以将最后一个查询写为:

For instance, you could write the last query as:

SELECT TOP (1) t.* 
INTO newdataset
FROM (SELECT SUM(score) AS calcsum, category      
      FROM table1 INNER JOIN
           table2
           ON table1.keys = table2.ID
      WHERE table2.filter = "Value"
      GROUP BY category
     ) t
ORDER BY SUM(score) DESC;

正如 GMB 指出的那样，您不需要此逻辑的子查询.

As GMB points out, you don't need a subquery for this logic.

我要指出的是，如果您有最大值的重复项，那么逻辑会选择任意最大值.如果您想要所有这些，请使用 TOP (1) WITH TIES.

I would point out that if you have duplicates for the maximum values, then the logic chooses an arbitrary maximum. If you want all of them, use TOP (1) WITH TIES.

我会更进一步，并质疑为什么您需要在 新 表中使用它.如果要在后续处理中使用，为什么不直接将值存储在参数中?

I would go one step further and question why you need this in a new table. Why not just store the values in parameters if you want to use the in subsequent processing?

这篇关于选择子查询的最大总和和最大计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！