代码之家 › 专栏 › 技术社区 › paul724

使用MIN和subquery的MySQL挑战

subquery mysql

paul724 · 技术社区 · 8 年前

我的目的是返回基于所选年份的日期值,以及基于包含所有年份的完整数据集的数据集的最小日期值。查询始终返回2017年的最小日期值。我希望它返回整个数据集的最小start\u日期。

我得到的是min\u date\u over\u all\u years

orgA    2017-10-09
orgB    2017-10-08

min\u date\u over\u all\u years的要求结果为

orgA        2015-10-10 
orgB        2014-10-09

请参见随附的小提琴示例: http://sqlfiddle.com/#!9/c0f74/9

架构为:

CREATE TABLE IF NOT EXISTS `project` (
  `project_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(10) NOT NULL,
  `start_date` DATE NOT NULL,
  `organisation_id` int(11) NOT NULL,
  PRIMARY KEY (`project_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `project` (`project_id`, `p_name`, 
`start_date`, `organisation_id`)
VALUES
(1, 'testP1', '2017-10-09', 1),
(2, 'testP2', '2016-10-10', 1),
(3, 'testP3', '2015-10-10', 1),
(4, 'testP4', '2017-10-10', 2),
(5, 'testP5', '2014-10-10', 2),
(6, 'testP6', '2017-10-10', 1),
(7, 'testP7', '2016-10-10', 1),
(8, 'testP8', '2015-10-10', 1),
(9, 'testP9', '2017-10-08', 2),
(10, 'testP10', '2014-10-09', 2);

CREATE TABLE IF NOT EXISTS `organisation` (`organisation_id` int(11) NOT NULL AUTO_INCREMENT,
`org_name` varchar(10) NOT NULL, 
 PRIMARY KEY (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `organisation` (`organisation_id`, `org_name` 
)
 VALUES
(1, 'orgA'),
(2, 'orgB');

我尝试过的查询(以及更简单的子查询和Case版本)是:

SELECT o.org_name, MIN(p.start_date) AS min_date_2017, YEAR(p.start_date) AS year_selected,
(SELECT MIN(p.start_date) FROM project p2
INNER JOIN organisation o2 ON o2.organisation_id = p2.organisation_id
 WHERE p2.organisation_id = o.organisation_id
GROUP BY o2.organisation_id) AS min_date_over_all_years
FROM organisation o
INNER JOIN project p on p.organisation_id = o.organisation_id
WHERE YEAR(p.start_date)=2017
GROUP BY o.organisation_id

1 回复 | 直到 8 年前

Barmar 8 年前

不能将返回多行的子查询放入 SELECT 列表当子查询用作表达式时,它必须返回一行一列。

您不需要单独的查询。

SELECT o.org_name,
        MIN(IF(YEAR(p.start_date) = 2017, p.start_date, NULL)) AS min_date_2017,
        2017 AS year_selected,
        MIN(p.start_date) AS min_date_over_all_years
FROM organisation AS o
INNER JOIN project AS p ON p.organisation_id = o.organisation_id
GROUP BY o.organisation_id

您还可以与获取总体数据的子查询联接。

SELECT o.org_name, MIN(p.start_date) AS min_date_2017, YEAR(p.start_date) AS year_selected, overall.start_date AS min_date_over_all_years
FROM organisation o
INNER JOIN project p on p.organisation_id = o.organisation_id
INNER JOIN (
    SELECT organisation_id, MIN(start_date) AS start_date
    FROM project 
    GROUP BY organisation_id) AS overall ON o.organisation_id = overall.organisation_id
WHERE YEAR(p.start_date)=2017
GROUP BY o.organisation_id