sqlite2：连接另一个表中每列的最大值（子查询参考）？

Question

我正在使用以下数据库：

CREATE TABLE datas (d_id INTEGER PRIMARY KEY, name_id numeric, countdata numeric);
INSERT INTO datas VALUES(1,1,20);  //(NULL,1,20);
INSERT INTO datas VALUES(2,1,47);  //(NULL,1,47);
INSERT INTO datas VALUES(3,2,36);  //(NULL,2,36);
INSERT INTO datas VALUES(4,2,58);  //(NULL,2,58);
INSERT INTO datas VALUES(5,2,87);  //(NULL,2,87);
CREATE TABLE names (n_id INTEGER PRIMARY KEY, name text);
INSERT INTO names VALUES(1,'nameA'); //(NULL,'nameA');
INSERT INTO names VALUES(2,'nameB'); //(NULL,'nameB');

我想做的是选择 names 的所有值（行） - 将附加 datas 的所有列，对于 datas.countdata 为 n_id 最大值的行（当然，其中 name_id = n_id）。

我可以通过以下查询到达那里：

sqlite> .header ON

sqlite> SELECT * FROM names AS n1 
    LEFT OUTER JOIN (
        SELECT d_id, name_id, countdata FROM datas AS d1 
        WHERE d1.countdata IN (
            SELECT MAX(countdata) FROM datas 
            WHERE name_id=1 
            ) 
        ) AS p1 ON n_id=name_id;

n1.n_id|n1.name|p1.d_id|p1.name_id|p1.countdata
1|nameA|2|1|47
2|nameB|||

...但是 - 显然 - 它仅适用于单行（由 name_id=1 显式设置的行）。

问题是，每当我尝试以某种方式引用“当前”n_id 时，SQL 查询都会失败：

sqlite> SELECT * FROM names AS n1 
    LEFT OUTER JOIN (
        SELECT d_id, name_id, countdata FROM datas AS d1 
        WHERE d1.countdata IN (
            SELECT MAX(countdata) FROM datas 
            WHERE name_id=n1.n_id 
            ) 
        ) AS p1 ON n_id=name_id;

SQL error: no such column: n1.n_id

有什么方法可以在 Sqlite2 中实现我想要的功能吗？

预先感谢，

干杯！

Answer 1

哦，好吧 - 这根本不是小事，但这里有一个解决方案：

sqlite> SELECT * FROM names AS n1 
        LEFT OUTER JOIN ( 
            SELECT d1.* 
            FROM datas AS d1, ( 
                SELECT max(countdata) as countdata,name_id 
                FROM datas 
                GROUP BY name_id
                ) AS ttemp 
            WHERE d1.name_id = ttemp.name_id AND d1.countdata = ttemp.countdata 
        ) AS p1 ON n1.n_id=p1.name_id;


n1.n  n1.name       p1.d_id     p1.name_id  p1.countdata                       
----  ------------  ----------  ----------  -----------------------------------
1     nameA         2           1           47                                 
2     nameB         5           2           87                                 

 
 

好吧，希望这最终能帮助别人，:)
干杯!

 
 
 

注释：请注意，仅调用 max(countdata) 就会完全搞砸 d_id：

sqlite> select d_id,name_id,max(countdata) as countdata from datas group by name_id;

d_id  name_id       countdata 
----  ------------  ----------
3     2             87        
1     1             47        

因此要获得正确的对应 d_id，我们必须执行 max () 分别对 datas - 然后与完整的 datas 执行某种相交（除了 sqlite 中的相交要求有相同数量的两个数据集中的列，这里的情况并非如此 - 即使我们这样做，如上所示，d_id 也会是错误的，因此 intersect 将不起作用）。

实现此目的的一种方法是使用某种临时表，然后利用多表 SELECT 查询，以便在完整数据和通过max(countdata)返回的子集之间设置条件，如下所示：

sqlite> CREATE TABLE ttemp AS SELECT max(countdata) as countdata,name_id FROM datas GROUP BY name_id;
sqlite> SELECT d1.*, ttemp.* FROM datas AS d1, ttemp WHERE d1.name_id = ttemp.name_id AND d1.countdata = ttemp.countdata;

d1.d  d1.name_id    d1.countda  ttemp.coun  ttemp.name_id                      
----  ------------  ----------  ----------  -----------------------------------
2     1             47          47          1                                  
5     2             87          87          2                                  

sqlite> DROP TABLE ttemp;

或者，我们可以重写上面的内容，使用 SELECT 子查询（子选择？），如下所示：

sqlite> SELECT d1.* FROM datas AS d1, (SELECT max(countdata) as countdata,name_id FROM datas GROUP BY name_id) AS ttemp WHERE d1.name_id = ttemp.name_id AND d1.countdata = ttemp.countdata;

d1.d  d1.name_id    d1.countda
----  ------------  ----------
2     1             47        
5     2             87