订餐规则，一定时间内可以消费，早餐：7：00-9：00，午餐：11：30-13:30，晚餐17:00-19:00，如何统计就餐的次数？

看不到图。   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382

   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。
CREATE DEFINER=`root`@`localhost` PROCEDURE `PKQ_CalcOrderTotal`(V_EmpSysID varchar(36),V_StartDate date,V_EndDate date)
BEGIN
DECLARE V_LoopDate date;
DECLARE V_BreakfastStart int;
DECLARE V_BreakfastEnd int;
DECLARE V_LunchStart int;
DECLARE V_LunchEnd int;
DECLARE V_BreakfastCi tinyint;
DECLARE V_LunchCi tinyint;
DECLARE V_ResultY varchar(4);
DECLARE V_ResultYM varchar(7);
DECLARE V_KqTime int;
DECLARE V_KqDataCi tinyint;
DECLARE V_Loop tinyint; SET V_ResultY=DATE_FORMAT(V_StartDate,'%Y');
SET V_ResultYM=DATE_FORMAT(V_StartDate,'%Y%m');
SET V_BreakfastCi =0;
SET V_LunchCi =0;

DELETE FROM kq_empyeardate WHERE EmpSysID=V_EmpSysID AND ResultY=V_ResultY;
INSERT INTO kq_empyeardate(GUID,EmpSysID,ResultY)VALUES(uuid(),V_EmpSysID,V_ResultY);

DELETE FROM kq_resultorderday;
DELETE FROM vkq_reportorderdaytotal Where EmpSysID=V_EmpSysID AND ResultDate>=V_StartDate AND ResultDate<=V_EndDate;
DELETE FROM vkq_reportordermonthtotal Where EmpSysID=V_EmpSysID AND ResultYM=V_ResultYM;
DELETE FROM vkq_reportorderyeartotal ;

SELECT BreakfastStart,BreakfastEnd,LunchStart,LunchEnd INTO V_BreakfastStart,V_BreakfastEnd,V_LunchStart,V_LunchEnd FROM kq_orderdate;

SET V_LoopDate=V_StartDate;
SET V_Loop=0;
WHILE V_LoopDate<=V_EndDate DO

SELECT count(*)  INTO V_KqDataCi FROM kq_kqdata WHERE EmpSysID=V_EmpSysID AND KqDate=V_LoopDate ;
IF NOT EXISTS(select EmpSysID From kq_resultorderday WHERE EmpSysID=V_EmpSysID AND ResultDate=V_LoopDate) THEN
   INSERT INTO kq_resultorderday(EmpSysID,ResultDate,ResultBreakfastCi,ResultLunchCi) values(V_EmpSysID,V_LoopDate,V_BreakfastCi,V_LunchCi);
END IF;

WHILE V_Loop<V_KqDataCi DO
SELECT KqTime INTO V_KqTime FROM kq_kqdata WHERE EmpSysID=V_EmpSysID AND KqDate=V_LoopDate limit V_Loop,1;
IF V_KqTime >=V_BreakfastStart AND V_KqTime <=V_BreakfastEnd THEN
SET V_BreakfastCi = V_BreakfastCi + 1;
UPDATE kq_resultorderday SET ResultBreakfastCi = ResultBreakfastCi + V_BreakfastCi WHERE EmpSysID=V_EmpSysID AND ResultDate=V_LoopDate;

END IF; IF V_KqTime >=V_LunchStart AND V_KqTime <=V_LunchEnd THEN
SET V_LunchCi = V_LunchCi + 1;
UPDATE kq_resultorderday SET ResultLunchCi =ResultLunchCi+V_LunchCi WHERE EmpSysID=V_EmpSysID AND ResultDate=V_LoopDate ;
END IF;
SET V_BreakfastCi =0;
SET V_LunchCi =0;
SET V_Loop= V_Loop + 1;
END WHILE;
SET V_Loop=0;
SET V_LoopDate=DATE_ADD(V_LoopDate,INTERVAL 1 DAY);

END WHILE;
CALL PKQ_SaveKQOrderDataView();

ENDCREATE DEFINER=`root`@`localhost` PROCEDURE `PKQ_SaveKQOrderDataView`()
BEGIN
DECLARE V_S varchar(8000);
DECLARE stop int DEFAULT 0;
DECLARE V_Column_Name varchar(100);
DECLARE curResultDay CURSOR FOR SELECT column_name FROM information_schema.columns where table_name = 'vkq_reportorderdaytotal';
DECLARE curResultMonth CURSOR FOR SELECT column_name FROM information_schema.columns where table_name = 'vkq_reportordermonthtotal';
DECLARE curResultYear CURSOR FOR SELECT column_name FROM information_schema.columns where table_name = 'vkq_reportorderyeartotal';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop=1;
SET stop=0;
SET V_S='insert into vkq_reportorderdaytotal select ';
   OPEN curResultDay;
   FETCH curResultDay INTO V_Column_Name;
   WHILE stop<>1 DO
     SET V_S=CONCAT(V_S,'    ',V_Column_Name,',' );
     FETCH curResultDay INTO V_Column_Name;
   END WHILE;
   CLOSE curResultDay;
SET V_S=LEFT(V_S,LENGTH(V_S)-1);
SET V_S=CONCAT(V_S,'  from vkq_reportorderdayext;');
CALL PSY_ExecSQL(V_S); SET stop=0;
SET V_S='insert into vkq_reportordermonthtotal select ';
   OPEN curResultMonth;
   FETCH curResultMonth INTO V_Column_Name;
   WHILE stop<>1 DO
     SET V_S=CONCAT(V_S,'    ',V_Column_Name,',' );
     FETCH curResultMonth INTO V_Column_Name;
   END WHILE;
   CLOSE curResultMonth;
SET V_S=LEFT(V_S,LENGTH(V_S)-1);
SET V_S=CONCAT(V_S,'  from vkq_reportordermonthext;');
CALL PSY_ExecSQL(V_S); SET stop=0;
SET V_S='insert into vkq_reportorderyeartotal select ';
   OPEN curResultYear;
   FETCH curResultYear INTO V_Column_Name;
   WHILE stop<>1 DO
     SET V_S=CONCAT(V_S,'    ',V_Column_Name,',' );
     FETCH curResultYear INTO V_Column_Name;
   END WHILE;
   CLOSE curResultYear;
SET V_S=LEFT(V_S,LENGTH(V_S)-1);
SET V_S=CONCAT(V_S,'  from vkq_reportorderyearext;');select V_S;
CALL PSY_ExecSQL(V_S);

ENDCREATE DEFINER=`root`@`localhost` PROCEDURE `PSY_ExecSQL`(V_S varchar(8000))
BEGIN  SET @sql=V_S;  PREPARE stm FROM @sql; EXECUTE stm;  DEALLOCATE PREPARE stm;END
CREATE TABLE `kq_empyeardate` (
  `GUID` varchar(36) NOT NULL,
  `EmpSysID` varchar(36) NOT NULL,
  `ResultY` varchar(10) NOT NULL,
  PRIMARY KEY (`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE TABLE `vkq_reportorderdaytotal` (
  `EmpSysID` varchar(36) NOT NULL,
  `EmpNo` varchar(10) DEFAULT NULL,
  `EmpName` varchar(36) DEFAULT NULL,
  `DepartID` varchar(12) DEFAULT NULL,
  `DepartName` varchar(50) DEFAULT NULL,
  `JobName` varchar(50) DEFAULT NULL,
  `ResultDate` date NOT NULL,
  `ResultBreakfastCi` int(4) DEFAULT NULL,
  `ResultLunchCi` int(4) DEFAULT NULL,
  PRIMARY KEY (`EmpSysID`,`ResultDate`),
  UNIQUE KEY `AK_KQ_ReportDay` (`EmpSysID`,`ResultDate`),
  KEY `AK_KQ_ReportDay2` (`EmpNo`),
  KEY `AK_KQ_ReportDay3` (`DepartName`),
  KEY `AK_KQ_ReportDay4` (`ResultDate`),
  KEY `AK_KQ_ReportDay5` (`EmpName`),
  KEY `AK_KQ_ReportDay6` (`DepartID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE TABLE `vkq_reportordermonthtotal` (
  `EmpSysID` varchar(36) NOT NULL,
  `EmpNo` varchar(10) NOT NULL,
  `EmpName` varchar(50) DEFAULT NULL,
  `DepartID` varchar(10) DEFAULT NULL,
  `DepartName` varchar(50) DEFAULT NULL,
  `JobName` varchar(50) DEFAULT NULL,
  `ResultYM` varchar(8) NOT NULL,
  `OrderID` tinyint(1) DEFAULT NULL,
  `OrderName` char(4) DEFAULT NULL,
  `D01` int(4) DEFAULT NULL,
  `D02` int(4) DEFAULT NULL,
  `D03` int(4) DEFAULT NULL,
  `D04` int(4) DEFAULT NULL,
  `D05` int(4) DEFAULT NULL,
  `D06` int(4) DEFAULT NULL,
  `D07` int(4) DEFAULT NULL,
  `D08` int(4) DEFAULT NULL,
  `D09` int(4) DEFAULT NULL,
  `D10` int(4) DEFAULT NULL,
  `D11` int(4) DEFAULT NULL,
  `D12` int(4) DEFAULT NULL,
  `D13` int(4) DEFAULT NULL,
  `D14` int(4) DEFAULT NULL,
  `D15` int(4) DEFAULT NULL,
  `D16` int(4) DEFAULT NULL,
  `D17` int(4) DEFAULT NULL,
  `D18` int(4) DEFAULT NULL,
  `D19` int(4) DEFAULT NULL,
  `D20` int(4) DEFAULT NULL,
  `D21` int(4) DEFAULT NULL,
  `D22` int(4) DEFAULT NULL,
  `D23` int(4) DEFAULT NULL,
  `D24` int(4) DEFAULT NULL,
  `D25` int(4) DEFAULT NULL,
  `D26` int(4) DEFAULT NULL,
  `D27` int(4) DEFAULT NULL,
  `D28` int(4) DEFAULT NULL,
  `D29` int(4) DEFAULT NULL,
  `D30` int(4) DEFAULT NULL,
  `D31` int(4) DEFAULT NULL,
  `Total` int(4) DEFAULT NULL,
  KEY `AK_KQ_ReportOrderM` (`EmpName`) USING BTREE,
  KEY `AK_KQ_ReportOrderM2` (`EmpNo`),
  KEY `AK_KQ_ReportOrderM3` (`DepartID`),
  KEY `AK_KQ_ReportOrderM4` (`DepartName`),
  KEY `AK_KQ_ReportOrderM5` (`ResultYM`),
  KEY `AK_KQ_ReportOrderM6` (`EmpName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8CREATE TABLE `vkq_reportorderyeartotal` (
  `EmpSysID` varchar(36) NOT NULL,
  `EmpNo` varchar(20) DEFAULT NULL,
  `EmpName` varchar(50) DEFAULT NULL,
  `DepartID` varchar(12) DEFAULT NULL,
  `DepartName` varchar(50) DEFAULT NULL,
  `JobName` varchar(50) DEFAULT NULL,
  `ResultY` varchar(4) NOT NULL,
  `ResultBreakfastCi` int(4) DEFAULT NULL,
  `ResultLunchCi` int(4) DEFAULT NULL,
  PRIMARY KEY (`EmpSysID`,`ResultY`),
  UNIQUE KEY `AK_VKQ_ReportOderYTotal` (`ResultY`,`EmpSysID`),
  KEY `AK_VKQ_ReportOderYTotal2` (`EmpNo`),
  KEY `AK_VKQ_ReportOderYTotal3` (`DepartName`),
  KEY `AK_VKQ_ReportOderYTotal4` (`ResultY`),
  KEY `AK_VKQ_ReportOderYTotal5` (`DepartID`),
  KEY `AK_VKQ_ReportOderYTotal6` (`EmpName`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8还有几张视图，跟vkq_reportorderdaytotal，vkq_reportordermonthtotal，vkq_reportorderyeartotal表机构一模一样，目前是这么设计的

调试易

订餐规则，一定时间内可以消费，早餐：7：00-9：00，午餐：11：30-13:30，晚餐17:00-19:00，如何统计就餐的次数？

解决方案 »