我会尽力为您进行翻译,以下是需要翻译的内容:
我试图通过下面写的基本示例来简化我的问题,实际问题要复杂得多,因此下面的查询可能没有太多意义,但基本概念是相同的(从一个查询中获取数据作为另一个查询的参数)。
查询1:
SELECT Ping.ID as PingID, Base.ID as BaseID FROM
(SELECT l.ID, mg.DateTime from list l
JOIN mygroup mg ON mg.ID = l.MyGroup
WHERE l.Type = "ping"
ORDER BY l.ID DESC
) Ping
INNER JOIN
(SELECT l.ID, mg.DateTime from list l
JOIN mygroup mg ON mg.ID = l.MyGroup
WHERE l.Type = "Base"
ORDER BY l.ID DESC
) Base
ON Base.DateTime < Ping.DateTime
GROUP BY Ping.ID
ORDER BY Ping.ID DESC;
+--------+--------+
| PingID | BaseID |
+--------+--------+
| 11 | 10 |
| 9 | 8 |
| 7 | 6 |
| 5 | 3 |
| 4 | 3 |
+--------+--------+
// 我需要将下面的11替换为上面的PingID,将10替换为上面的BaseID,然后将结果显示在第三列上方(如果没有结果,则为0,如果有结果,则为1)
查询2:
SELECT * FROM
(SELECT sl.Data FROM list l
JOIN sublist sl ON sl.ParentID = l.ID
WHERE l.Type = "ping" AND l.ID = 11) Ping
INNER JOIN
(SELECT sl.Data FROM list l
JOIN sublist sl ON sl.ParentID = l.ID
WHERE l.Type = "base" AND l.ID = 10) Base
ON Base.Data < Ping.Data;
我该怎么做呢?再次声明,我不确定会得到什么样的建议,但请理解查询2实际上有超过200行,并且基本上无法修改,因此我没有太多的灵活性。 理想情况下,我希望能够在SQL中完成所有工作,而无需编写脚本。
CREATE DATABASE lookback;
use lookback;
CREATE TABLE mygroup (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DateTime DateTime
) ENGINE=InnoDB;
CREATE TABLE list (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Type VARCHAR(255),
MyGroup BIGINT NOT NULL,
Data INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE sublist (
ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID BIGINT NOT NULL,
Data INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO mygroup (DateTime) VALUES ("2012-03-09 22:33:19"), ("2012-03-09 22:34:19"), ("2012-03-09 22:35:19"), ("2012-03-09 22:36:19"), ("2012-03-09 22:37:19"), ("2012-03-09 22:38:19"), ("2012-03-09 22:39:19"), ("2012-03-09 22:40:19"), ("2012-03-09 22:41:19"), ("2012-03-09 22:42:19"), ("2012-03-09 22:43:19");
INSERT INTO list (Type, MyGroup, Data) VALUES ("ping", 1, 4), ("base", 2, 2), ("base", 3, 4), ("ping", 4, 7), ("ping", 5, 8), ("base", 6, 7), ("ping", 7, 8), ("base", 8, 3), ("ping", 9, 10), ("base", 10, 2), ("ping", 11, 3);
INSERT INTO sublist (ParentID, Data) VALUES (1, 2), (2, 3), (3, 6), (4, 8), (5, 4), (6, 5), (7, 1), (8, 9), (9, 11), (10, 4), (11, 6);