计数mysql中的连接列

我想做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`,`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','NG','2ver-m302',25158,1,'accept'),(78,'135X0001','2010-08-01 14:24:35',(77,'KW-TC800UND','135X0003','011A','2010-08-01 09:12:01','TEST',''),(76,'2010-08-01 09:10:01',(75,'2010-08-01 09:08:01',(63,'KD-R411ED','022A','FA 05','2010-08-01 16:24:04','135V0001-135V0200','ver-r105','reject'),(65,'kd-r411ed','135x0002','022a','a','135v0001-135v0200','ok','ng',(66,'023A','2010-09-02 14:24:35',(67,'025A','FA 07','2010-10-01 09:08:01',(80,'013A','FA 02','2010-09-01 14:24:35','135X0001-135X0200',(81,'2010-09-01 14:28:35',(82,'014a','fa 03','2010-09-01 09:08:01',(83,'015A','2010-09-01 16:24:04','135X9901-135V0000','reject');

编辑

我试过这个查询：

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

来自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

这个查询几乎接近答案,但是我无法计算接受和拒绝的结果,然后在同一日期进行分组.

最佳答案

回答：

SELECT X.InsDate,SUM(X.lot_qty),SUM(X.accept),SUM(X.reject)
FROM 
   (SELECT 
        Date(Inspection_datetime) as InsDate,0) AS reject
    FROM inspection_report 
    GROUP BY Date(Inspection_datetime),Range_sampling,Lot_no) X
GROUP BY X.InsDate

计数mysql中的连接列

猜你在找的MySQL相关文章