MySQL 在单行中选择移位

Question

由于这是一个奇怪的问题，我找不到正确的答案。我正在使用 codeigniter 为我的公司开发一个内联网。我有一个查询语法，可以让我在给定的一天中获取所有产品：

$query = $this->db
         ->select('
             stock_meta.code_company AS product,
             stock_meta.company AS company,
             stock_meta.factory AS factory,
             COUNT(production.product) AS total_product
             ')
         ->from('production')
         ->join('stock_meta', 'production.product = stock_meta.code_local', 'inner')
         ->where('date BETWEEN ' . $start_date . ' AND ' . $end_date)
         ->group_by('product')
         ->order_by('factory')
         ->get();

这里的语法与 MySQL 一样：

SELECT
    stock_meta.code_company AS product,
    stock_meta.company AS company,
    stock_meta.factory AS total_product
FROM
    production
INNER JOIN
    stock_meta ON production.product = stock_meta.code_local
WHERE
    date BETWEEN 1304208650 AND 1304280234
GROUP BY
    product
ORDER BY
    factory

我只调用此查询一次。

我得到这样的结果：

| product  | company  | factory  | total_product  |
+----------+----------+----------+----------------+
| 231234   | A        | Fac1     | 475            |
| 245214   | A        | Fac2     | 246            |
+----------+----------+----------+----------------+

而且效果非常好。但我需要在轮班之间进行生产。树工作班次：00:00 - 08:00、08:00 - 16:00、16:00 - 24:00。我如何获得每种产品的轮班？

我的意思是我需要得到这样的结果：

| product  | company  | factory  | shift1  | shift2  | shift3  | total_product  |
+----------+----------+----------+---------+---------+---------+----------------+
| 231234   | A        | Fac1     | 100     | 200     | 175     | 475            |
| 245214   | A        | Fac2     | 46      | 50      | 150     | 246            |
| 500231   | B        | aFaca1   | 46      | 50      | 150     | 246            |
+----------+----------+----------+---------+---------+---------+----------------+

我的表格是这样的：

CREATE TABLE IF NOT EXISTS `production` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `factory` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date` int(11) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `product` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `stock_meta` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code_local` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code_company` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `company` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `factory` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=242 ;

所以。有什么想法吗？

编辑：我忘了提及它。我正在使用 Unix 时间戳。

谢谢你的建议。

最好的雷加德。

戈坎

Answer 1

我累了，不应该再写代码了。我想它会看起来像这样。

SELECT
   stock_meta.code_company AS product,
   stock_meta.company AS company,
   stock_meta.factory AS total_product,
   s1.shift1,
   s2.shift2,
   s2.shift3,
   stock_meta.factory AS total_product
FROM
   production
INNER JOIN
   stock_meta ON production.product = stock_meta.code_local
LEFT JOIN 
   ( SELECT stock_meta.code_company AS product, 
        COUNT(production.product) AS shift1
     FROM production, stock_meta
     WHERE production.product = stock_meta.code_local
     AND date BETWEEN '.$start_date.' AND '.strtotime('+8 hour', $start_date).'
   ) as s1 USING (product)
LEFT JOIN 
   ( SELECT stock_meta.code_company AS product, 
        COUNT(production.product) AS shift2
     FROM production, stock_meta
     WHERE production.product = stock_meta.code_local
     AND date BETWEEN '.strtotime('+8 hour', $start_date).' AND '.strtotime('+16 hour', $start_date).'
   ) as s3 USING (product)
LEFT JOIN 
   ( SELECT stock_meta.code_company AS product, 
       COUNT(production.product) AS shift3
     FROM production, stock_meta
     WHERE production.product = stock_meta.code_local
     AND date BETWEEN '.strtotime('+16 hour', $start_date).' AND '.strtotime('+24 hour', $start_date).'
   ) as s3 USING (product)
WHERE
   date BETWEEN '.$start_date.' AND '.strtotime('+16 hour', $start_date).'
GROUP BY
   product
ORDER BY
   total_product