mysql 中连接列的计数

Question

我想要像 this 那样，但对于另一种不起作用的情况。 这是我的示例表：

Date         Model        No      Line       Range       Lot        Status
2010-08-01   KD-G435      1       01         1-100       013A       accept
2010-08-01   KD-G435      2       01         1-100       013A       accept
2010-08-01   KW-TC800     1       01         1-200       001A       null
2010-08-01   KW-TC800     2       01         1-200       001A       null
2010-08-01   KW-TC800     3       01         1-200       001A       null
2010-08-01   KD-R411      1       05         1-100       021A       reject
2010-08-01   KD-R411      2       05         1-100       021A       reject


    CREATE TABLE IF NOT EXISTS `inspection_report` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `Model` varchar(14) NOT NULL,
      `Serial_number` varchar(8) NOT NULL,
      `Lot_no` varchar(6) NOT NULL,
      `Line` char(5) NOT NULL,
      `Shift` char(1) NOT NULL,
      `Inspection_datetime` datetime NOT NULL,
      `Range_sampling` varchar(19) NOT NULL,
      `Packing` char(2) NOT NULL,
      `Accesories` char(2) NOT NULL,
      `Appearance` char(2) NOT NULL,
      `Tuner` char(2) NOT NULL,
      `General_operation` char(2) NOT NULL,
      `Remark` text NOT NULL,
      `NIK` int(5) NOT NULL,
      `S` int(11) NOT NULL,
      `A` int(11) NOT NULL,
      `B` int(11) NOT NULL,
      `C` int(11) NOT NULL,
      `Status` varchar(6) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `Model` (`Model`,`Serial_number`,`Lot_no`,`Line`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=84 ;

    --
    -- Dumping data for table `inspection_report`
    --

    INSERT INTO `inspection_report` (`id`, `Model`, `Serial_number`, `Lot_no`, `Line`, `Shift`, `Inspection_datetime`, `Range_sampling`, `Packing`, `Accesories`, `Appearance`, `Tuner`, `General_operation`, `Remark`, `NIK`, `S`, `A`, `B`, `C`, `Status`) VALUES
(79, 'KD-G435UND', '135X0002', '012A', 'FA 01', 'A', '2010-08-01 14:26:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 1, 1, 0, 'accept'),
(78, 'KD-G435UND', '135X0001', '012A', 'FA 01', 'A', '2010-08-01 14:24:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'),
(77, 'KW-TC800UND', '135X0003', '011A', 'FA 01', 'A', '2010-08-01 09:12:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''),
(76, 'KW-TC800UND', '135X0002', '011A', 'FA 01', 'A', '2010-08-01 09:10:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''),
(75, 'KW-TC800UND', '135X0001', '011A', 'FA 01', 'A', '2010-08-01 09:08:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''),
(63, 'KD-R411ED', '135X0001', '022A', 'FA 05', 'A', '2010-08-01 16:24:04', '135V0001-135V0200', 'OK', 'OK', 'NG', 'OK', 'OK', 'ver-r105', 25158, 0, 1, 0, 0, 'reject'),
(65, 'kd-r411ed', '135x0002', '022a', 'FA 05', 'a', '2010-08-01 09:08:01', '135v0001-135v0200', 'ok', 'ok', 'ng', 'ng', 'ok', 'ver-r105', 25158, 0, 1, 1, 0, 'reject'),
(66, 'KD-G435UND', '135X0001', '023A', 'FA 05', 'A', '2010-09-02 14:24:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'),
(67, 'KW-TC800UND', '135X0001', '025A', 'FA 07', 'A', '2010-10-01 09:08:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''),
(80, 'KD-G435UND', '135X0001', '013A', 'FA 02', 'A', '2010-09-01 14:24:35', '135X0001-135X0200', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'),
(81, 'KD-G435UND', '135X0002', '013A', 'FA 02', 'A', '2010-09-01 14:28:35', '135X0001-135X0200', 'OK', 'OK', 'OK', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 0, 0, ''),
(82, 'kd-r411ed', '135x0002', '014a', 'fa 03', 'a', '2010-09-01 09:08:01', '135v0001-135v0200', 'ok', 'ok', 'ng', 'ng', 'ok', 'ver-r105', 25158, 0, 1, 1, 0, 'reject'),
(83, 'KD-R411ED', '135X0001', '015A', 'FA 05', 'A', '2010-09-01 16:24:04', '135X9901-135V0000', 'OK', 'OK', 'NG', 'OK', 'OK', 'ver-r105', 25158, 0, 1, 0, 0, 'reject');

编辑

我尝试过这个查询：

SELECT Date(Inspection_datetime), Model,
   COUNT(DISTINCT(CONCAT(Range_sampling,Model,Line,Lot_no))) AS lot_qty,
   IF(Status !='reject',1,0) AS accept,
   IF(Status ='reject',1,0) AS reject

FROM Inspection_report GROUP BY Date(Inspection_datetime)，模型

并获得如下结果：

Date(Inspection_datetime)   Model         lot_qty   accept  reject
2010-08-01                  KD-G435UND        1     1   0
2010-08-01                  kd-r411ed         1     0   1
2010-08-01                  KW-TC800UND       1     1   0
2010-09-01                  KD-G435UND        1     1   0
2010-09-01                  kd-r411ed         2     0   1
2010-09-02                  KD-G435UND        1     1   0
2010-10-01                  KW-TC800UND       1     1   0

我想制作一个如下表：

Date          lot_qty        accept        reject
2010-08-01    3              2             1         //count in same date become one
2010-09-01    3              1             1       //count in same date become one     
2010-09-02    1              1             0
2010-10-01    1              1             0

此查询几乎接近答案，但我无法计算接受和拒绝结果，然后按同一日期分组。尝试我的查询。

Answer 1

回答：

SELECT X.InsDate, SUM(X.lot_qty), SUM(X.accept), SUM(X.reject)
FROM 
   (SELECT 
        Date(Inspection_datetime) as InsDate, 
        Model,
        COUNT(DISTINCT(CONCAT(Range_sampling,Model,Line,Lot_no))) AS lot_qty,
        IF(Status !='reject',1,0) AS accept,
        IF(Status ='reject',1,0) AS reject
    FROM inspection_report 
    GROUP BY Date(Inspection_datetime), Model, Line, Range_sampling, Lot_no) X
GROUP BY X.InsDate