SQL GROUP BY，其中包含镜像值的列 [英] SQL GROUP BY with columns which contain mirrored values

问题描述

对不起的标题。我无法想出一个更好的方式来描述我的问题。

我有下表：

  Category | A | B 
 A | 1 | 2 
 A | 2 | 1 
 B | 3 | 4 
 B | 4 | 3 
  

我想按类别，每个类别只返回1行，但同时提供列的值 A 和 B 。

所以结果应该如下所示：

  category | resultA | resultB 
 A | 1 | 2 
 B | 4 | 3 
  

这是如何实现的？

我试过这个语句：

  SELECT类别，a，b 
 FROM表格
 GROUP BY类别
  

但很明显，我收到以下错误：

< blockquote>

列a在选择列表中无效，因为它在集合函数或GROUP BY子句中不包含
。

列b在选择列表中无效，因为它不包含在
聚合函数或GROUP BY子句中。

我怎样才能达到预期的效果？

>试试这个：

  SELECT类别，MIN（a）AS resultA，MAX（a）AS resultB 
 FROM表格
 GROUP BY类别
  

如果这些值是镜像的，那么您可以使用<$在code> a 之类的单个列上应用c $ c> MIN ， MAX p>

Sorry for the bad title. I couldn't think of a better way to describe my issue.

I have the following table:

Category | A | B
A        | 1 | 2
A        | 2 | 1
B        | 3 | 4
B        | 4 | 3

I would like to group the data by Category, return only 1 line per category, but provide both values of columns A and B.

So the result should look like this:

category | resultA | resultB
A        | 1       | 2
B        | 4       | 3

How can this be achieved?

I tried this statement:

SELECT category, a, b
FROM table
GROUP BY category

but obviously, I get the following errors:

Column 'a' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Column 'b' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I achieve the desired result?

解决方案

Try this:

SELECT category, MIN(a) AS resultA, MAX(a) AS resultB
FROM table
GROUP BY category

If the values are mirrored then you can get both values using MIN, MAX applied on a single column like a.

这篇关于SQL GROUP BY，其中包含镜像值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！