mysql join从3个表中获取数据，添加时间和int，将错误作为总和值的倍数

Question

我需要获取飞机的名称(aircraft_type)、单架飞机的飞行时间总和(飞机_master上的标志A)、单架飞机支付的费用总和(费用表下的ac_id)

使用查询作为

select
  t1.aircraft_type,
  sec_to_time(sum(time_to_sec(t2.ft_duration))),
  sum(t3.fee_amount)
from
  aircraft_master t1 
  left join flying_test t2 on t2.ac_id = t1.ac_id
  left join fees_detail t3 on t3.ac_id = t1.ac_id
where
  t2.per_detail_id = '12'
  and t3.per_detail_id = '12'
group by
  t1.ac_id]

没有加入flying_test，我得到了正确的飞行时间；无需付费加入，我就可以为每架飞机支付正确的金额。使用两者我可以获得数倍的时间和费用。

SQL 表和数据:

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 11, 2012 at 11:07 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `aviation`
--

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

--
-- Table structure for table `aircraft_master`
--

CREATE TABLE IF NOT EXISTS `aircraft_master` (
  `ac_id` int(10) NOT NULL AUTO_INCREMENT,
  `aircraft_type` varchar(50) DEFAULT NULL,
  `aircraft_engine` varchar(150) DEFAULT NULL,
  `flag` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`ac_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `aircraft_master`
--

INSERT INTO `aircraft_master` (`ac_id`, `aircraft_type`, `aircraft_engine`, `flag`) VALUES
(1, NULL, 'Single Engine', 'E'),
(2, NULL, 'Multi Engine', 'E'),
(3, 'CH2T', '1', 'A'),
(4, 'C172', '1', 'A'),
(5, 'C152', '1', 'A'),
(6, 'VT-SMT', '3', 'R'),
(7, 'VT-MDU', '3', 'R'),
(8, 'VT-RIT', '3', 'R'),
(9, 'VT-AFA', '4', 'R'),
(10, 'VT-TSM', '4', 'R'),
(11, 'VT-AFR', '4', 'R'),
(12, 'VT-RRM', '4', 'R'),
(13, 'VT-JSN', '4', 'R'),
(14, 'VT-AKN', '5', 'R'),
(15, 'VT-PBA', '5', 'R'),
(16, 'VT-AKA', '5', 'R'),
(17, 'VT-AFN', '5', 'R'),
(18, 'VT-AFN', '5', 'R'),
(19, 'VT-EMO', '5', 'R'),
(20, 'VT-EMP', '5', 'R'),
(21, 'VT-RJT', '3', 'R');

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

--
-- Table structure for table `fees_detail`
--

CREATE TABLE IF NOT EXISTS `fees_detail` (
  `fee_id` int(11) NOT NULL AUTO_INCREMENT,
  `per_detail_id` int(10) NOT NULL,
  `fee_detail_id` varchar(10) NOT NULL,
  `fee_date` date DEFAULT NULL,
  `mode_id` int(11) DEFAULT NULL,
  `fee_amount` int(10) DEFAULT NULL,
  `bank_id` int(20) DEFAULT NULL,
  `bank_branch` varchar(100) DEFAULT NULL,
  `chqdd_no` int(20) DEFAULT NULL,
  `fee_comment` varchar(150) DEFAULT NULL,
  `fee_posting_time` datetime NOT NULL,
  `ac_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`fee_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

--
-- Dumping data for table `fees_detail`
--

INSERT INTO `fees_detail` (`fee_id`, `per_detail_id`, `fee_detail_id`, `fee_date`, `mode_id`, `fee_amount`, `bank_id`, `bank_branch`, `chqdd_no`, `fee_comment`, `fee_posting_time`, `ac_id`) VALUES
(16, 12, '9', '2012-06-13', 87, 70000, NULL, NULL, NULL, 'c172', '2012-06-16 17:20:34', 4),
(17, 12, '10', NULL, NULL, 10000, NULL, NULL, NULL, NULL, '0000-00-00 00:00:00', 3),
(18, 12, '10', NULL, NULL, 10000, NULL, NULL, NULL, NULL, '2012-07-10 12:00:05', 3);

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

--
-- Table structure for table `flying_test`
--

CREATE TABLE IF NOT EXISTS `flying_test` (
  `ft_id` int(10) NOT NULL AUTO_INCREMENT,
  `ft_from` varchar(10) DEFAULT NULL,
  `ft_from_time` time DEFAULT NULL,
  `ft_to` varchar(10) DEFAULT NULL,
  `ft_to_time` time DEFAULT NULL,
  `ft_pic` varchar(50) DEFAULT NULL,
  `ft_engine` varchar(10) DEFAULT NULL,
  `per_detail_id` int(10) NOT NULL,
  `ft_remark` varchar(512) DEFAULT NULL,
  `ft_type` int(3) DEFAULT NULL,
  `ft_ftftpr` int(10) DEFAULT NULL,
  `ft_nature_exercise` int(3) DEFAULT NULL,
  `ftpr_ut` varchar(150) DEFAULT NULL,
  `ftpr_co_pilot` varchar(150) DEFAULT NULL,
  `ftpr_pi_us` varchar(150) DEFAULT NULL,
  `ft_status` int(3) DEFAULT NULL,
  `ft_aircraft` int(5) DEFAULT NULL,
  `ft_regno` int(3) DEFAULT NULL,
  `ft_landings` int(3) DEFAULT NULL,
  `ft_duration` time DEFAULT NULL,
  `fee_detail_id` int(10) DEFAULT NULL,
  `ft_act` time DEFAULT NULL,
  `ft_sim` time DEFAULT NULL,
  `ft_simulator` time DEFAULT NULL,
  `ft_dh` int(3) DEFAULT NULL,
  `ft_exercise` int(3) DEFAULT NULL,
  `ft_date` date DEFAULT NULL,
  PRIMARY KEY (`ft_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=83 ;

--
-- Dumping data for table `flying_test`
--

INSERT INTO `flying_test` (`ft_id`, `ft_from`, `ft_from_time`, `ft_to`, `ft_to_time`, `ft_pic`, `ft_engine`, `per_detail_id`, `ft_remark`, `ft_type`, `ft_ftftpr`, `ft_nature_exercise`, `ftpr_ut`, `ftpr_co_pilot`, `ftpr_pi_us`, `ft_status`, `ft_aircraft`, `ft_regno`, `ft_landings`, `ft_duration`, `fee_detail_id`, `ft_act`, `ft_sim`, `ft_simulator`, `ft_dh`, `ft_exercise`, `ft_date`) VALUES
(79, 'VISL', '10:00:00', 'VISL', '23:00:00', 'vcvc', '1', 12, 'dss', NULL, 16, 101, NULL, NULL, NULL, 20, 3, 8, 1, '15:30:00', 10, '00:00:00', '00:00:00', '00:00:00', 18, 38, '2012-07-07'),
(80, NULL, NULL, NULL, NULL, NULL, NULL, 12, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 4, NULL, NULL, '02:30:00', 9, NULL, NULL, NULL, NULL, NULL, '2012-07-25'),
(81, NULL, NULL, NULL, NULL, NULL, NULL, 12, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 3, NULL, NULL, '02:30:00', 10, NULL, NULL, NULL, NULL, NULL, '2012-07-25'),
(82, 'VISL', '15:30:00', 'VISL', '15:45:00', 'none', '1', 12, 'none', NULL, 16, 100, NULL, NULL, NULL, 20, 3, 6, 1, '00:15:00', NULL, '00:00:00', '00:00:00', '00:00:00', 18, 38, '2012-07-11');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer 1

您遇到一个经典问题，即将一个表连接到其他几个表，但需要部分聚合。

您需要通过加入子查询来解决这个问题:

select t1.aircraft_type,
       s1.val,
       sus2.fees
from (select t1.aircraft_type, sec_to_time(sum(time_to_sec(t2.ft_duration))) as val 
      from aircraft_master t1 left join
           flying_test t2
           on t2.ac_id = t1.ac_id
      where t2.per_detail_id = '12'
      group by t1.aircraft_type
     ) s1 left outer join
     (select ac_id, sum(t3.fee_amount) as fees
      from fees_detail
      where t3.per_detail_id = '13'
      group by ac_id
     ) s2
     on s2.ac_id = s1.ac_id

如果这不完全正确，我深表歉意。我的浏览器有问题，所以我希望你明白。