我正在开发一个文档管理系统,但我似乎卡住了。
数据库包含所有文档编号(唯一)及其所有修订版本(A-B-C-等)。将文档发送到客户端时,我使用传递(文档 1234,修订版 B 与传递 6 一起发送到客户端 XYZ)。
现在,团队希望有一个概览(表格),他们可以在其中查看每个文档的最新修订版以及发送给客户端XYZ的最后一个修订版。像这样:
---------------------------------------------------------------------------------------
| Documents || Latest Revision || Last sent Revision |
| || revision || revision | transmittalnr | sent To | Reason |
-------------||-----------------||- ---------------------------------------------------
| || || | | | |
| [docnrlab] || [revision] || [revision] | [transmittalnr] | [userto_id] | [rsn] |
---------------------------------------------------------------------------------------
我一直试图通过一个查询来实现它,但由于我不是专家,我似乎没有完成它。我得到了什么(通过反复试验,这可能不是构建复杂查询的最佳方式)
/* all docs with al their revis */
SELECT * , revisiondate, docrevisions.id
FROM docrevisions
ORDER BY doc_id
/* for each doc: give the latest revi, which has a transmittal */
SELECT * , MAX( revisiondate ), docrevisions.id AS revi_id, transmittals2revisions . *
FROM docrevisions
LEFT JOIN transmittals2revisions ON docrevisions.id = transmittals2revisions.revisions_id
LEFT JOIN transmittals ON transmittals2revisions.transmittal_id = transmittals.id
WHERE transmittals.id IS NOT NULL
GROUP BY doc_id
/* testing */
SELECT docrevisions.id AS sent, docrevisions.id AS unsent, documents.docnrlab
FROM docrevisions
INNER JOIN transmittals2revisions AS t2revi1 ON sent = t2revi1.revisions_id
INNER JOIN transmittals2revisions AS t2revi2 ON unsent = t2revi2.revisions_id
INNER JOIN documents ON docrevisions.doc_id = documents.id
WHERE t2revi1.revisions_id IS NOT NULL
AND t2revi2.revisions_id IS NULL
GROUP BY doc_id
ORDER BY docrevisions.doc_id ASC
在另一个查询中,我使用了别名,我认为这对我的问题很有帮助,但我不知道如何找到正确的别名:
$sql = "SELECT trm.*, userfrom.lastname AS UserFrom, userto.lastname AS UserTo
FROM transmittals AS trm
LEFT JOIN users AS userfrom ON userfrom.id = trm.userfrom_id
LEFT JOIN users AS userto ON userto.id = trm.userto_id";
以下是涉及的数据表的结构
CREATE SCHEMA IF NOT EXISTS `docguard` DEFAULT CHARACTER SET utf8 ;
USE `docguard` ;
-- -----------------------------------------------------
-- Table `docguard`.`documents`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `docguard`.`documents` (
`id` INT(10) NOT NULL AUTO_INCREMENT ,
`docnrsupplier` TEXT NULL DEFAULT NULL ,
`docnrlab` TEXT NULL DEFAULT NULL ,
`docnrclient` TEXT NULL DEFAULT NULL ,
`title1` TEXT NULL DEFAULT NULL ,
`title2` TEXT NULL DEFAULT NULL ,
`title3` TEXT NULL DEFAULT NULL ,
`title4` TEXT NULL DEFAULT NULL ,
`docsize_id` INT(10) NULL DEFAULT NULL ,
`docstate_id` INT(10) NULL DEFAULT NULL ,
`docformat_editable_id` INT(10) NULL DEFAULT NULL ,
`docformat_noneditable_id` INT(10) NULL DEFAULT '0' ,
`file_editable` TEXT NULL DEFAULT NULL ,
`file_noneditable` TEXT NULL DEFAULT NULL ,
`doccat_id` INT(10) NULL DEFAULT NULL ,
`docphase_id` INT(10) NULL DEFAULT NULL ,
`doctype_id` INT(10) NULL DEFAULT NULL ,
`company_id` INT(10) NULL DEFAULT NULL ,
`docrevisions_id` INT(10) NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_documents_docrevisions_idx` (`docrevisions_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 778
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `docguard`.`docrevisions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `docguard`.`docrevisions` (
`id` INT(10) NOT NULL AUTO_INCREMENT ,
`doc_id` INT(10) NOT NULL ,
`revision` TEXT NOT NULL ,
`revisiondate` DATE NULL DEFAULT NULL ,
`user_id` INT(10) NULL DEFAULT NULL ,
`transmittals2revisions_id` INT(10) NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_docrevisions_transmittals2revisions1_idx` (`transmittals2revisions_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 1199
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `docguard`.`transmittals2revisions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `docguard`.`transmittals2revisions` (
`id` INT(10) NOT NULL AUTO_INCREMENT ,
`transmittal_id` INT(10) NOT NULL ,
`revisions_id` INT(10) NULL DEFAULT NULL ,
`quantity` TINYINT(2) NOT NULL DEFAULT '1' ,
`rsn` TINYINT(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`id`) )
ENGINE = MyISAM
AUTO_INCREMENT = 607
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `docguard`.`transmittals`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `docguard`.`transmittals` (
`id` INT(10) NOT NULL AUTO_INCREMENT ,
`transmittalnr` TEXT NOT NULL ,
`userfrom_id` INT(10) NULL DEFAULT NULL ,
`userto_id` INT(10) NULL DEFAULT NULL ,
`shippingtimestamp` DATE NULL DEFAULT NULL ,
`replybeforetimestamp` DATE NULL DEFAULT NULL ,
`note` TEXT NULL DEFAULT NULL ,
`tempname` TINYINT(1) NOT NULL DEFAULT '0' ,
`transmittals2revisions_id` INT(10) NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_transmittals_transmittals2revisions1_idx` (`transmittals2revisions_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 52
DEFAULT CHARACTER SET = utf8;
我很抱歉这篇长文,但我希望有人能够帮助我或指出我正确的方向。提前感谢!
在这里,您需要执行两个分析查询:
-
要在所选文档的所有修订中查找具有最新日期的修订,请执行以下操作:
SELECT dr.doc_id, dr.id, MAX(dr.revisiondate) FROM docrevisions dr GROUP BY dr.doc_id
-
要在传输给用户 XYZ 的所有选定文档的修订中查找具有最新日期的修订,请执行以下操作:
SELECT dr.doc_id, dr.id, MAX(tr.shippingtimestamp) FROM transmittals2revisions t2r, transmittals tr, docrevisions dr WHERE t2r.transmittal_id = tr.id AND dr.id = t2r.revisions_id GROUP BY dr.doc_id
因此,我们可以将它们组合成一个查询:
SELECT d.id doc_id,
rl.id last_revision_id,
rlt.id last_trans_revision_id,
rlt.transmittalnr,
rlt.userto_id,
rlt.rsn
FROM documents d,
(
SELECT dr.doc_id, dr.id, MAX(dr.revisiondate)
FROM docrevisions dr
GROUP BY dr.doc_id
) rl,
(
SELECT dr.doc_id, dr.id,
tr.transmittalnr, tr.userto_id,
t2r.rsn,
MAX(tr.shippingtimestamp)
FROM transmittals2revisions t2r,
transmittals tr,
docrevisions dr
WHERE t2r.transmittal_id = tr.id
AND dr.id = t2r.revisions_id
AND tr.userfrom_id = 1
GROUP BY dr.doc_id
) rlt
where d.id = rl.doc_id
AND d.id = rlt.doc_id;
其中 d.id - 文件,rl.id - 上次修订,rlt.id - 上次传输的修订。据我了解运输时间戳 - 传输时间。
谢廖夫为我指出了正确的解决方案,所以非常感谢他。我将在这里写下我如何用他的建议解决我的问题:
要在所有选定文档的修订版中查找具有最新日期的修订版 - 从上面更改,因为我也需要实际修订版(A-B-C-等):
SELECT d.docnrlab, dr.* FROM documents d, docrevisions AS dr INNER JOIN ( SELECT dr.id, dr.doc_id, MAX( revisiondate ) AS revisiondate FROM docrevisions AS dr GROUP BY doc_id ) AS CurrentRevision ON CurrentRevision.doc_id = dr.doc_id AND CurrentRevision.revisiondate = dr.revisiondate WHERE d.id = dr.doc_id
要在传输给用户XYZ的所有选定文档的修订中找到具有最新日期的修订 - 我没有提到可能根本没有发送文档。
SELECT dr.id, dr.doc_id, dr.revision , MAX( revisiondate ), tr.transmittalnr, tr.userto_id, t2r.rsn, MAX(tr.shippingtimestamp) FROM docrevisions dr LEFT JOIN transmittals2revisions AS t2r ON dr.id = t2r.revisions_id LEFT JOIN transmittals AS tr ON t2r.transmittal_id = tr.id /*WHERE tr.id IS NOT NULL*/ GROUP BY doc_id
/* 现在都在一起 */
SELECT d.id doc_id,
d.docnrlab doc_nr_lab,
rl.id last_revision_id,
rl.revision last_revision,
rlt.id last_trans_revision_id,
rlt.revision last_trans_revision,
rlt.transmittalnr,
rlt.userto_id,
rlt.rsn
FROM documents d,
(
SELECT d.docnrlab, dr.*
FROM documents d, docrevisions AS dr
INNER JOIN
(
SELECT dr.id, dr.doc_id, MAX( revisiondate ) AS revisiondate
FROM docrevisions AS dr
GROUP BY doc_id
)
AS CurrentRevision
ON CurrentRevision.doc_id = dr.doc_id
AND CurrentRevision.revisiondate = dr.revisiondate
WHERE d.id = dr.doc_id
) rl,
(
SELECT dr.id, dr.doc_id, dr.revision , MAX( revisiondate ),
tr.transmittalnr, tr.userto_id,
t2r.rsn,
MAX(tr.shippingtimestamp)
FROM docrevisions dr
LEFT JOIN transmittals2revisions AS t2r ON dr.id = t2r.revisions_id
LEFT JOIN transmittals AS tr ON t2r.transmittal_id = tr.id
/*WHERE tr.id IS NOT NULL*/
GROUP BY doc_id
) rlt
WHERE d.id = rl.doc_id
AND d.id = rlt.doc_id;