SQL查询中的复杂分组 [英] Complex Grouping in SQL Query

问题描述

我需要构建一个相当复杂的SQL查询.原谅我，我不是SQL的神奇向导.

I have a fairly complicated SQL query that I need to build. Forgive me, I'm not a magical wizard with SQL.

这是我的两个桌子(大大简化了):

Here's my two tables (significantly simplified):

tableA

id   request_id   page_views   step
-----------------------------------
 1   1            0            0
 2   1            0            1
 3   1            0            2
 4   1            0            3
 5   2            0            0
 6   2            0            1
 7   2            1            2
 8   3            0            0
 9   3            0            1
10   4            0            0
11   4            0            1
12   4            0            2

tableB

id   name         phone       
------------------------------
1    John Deere   111-222-3333
2    Sally Sue    333-222-1111
3    Jacob Clark  434-343-4343
4    Alex Smith   222-112-2112

首先，需要在tableA.request_id = tableB.id所在的表上进行联接:

First, there needs to be a join on the tables where tableA.request_id = tableB.id to result in:

id   request_id   page_views   step   name          phone
----------------------------------------------------------------
 1   1            0            0      John Deere   111-222-3333
 2   1            0            1      John Deere   111-222-3333
 3   1            0            2      John Deere   111-222-3333
 4   1            0            3      John Deere   111-222-3333
 5   2            0            0      Sally Sue    333-222-1111
 6   2            0            1      Sally Sue    333-222-1111
 7   2            1            2      Sally Sue    333-222-1111
 8   3            0            0      Jacob Clark  434-343-4343
 9   3            0            1      Jacob Clark  434-343-4343
10   4            0            0      Alex Smith   222-112-2112
11   4            0            1      Alex Smith   222-112-2112
12   4            0            2      Alex Smith   222-112-2112

从该表中，我希望返回符合以下条件的组.按组，我指的是具有相同request_id的行组.条件如下:

From that table, I want groups to be returned if they match the conditions below. By group, I mean groups of rows that have the same request_id. Here's the conditions:

1. 组中的行中，没有page_views大于0.
2. 组中的行中，没有step大于2.

1. Of the rows in a group, none have page_views greater than 0.
2. Of the rows in a group, none have step greater than 2.

如果以上两个条件中的任何一个失败，将不返回整个群组.所以这是应该返回的内容:

If either of the two above conditions fail, the entire group won't be returned. So here's what should be returned:

id   request_id   page_views   step   name          phone
----------------------------------------------------------------
8    3            0            0      Jacob Clark   434-343-434
9    3            0            1      Jacob Clark   434-343-434
10   4            0            0      Alex Smith    222-112-2112
11   4            0            1      Alex Smith    222-112-2112
12   4            0            2      Alex Smith    222-112-2112

"3"组(或Jacob Clark)没有page_views大于0的任何行，并且没有任何行的step大于2.与"4"组相同(或亚历克斯·史密斯).

The "3" group (or Jacob Clark) didn't have any rows where page_views is greater than 0, and none of the rows have step greater than 2. Same with the "4" group (or Alex Smith).

这就是问题所在.我需要一个可以处理所有这些的SQL查询.第一次连接可以是子查询，没问题:

So that's the problem. I need a single SQL query that will handle all of that. The first join can be a subquery, no problem:

SELECT sub.*
FROM (
    SELECT tableA.*, tableB.name, tableB.phone
    FROM `tableA`, `tableB`
    WHERE tableA.`request_id` = tableB.id
) sub

那之后，我不太确定.

任何帮助将不胜感激.

推荐答案

您需要在子查询中分别计算最大视图和步骤(按request_id)，然后使用具有足够小的最大值的request_id:

You need to calculate the max views and steps (by request_id) separately in a subquery, and then use the request_id's with small enough max values:

SELECT a.*, b.name, b.phone
FROM (
   SELECT request_id
   FROM tableA
   GROUP BY request_id
   HAVING MAX(page_views) <= 0 AND MAX(step) <= 2
) AS sumQ
INNER JOIN tableA AS a ON sumQ.request_id = a.request_id
INNER JOIN tableB AS b ON a.request_id = b.id
;

或者，或者:

SELECT a.*, b.name, b.phone
FROM tableA AS a
INNER JOIN tableB AS b ON a.request_id = b.id
WHERE a.request_id IN (
     SELECT request_id
     FROM tableA
     GROUP BY request_id
     HAVING MAX(page_views) <= 0 AND MAX(step) <= 2
  )
;

根据我的经验，第一个版本通常更快.

In my experience, the first version is usually faster.

这篇关于SQL查询中的复杂分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！