我怎么能在3个关联表得到一个字段的值与日期的最大(how can I get a field val

所以，我有一个表，我想从一个领域获得以最大的记录值DateTime()在另一个字段的值并在另一个领域是等于某个值。

    --
    -- Structure de la table `site`
    --
    CREATE TABLE IF NOT EXISTS `site` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `site_name` varchar(15) NOT NULL,
      `city` varchar(50) DEFAULT NULL,
      `address` varchar(80) DEFAULT NULL,
      `entity` varchar(50) DEFAULT NULL,
      `status_activity` tinyint(1) NOT NULL DEFAULT '1',
      `infra` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `idx_site_name` (`site_name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

    --
    -- Contenu de la table `site`
    --

    INSERT INTO `site` (`id`, `site_name`, `city`, `address`, `entity`, `status_activity`, `infra`) VALUES
    (1, 'FR001', 'tttt', 'tttttttttt', 'yyyyy', 1, 0),
    (2, 'FR002', 'ccccc', 'cccccccccc', 'rrrrrrrrrrrr', 1, 0);

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

    --
    -- Structure de la table `site_topology`
    --

    CREATE TABLE IF NOT EXISTS `site_topology` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `id_site` int(10) unsigned NOT NULL,
      `id_topology` int(10) unsigned NOT NULL,
      `date` date NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_site_date` (`date`),
      KEY `fk_id_site_2` (`id_site`),
      KEY `fk_id_topology_2` (`id_topology`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

    --
    -- Contenu de la table `site_topology`
    --

    INSERT INTO `site_topology` (`id`, `id_site`, `id_topology`, `date`) VALUES
    (1, 1, 1, '2015-03-03'),
    (2, 2, 3, '2015-03-03'),
    (3, 1, 2, '2015-04-30'),
    (4, 2, 5, '2015-04-30'),
    (5, 1, 1, '2015-06-25'),
    (6, 2, 4, '2015-06-25');

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

    --
    -- Structure de la table `topology`
    --

    CREATE TABLE IF NOT EXISTS `topology` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(10) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_type_name` (`name`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

    --
    -- Contenu de la table `topology`
    --

    INSERT INTO `topology` (`id`, `name`) VALUES
    (3, 'C1/C2'),
    (2, 'C3'),
    (5, 'HBN'),
    (4, 'Infrastruc'),
    (6, 'LB'),
    (1, 'SHBN');

    --
    -- Contraintes pour les tables exportées
    --

    --
    -- Contraintes pour la table `site_topology`
    --
    ALTER TABLE `site_topology`
      ADD CONSTRAINT `fk_id_site_2` FOREIGN KEY (`id_site`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
      ADD CONSTRAINT `fk_id_topology_2` FOREIGN KEY (`id_topology`) REFERENCES `topology` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

我想看看

FR001 SHBN 2015-06-26
FR002 Infrastruct 2015-06-26

但是当我执行我的查询我有这个

FR001 SHBN 2015-06-26`enter code here`
FR002 C1/C2 2015-06-26

谁能帮我？

Answer 1:

您对如何选择最多的日期问题，自本月初被首次展示。另一种方法是， 弹出MAX_DATE值 （从一个子查询）。这之后，您可以通过日期的不同部分进行比较 。代码如下：

select site_name, site_date, name 
 from
  (
    select  site_name, st.date as site_date, name, s.id, (select max(sto.date) from site_topology sto) as max_date
    from site s 
     inner join site_topology st on s.id =st.id_site
     inner join topology t on st.id_topology = t.id 
  )  as v
 where YEAR(site_date) = YEAR(max_date) AND MONTH(site_date) = MONTH(max_date) AND DAY(site_date) = DAY(max_date)
 group by name
 order by site_name

这里测试。

Answer 2:

使用子查询他们是正确的聚集在这里结果

select site_name,max(date), name 
   from
    (select  site_name, 
     city, address, entity , status_activity, infra,
     st.*,name
     from site s 
       inner join site_topology st on s.id =st.id_site
       inner join topology t on st.id_topology = t.id ) 
     as v
   group by site_name