我已经用mySQL(和php)实现了版本控件。
看起来如下:
categories
-------------
| id | name |
|----|-------
| 1 | a
| 2 | b
| 3 | c
| 4 | d
-------------
revisions
----------------------
| id | cid | current |
|----|-----|----------
| 1 | 1 | 1 |
| 2 | 1 | NULL |
| 3 | 2 | NULL |
| 4 | 3 | 1 |
| 5 | 4 | NULL |
| 6 | 4 | 1 |
----------------------
每个类别都分配了多个修订。设置修订,如果"当前"设置为1(每个类别只能为1个修订版,所有其他修订版本为null)。我现在想要的是获取每个类别,该类别具有新的重新分配(请注意,一旦提交了修订后,它就不会立即设置为当前。这是由主持人来完成的)。我的问题现在也获得了每个类别,只有一个带有当前的修订为null
因此,预期的结果是:
-------------
| category.id
|------------
| 1
| 2
-------------
所有最好的
编辑://
我当前的解决方案是:
SELECT categories.* FROM categories
JOIN revisions as t1 ON
(
t1.cid = categories.id
t1.current IS NULL
)
JOIN revisions as t2 ON
(
t2.id != t1.id
t2.current IS NOT NULL
t2.cid = categories.id
)
WHERE t1.id > t2.id
我制作了一个带有不同表结构的SQL小提琴演示
CREATE TABLE `categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(1) NOT NULL DEFAULT '',
`revision_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO categories
(`id`, `name`, `revision_id`)
VALUES
(1, 'a', 1),
(2, 'b', null),
(3, 'c', 4),
(4, 'd', 6),
(5, 'e', 7),
(6, 'f', 9)
;
CREATE TABLE `revisions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO revisions
(`id`, `category_id`)
VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 3),
(5, 4),
(6, 4),
(7, 5),
(8, 6),
(9, 6)
;
外国密钥关系
ALTER TABLE `categories`
ADD CONSTRAINT `fk_cat_rev`
FOREIGN KEY (`revision_id`)
REFERENCES `revisions` (`id`) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE `revisions`
ADD CONSTRAINT `fk_rev_cat`
FOREIGN KEY (`category_id`)
REFERENCES `categories` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
检查删除的关系
DELETE FROM revisions WHERE id = 7;
DELETE FROM categories WHERE id = 6;
SELECT
cid
FROM (
SELECT
c.id cid,
MAX( r.id ) mrid,
c.revision_id rid
FROM
categories c
JOIN
revisions r ON r.category_id = c.id
GROUP BY
c.id ) tmp
WHERE
COALESCE( mrid, 0 ) <> COALESCE( rid, 0 );
SQL小提琴演示