在 MySQL 中计算连接的列数

Question

在 MySQL 中计算连接的列数

mysqlcountdistinctpivot-tableconcatenation

3

我希望做类似于这个的操作，但是对于另一种无法用函数实现的情况。

这是我的示例表格：

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

从inspection_report中按照Inspection_datetime日期和Model型号进行分组：

并得到如下结果：

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

这个查询已经接近答案了，但我无法计算接受和拒绝的结果并将其分组在同一日期内。请尝试我的查询。

- klox

你得到了什么答案？（是'accept' = 2，'reject' = 2吗？还是'accept' = 5，'reject' = 2？）为什么你期望'accept'和'reject'的值不同呢？ - Jonathan Leffler

我发布结果。我必须这样做，因为我需要一些每日批次判断。它计算了由lot_qty拥有的接受或拒绝的数量，或者从lot_qty中接受或拒绝的数量。 - klox

如果我不使用“SUM”，答案将会关闭。但是我需要计数它们。 - klox

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jagmag · Accepted Answer

答案：

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