MySQL-从表B引用时需要获取表A的最新信息 [英] Mysql - Need to get latest of table A when referenced from table B

问题描述

我正在构建用于踢的bug跟踪程序类型的工具. 我的问题有一个小问题，与我的数据的版本控制有关.

I'm building a bug tracker type tool for kicks. I'm having probs with a small prob relating to version control of my data.

我有一个表格"action"，其中存储了有关该动作的所有数据(描述，输入者，状态等).我还有一个action_status表，其中状态每次更改(从未分配，进行中，完成等)都记录在这里. 我似乎无法做的是列出具有最新状态值的操作. 您会注意到，状态表有两行，一行已提交，其他尚未提交..我只想查看已提交的行= 0(我假定的最新日期.)

I have a table 'action' where I store all the data about the action (desription, who entered it, status etc). I also have a action_status table where each time the status is changed (from not asigned, in progress, complete etc) it is logged here.. What I can't seem to do is list the actions with their latest status value. You'll note that the status table has two rows, one has been submitted, the otehr has not.. I ONLY want to see the row that has submitted = 0 (the latest date I'd presume..)

更糟糕的是，每个动作都有一个修订ID，如果更改了动作文本，我将在动作表中创建一个具有相同ID的新条目，但使用一个新的修订ID. ..但我想我应该提一下，以免干扰我的问题.

to make matters worse, each action has a revision Id and if the action text is changed, I'm creating a new entry in the action table with the same ID, but a new revision ID.. this however is working great.. but I thought I should mention in case it's interfering with my problem.

这是我的表格和一些示例数据: 我是猴子吗?

Here are my tables and some sample data: Am I being a monkey?

CREATE TABLE IF NOT EXISTS `action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_action` int(11) NOT NULL,
  `id_priority` int(11) NOT NULL,
  `revision` int(11) NOT NULL DEFAULT '1',
  `reference` varchar(255) NOT NULL,
  `department` int(11) NOT NULL,
  `id_parent` int(11) NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL,
  `description` text NOT NULL,
  `date_start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `action`
--

INSERT INTO `action` (`id`, `id_action`, `id_priority`, `revision`, `reference`, `department`, `id_parent`, `sort_order`, `description`, `date_start`, `date_end`, `date_created`) VALUES
(1, 1, 1, 1, '1', 1, 0, 2, 'Test Action revision test 1 a', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(2, 1, 1, 2, '0', 1, 0, 2, 'Test Action revision test 1 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(3, 2, 1, 1, '0', 1, 0, 1, 'Test Action revision test 2 a', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(4, 2, 1, 2, '0', 1, 0, 1, 'Test Action revision test 2 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00'),
(5, 3, 2, 1, '0', 1, 0, 0, 'Test Action revision test 3 b', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2011-06-17 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `action_status`
--

CREATE TABLE IF NOT EXISTS `action_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_department` int(11) NOT NULL,
  `id_priority` int(11) NOT NULL,
  `id_action` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `submitted` tinyint(4) NOT NULL,
  `approved` tinyint(4) NOT NULL,
  `published` tinyint(4) NOT NULL,
  `date_now` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `action_status`
--

INSERT INTO `action_status` (`id`, `id_department`, `id_priority`, `id_action`, `status`, `submitted`, `approved`, `published`, `date_now`) VALUES
(1, 1, 1, 2, 3, 1, 1, 1, '2011-06-20 16:36:09'),
(2, 1, 1, 2, 5, 0, 0, 0, '2011-06-20 16:40:09');


CREATE TABLE IF NOT EXISTS `priority` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `priority`
--

INSERT INTO `priority` (`id`, `description`) VALUES
(1, 'Test Priority'),
(2, '2nd Priority');

还有我的问题" SQL

And my 'problem' SQL

SELECT `action`.`id_priority`, `priority`.`description` as priority, `action`.`reference`, `action`.`description` as action, `action`.`id_action`, `action`.`date_start`, `action`.`date_end`, `action`.`id_parent`, `action_status`.`status`, `action`.`revision`, `action_status`.`submitted`, `action_status`.`date_now`

FROM (`action`)

LEFT JOIN action_status ON
  `action_status`.`id_action` = `action`.`id_action`

JOIN `priority` ON
  `action`.`id_priority` = `priority`.`id`

WHERE
action.department = 1 AND
action.revision =(SELECT MAX(ar.revision) FROM action as ar WHERE action.id_action = ar.id_action)

GROUP BY `action`.`id_action`

ORDER BY `id_priority` asc, `id_parent` asc, `sort_order` asc

推荐答案

如果我理解得很好，而您只需要使用最高修订版本的操作，请尝试以下操作:

If I understood it well and you need only actions with max revision, try something like this:

SELECT *

FROM

    (SELECT `action`.`id_action`, max(`action`.`revision`), 
        (select id 
         from action as a 
         where a.id_action = action.id_action 
         order by revision desc 
         limit 1) as id_with_max_revision

    FROM `action`

    WHERE
    action.department = 1

    GROUP BY `action`.`id_action`

    ) as action_max_revision
JOIN action on action_max_revision.id_with_max_revision = action.id
LEFT JOIN action_status ON
      `action_status`.`id_action` = `action`.`id_action`

JOIN `priority` ON
      `action`.`id_priority` = `priority`.`id`
ORDER BY `id_priority` asc, `id_parent` asc, `sort_order` asc

内部查询选择具有最高修订版本的操作，而外部查询则进行其他的别开玩笑"，这不是问题的核心:-)

The inner query selects actions with max revision, and the outer query does the other gimcrackery which is not the core of the problem :-)

这篇关于MySQL-从表B引用时需要获取表A的最新信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！