mysql - 从表 A 中选择有限的最大值并按表 B 排序

Question

我很难编写解决此问题的查询。我的表看起来像:

表A

id | u_id | open | date
---+------+------+------------
 1 |    8 |   2  | 2017-07-20 (BBB)
 2 |  101 |   1  | 2017-07-20 (MMM)
 3 | 5475 |   3  | 2017-07-20 (WWW)
 4 |  147 |   1  | 2017-07-21 (PPP)
 5 |  149 |   1  | 2017-07-21 (QQQ)
 6 |  147 |   2  | 2017-07-16 (PPP)

(u_id being foreign key to Table B)

表B

 id  | name | amm
-----+------+---
   4 | AAA  | 4
   5 | BBB  | 5
 101 | MMM  | 3
 110 | NNN  | 3
 146 | OOO  | 3
 147 | PPP  | 6
 149 | QQQ  | 3
5475 | WWW  | 2

我要查找的结果需要应用以下规则:

1. 从表 B 中选择所有 id, names 和打开，日期 来自表 A(或 NULL，如果 id 不存在则为 NULL)
2. 每个名字只有 1 个结果，表 A 有 MAX(date)
3. WHERE:表 A 中 MAX(date) 上的open>1
4. 从中选择表 B 中最高 amm 的前 50 个
5. 返回按name字母顺序排序的结果

基本上:表 B 中的所有内容，不包括 表 A 中的 ID，其中 open 来自该 ID 的最大日期等于 1。

假设我们将选择限制为 4，然后在规则 4 之后。结果将是:

id   | name | open | date
-----+------+------+-----------
   5 | BBB  |  2   | 2017-07-20 //open>1,   nr.2 highest amm,
   6 | AAA  | NULL | NULL       //not in A, nr.3 highest amm, 
 110 | NNN  | NULL | NULL       //not in A, nr.4 highest amm, 
 146 | OOO  | NULL | NULL       //not in A, nr.6 highest amm, 

 NOT SELECTING:
 MMM: open =1
 PPP: at LARGEST date => open =1
 WWW: outside LIMIT

最后，我希望结果按字母顺序排序:

id   | name | open | date
-----+------+------+-----------
   6 | AAA  | NULL | NULL       
   5 | BBB  |  2   | 2017-07-20
 110 | NNN  | NULL | NULL
 146 | OOO  | NULL | NULL

此刻，我有这个问题:

SELECT
    t3.id, 
    t3.name,
    t3.open,
    t3.amm,
    t3.date
    FROM(

    SELECT 
        t1.id, 
        t1.name,
        t1.amm,
        t2.open,
        MAX(t2.date) AS date
    FROM Table_B AS t1

    LEFT JOIN Table_A AS t2
    ON t1.id = t2.u_id
    AND (t2.open>1 OR t2.open IS NULL)

    GROUP BY t1.id
    ORDER BY t1.amm DESC,
    LIMIT 20
    ) AS t3
ORDER BY t3.name

这确实给我一个按 amm 和 name 排序的结果。它不做的是正确过滤date/open 组合。它只采用它为某些 u_id 找到的最后一个 date，而不是 MAX(date) 及其对应的 open值(value)。

所以结果仍然包含:

 147 | PPP  |  2  | 2017-07-16

尽管还有一个

 147 | PPP  |  1  | 2017-07-19

这是一个SQLfiddle .PPP 在结果中，不应该在的地方。

Answer 1

我已经放弃尝试从这 block 特殊的石头上抽血了。

据我所知，您正在寻找以下内容；我已将表格重命名为更易理解的内容。

对于最后一部分，我只是在应用程序代码中对结果数组进行排序...

DROP TABLE IF EXISTS log;

CREATE TABLE log
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,u_id INT NOT NULL
,open TINYINT NOT NULL
,date DATE NOT NULL
);

INSERT INTO log VALUES
(1,   8,2,'2017-07-20'),
(2, 101,1,'2017-07-20'),
(3,5475,3,'2017-07-20'),
(4, 147,1,'2017-07-21'),
(5, 149,1,'2017-07-21'),
(6, 147,2,'2017-07-16');

DROP TABLE IF EXISTS users;

CREATE TABLE users
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,amm TINYINT NOT NULL
);

INSERT INTO users VALUES
(   4,'AAA',4),
(   5,'BBB',5),
( 101,'MMM',3),
( 110,'NNN',3),
( 146,'OOO',3),
( 147,'PPP',6),
( 149,'QQQ',3),
(5475,'WWW',2);

SELECT * 
  FROM users u 
  LEFT 
  JOIN 
     ( SELECT x.*  
         FROM log x
         JOIN 
            ( SELECT u_id, MAX(date) date FROM log WHERE open > 1 GROUP BY u_id ) y
           ON y.u_id = x.u_id
          AND y.date = x.date
     ) l
    ON l.u_id = u.id
 ORDER 
    BY amm DESC LIMIT 50;

+------+------+-----+------+------+------+------------+
| id   | name | amm | id   | u_id | open | date       |
+------+------+-----+------+------+------+------------+
|  147 | PPP  |   6 |    6 |  147 |    2 | 2017-07-16 |
|    5 | BBB  |   5 | NULL | NULL | NULL | NULL       |
|    4 | AAA  |   4 | NULL | NULL | NULL | NULL       |
|  149 | QQQ  |   3 | NULL | NULL | NULL | NULL       |
|  101 | MMM  |   3 | NULL | NULL | NULL | NULL       |
|  110 | NNN  |   3 | NULL | NULL | NULL | NULL       |
|  146 | OOO  |   3 | NULL | NULL | NULL | NULL       |
| 5475 | WWW  |   2 |    3 | 5475 |    3 | 2017-07-20 |
+------+------+-----+------+------+------+------------+