我如何使用“分组依据"?三列数据? [英] How do I use "group by" with three columns of data?

问题描述

我目前的查询是:

SELECT x, MAX(z) AS mz, y FROM my_table GROUP BY x

x 和 mz 列按预期返回，但最后一列 y 与其他两列不匹配.换句话说，我希望y"列像当前的 x 列一样匹配 mz 列.我该如何解决?

The columns x and mz are returned as expected, but the last column, y, does not match up with the other two. In other words, I want the "y" column to match the mz column just like the x column currently does. How do I pull that off?

更新:抱歉，问题不是很清楚.我想执行以下查询:

UPDATE: Sorry, the question wasn't very clear. I want to perform the following query:

SELECT * FROM (SELECT x, MAX(z) ASmz FROM my_table GROUP BY x) a RIGHT加入 (SELECT y, MAX(z) AS mz FROMmy_table GROUP BY y) b ON a.mz =b.mz

SELECT * FROM (SELECT x, MAX(z) AS mz FROM my_table GROUP BY x) a RIGHT JOIN (SELECT y, MAX(z) AS mz FROM my_table GROUP BY y) b ON a.mz = b.mz

无需使用 3 个 SELECT 语句(也许这没什么大不了的，但对我来说这似乎是一个低效的查询.但我对 sql 查询还很陌生，所以我不知道.)

without having to use 3 SELECT statements (Perhaps that's not a big deal, but it seems like an inefficient query to me. But I'm pretty new at sql queries, so I dunno.)

更新 #2:假设我的表是这样的:

UPDATE #2: Lets say my table looks like this:

-------------------
|  x  |  y  |  z  |
-------------------
| 45  |  h  |  3  |
| 23  |  c  |  5  |
| 45  |  e  |  9  |
| 23  |  b  |  12 |
| 45  |  x  |  36 |
| 33  |  s  |  44 |
| 33  |  p  |  78 |
-------------------

我想返回以下内容:

-------------------
|  x  |  y  |  z  |
-------------------
| 23  |  b  |  12 |
| 45  |  x  |  36 |
| 33  |  p  |  78 |
-------------------

推荐答案

You can do

select s.x, s.mz, stuff.y 
from (select x, max(z) as mz from stuff group by x) s 
  left join stuff on stuff.x = s.x and stuff.z = s.mz;

这篇关于我如何使用“分组依据"?三列数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！