SQL——查询一段时间内每天的数据，按天将数据封装进行封存

DROP TABLE IF EXISTS `T_ROTA_RECORD`;

CREATE TABLE `T_ROTA_RECORD` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `duty_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '值班日期',
  `duty_turn` tinyint(4) NOT NULL DEFAULT '0' COMMENT '值班班次，0：全天班 1：早班 2：中班 3：晚班',
  `provider_id` int(11) DEFAULT '0' COMMENT '值班对象ID，T_ROTA_PROVIDER.id',
  `is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否被删除：0-否；1-是',
  `org_code` varchar(128) DEFAULT '""' COMMENT '企业或部门编码',
  `is_noticed` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否已经通知（0：否，1：是）',
  `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据更新时间，默认为当前时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='值班记录表';

/*Data for the table `T_ROTA_RECORD` */

insert  into `T_ROTA_RECORD`(`id`,`duty_date`,`duty_turn`,`provider_id`,`is_delete`,`org_code`,`is_noticed`,`update_time`) values 

(1,'2018-05-25',1,1,0,'330100.000002.001',0,'2018-05-25 11:00:24'),

(2,'2018-05-25',2,2,0,'330100.000002.001',0,'2018-05-25 11:00:53'),

(3,'2018-05-25',3,3,0,'330100.000002.001',0,'2018-05-25 11:01:28'),

(4,'2018-05-26',1,2,0,'330100.000002.001',0,'2018-05-25 11:02:25'),

(5,'2018-05-27',1,1,0,'330100.000002.001',0,'2018-05-25 11:02:52'),

(6,'2018-05-25',1,4,0,'330100.000002.002',0,'2018-05-25 11:09:34'),

(7,'2018-05-26',1,5,0,'330100.000002.002',0,'0000-00-00 00:00:00'),

(8,'2018-05-26',2,4,0,'330100.000002.002',0,'2018-05-25 11:10:18'),

(9,'2018-05-26',3,6,0,'330100.000002.002',0,'2018-05-25 11:10:44'),

(10,'2018-05-27',0,4,0,'330100.000002.002',0,'0000-00-00 00:00:00');

查询一段时间内每天的数据，按天将数据封装进行封存，同一天的数据封装成list，如何实现呢？

抽象成一对多的关系，即同一天对应多条数据，同一天必须保证是同一条数据。

SELECT
  a.`id`,
  a.`duty_date`,
  b.`duty_turn`,
  b.provider_id,
  b.is_delete,
  b.org_code,
  b.is_noticed,
  b.update_time
FROM
  (SELECT
    id,
    duty_date
  FROM
    T_ROTA_RECORD
  WHERE is_delete = 0
 --   AND org_code = '330100.000002.001'
  GROUP BY duty_date) a
  LEFT JOIN
    (SELECT
      duty_date,
      duty_turn,
      provider_id,
      is_delete,
      org_code,
      is_noticed,
      update_time
    FROM
      T_ROTA_RECORD
    WHERE is_delete = 0
 --     AND org_code = '330100.000002.001'
    GROUP BY duty_date,
      duty_turn ) b
    ON a.`duty_date` = b.`duty_date`
    
    WHERE   b.org_code = '330100.000002.001'
     AND a.duty_date >= '2018-05-25'
     AND a.duty_date <= '2018-05-28'
    -- and b.provider_id = 1
    ORDER BY  a.`duty_date`, b.duty_turn
    
    

这里的a.id 不可省略，只有加上才能表示同一天是同一条数据。