计数mysql中的连接列

概述我想做this,但对于另一种情况却无法正常工作.这是我的示例表：Date Model No Line Range Lot Status 2010-08-01 KD-G435 1 01 1-100 013A accep

我想做this,但对于另一种情况却无法正常工作.
这是我的示例表：

Date         Model        No      line       Range       Lot        Status2010-08-01   KD-G435      1       01         1-100       013A       accept2010-08-01   KD-G435      2       01         1-100       013A       accept2010-08-01   KW-TC800     1       01         1-200       001A       null2010-08-01   KW-TC800     2       01         1-200       001A       null2010-08-01   KW-TC800     3       01         1-200       001A       null2010-08-01   KD-R411      1       05         1-100       021A       reject2010-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  reject2010-08-01                  KD-G435UND        1     1   02010-08-01                  kd-r411ed         1     0   12010-08-01                  KW-TC800UND       1     1   02010-09-01                  KD-G435UND        1     1   02010-09-01                  kd-r411ed         2     0   12010-09-02                  KD-G435UND        1     1   02010-10-01                  KW-TC800UND       1     1   0

我想制作一张像这样的桌子：

Date          lot_qty        accept        reject2010-08-01    3              2             1         //count in same date become one2010-09-01    3              1             1       //count in same date become one     2010-09-02    1              1             02010-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) XGROUP BY X.InsDate

总结

以上是内存溢出为你收集整理的计数mysql中的连接列 全部内容，希望文章能够帮你解决计数mysql中的连接列 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。