sql—将按类别筛选的最高绝对值插入到另一个表中

u5i3ibmn 于 2021-08-09 发布在 Java

关注(0)|答案(3)|浏览(366)

我想从表中为每个类别选择最高的绝对值。
这是我的table：

CREATE TABLE mytable
(
     CategoryID INTEGER NOT NULL PRIMARY KEY,  
     ExpValue   INTEGER NOT NULL
);

INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, 0);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, 11);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, -59);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, 0);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, 59);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, -2);

这就变成了

+------------+----------+
| CategoryID | ExpValue |
+------------+----------+
|         11 |        0 |
|         11 |      -13 |
|         11 |      -59 |
|        130 |        0 |
|        130 |       59 |
|        130 |        2 |
+------------+----------+

对于每个类别，我都会选择如下所示的最高值：

INSERT INTO #Result
    SELECT TOP 1 *
    FROM #SmallTable
    WHERE CategoryID = 11
    ORDER BY ExpValue DESC;

这个效果不错。但我知道我发现了一个边缘案件。如你所见 CategoryID = 11 ，它将返回包含0的行。
但我希望它选择值-59。所以最高的 abs() 从任何数字。
以及 CategoryID = 130 ，它应该选择59作为一行。
table上只能放3个箱子。
对于特定的 CategoryID 如果存在多个正值和0值->返回最大值
对于特定的 CategoryID 如果存在多个0值->选择一个0值
对于特定的 CategoryID 如果存在多个负值和0值->选择最小值。
正如您所看到的，总是有一个0存在的回退，但除了唯一的正值、负值或根本没有其他值。
表实际上有更多的列（30），但我只显示了查询所需的2列。但事实上，我需要坐整排。
我的示例表的结果应该是：

+------------+----------+
| CategoryID | ExpValue |
+------------+----------+
|         11 |      -59 |
|        130 |       59 |
+------------+----------+

编辑：谢谢大家。最后我就这样做了：

INSERT INTO #Result
    SELECT TOP 1 *
    FROM #SmallTable
    WHERE CategoryID = 11
    ORDER BY ABS(ExpValue) DESC;

sql sql-server tsql sql-server-2008

来源：https://stackoverflow.com/questions/61891969/insert-highest-absolut-value-filtered-by-category-into-another-table

3条答案

按热度按时间

qlvxas9a1#

另一种选择。使用 ROW_NUMBER 窗口中的窗口功能 ORDER BY 条款，以及 TOP (1) WITH TIES :

SELECT TOP (1) WITH TIES
  CategoryID,
  ExpValue
FROM
  mytable
ORDER BY ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ABS(ExpValue) DESC);

结果：

+------------+----------+
| CategoryID | ExpValue |
+------------+----------+
|         11 |      -59 |
|        130 |       59 |
+------------+----------+

通过将行号放入“选择”列表中，更容易了解其工作原理：

CategoryID,
  ExpValue,
  ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ABS(ExpValue) DESC) as RowNum
FROM
  mytable;

结果：

+------------+----------+--------+
| CategoryID | ExpValue | RowNum |
+------------+----------+--------+
|         11 |      -59 |      1 |
|         11 |       11 |      2 |
|         11 |        0 |      3 |
|        130 |       59 |      1 |
|        130 |       -2 |      2 |
|        130 |        0 |      3 |
+------------+----------+--------+

这个 TOP (1) WITH TIES 只需从每个分组中取第1行。
这是一个rextester演示。

赞(0）回复(0）举报 2021-08-09

ibps3vxo2#

您可以使用amx abs的子查询(

select  m.CategoryID, m.ExpValue
from #SmallTable m
INNER JOIN  (
    Select CategoryID , max(abs(ExpValue )) max_abs
    FROM #SmallTable
    GROUP BY CategoryID

    ) t on t.max_abs = abs(ExpValue )
        and t.CategoryID = m.CategoryID

INSERT INTO #Result
select  m.CategoryID, m.ExpValue
from #SmallTable m
INNER JOIN  (
    Select CategoryID , max(abs(ExpValue )) max_abs
    FROM #SmallTable
    GROUP BY CategoryID

    ) t on t.max_abs = abs(ExpValue )
        and t.CategoryID = m.CategoryID

赞(0）回复(0）举报 2021-08-09

wwodge7n3#

您可以使用单个聚合和一些条件逻辑：

select categoryid,
       (case when abs(min(expval)) > abs(max(expval)) then min(expval)
             else max(expval)
        end)
from #smalltable
group by categoryid;

如果您只想对一个类别使用此选项，则可以在 group by .

赞(0）回复(0）举报 2021-08-09

我来回答

sql—将按类别筛选的最高绝对值插入到另一个表中

3条答案

相关问题

热门标签

最新问答