

DROP TABLE IF EXISTS `t_zebwdt1000`;

CREATE TABLE `t_zebwdt1000` (
  `ZEBWD_SYS_ID` varchar(30) NOT NULL ,
  `ZEBWD_SYS_UP` varchar(30) DEFAULT NULL ,
  `ZEBWD_SYS_LV` char(2) DEFAULT NULL ,
  `ZEBWD_SYS_SORT` int(11) DEFAULT NULL ,
  `ZEBWD_SYS_AV` char(2) DEFAULT NULL ,
  `ZEBWD_SYS_DEL` char(1) DEFAULT NULL ,
  `ZEBWD_SYS_APPLY` char(1) DEFAULT NULL ,
  `ERDAT` datetime DEFAULT NULL ,
  `ERNAM` varchar(12) DEFAULT NULL ,
  `REPID` varchar(40) DEFAULT NULL ,
  `AEDAT` datetime DEFAULT NULL ,
  `AENAM` varchar(12) DEFAULT NULL ,
  PRIMARY KEY (`ZEBWD_SYS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_zebwdt1000` */

insert  into `t_zebwdt1000`(`ZEBWD_SYS_ID`,`ZEBWD_SYS_UP`,`ZEBWD_SYS_LV`,`ZEBWD_SYS_SORT`,`ZEBWD_SYS_AV`,`ZEBWD_SYS_DEL`,`ZEBWD_SYS_APPLY`,`ERDAT`,`ERNAM`,`REPID`,`AEDAT`,`AENAM`) values 
('ZSBILL1000','0000000000','1',1,'1','N',NULL,NULL,NULL,NULL,NULL,NULL),
('ZSBILL1010','ZSBILL1000','2',1,'1','N',NULL,NULL,NULL,NULL,'2018-01-03 15:50:10',NULL),
('ZSBILL1020','ZSBILL1000','2',2,'1','N',NULL,NULL,NULL,NULL,'2018-01-03 15:50:23',NULL),
('ZSBILL1030','ZSBILL1000','2',3,'1','N',NULL,NULL,NULL,NULL,'2018-01-03 15:50:48',NULL),
('ZSBILL2000','0000000000','1',1,'3','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2010','ZSBILL2000','2',1,'3','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2020','ZSBILL2000','2',2,'3','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2030','ZSBILL2000','2',3,'3','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3000','0000000000','1',1,'2','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3010','ZSBILL3000','2',1,'2','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3020','ZSBILL3000','2',2,'2','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3030','ZSBILL3000','2',3,'2','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4000','0000000000','1',1,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4010','ZSBILL4000','2',1,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4020','ZSBILL4000','2',2,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4030','ZSBILL4000','2',3,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4040','ZSBILL4000','2',4,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4050','ZSBILL4000','2',5,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4060','ZSBILL4000','2',6,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4070','ZSBILL4000','2',8,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4080','ZSBILL4000','2',9,'5','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4090','ZSBILL4000','2',7,'5','N',NULL,NULL,NULL,NULL,'2018-01-18 00:00:00','odpia3'),
('ZSBILL5000','0000000000','1',1,'6','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5010','ZSBILL5000','2',1,'6','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5020','ZSBILL5000','2',2,'6','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5030','ZSBILL5000','2',3,'6','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5040','ZSBILL5000','2',4,'6','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6000','0000000000','1',1,'8','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6010','ZSBILL6000','2',1,'8','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6020','ZSBILL6000','2',2,'8','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6030','ZSBILL6000','2',5,'8','N',NULL,NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6040','ZSBILL6000','2',5,'8','N',NULL,'2018-01-17 00:00:00','odpia3',NULL,NULL,NULL),
('ZSBILL6050','ZSBILL6000','2',5,'8','N',NULL,'2018-01-17 00:00:00','odpia3',NULL,NULL,NULL),
('ZSBILL7000','0000000000','1',1,'4','N',NULL,'2018-01-17 00:00:00','odpia3',NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL8000','0000000000','1',1,'7','N',NULL,'2018-01-17 00:00:00','odpia3',NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL9000','0000000000','1',1,'9','N',NULL,NULL,NULL,NULL,NULL,NULL),
('ZSBILL9010','ZSBILL9000','2',1,'9','N',NULL,NULL,NULL,NULL,NULL,NULL),
('ZSBILL9020','ZSBILL9000','2',2,'9','N',NULL,NULL,NULL,NULL,NULL,NULL),
('ZSBILL9030','ZSBILL9000','2',3,'9','N',NULL,NULL,NULL,NULL,NULL,NULL),
('ZSBILL9040','ZSBILL9000','2',4,'9','N',NULL,'2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test'),
('ZSBILL9050','ZSBILL9000','2',5,'9','N',NULL,'2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test'),
('ZSBILL9060','ZSBILL9000','2',6,'9','N',NULL,'2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test');

/*Table structure for table `t_zebwdt1000t` */

DROP TABLE IF EXISTS `t_zebwdt1000t`;

CREATE TABLE `t_zebwdt1000t` (
  `ZEBWD_SYS_ID` varchar(30) NOT NULL ,
  `LANGU` char(1) NOT NULL DEFAULT '3' ,
  `ZEBWD_SYS_IDT` varchar(100) DEFAULT NULL ,
  `ERDAT` datetime DEFAULT NULL ,
  `ERNAM` varchar(12) DEFAULT NULL ,
  `REPID` varchar(40) DEFAULT NULL ,
  `AEDAT` datetime DEFAULT NULL,
  `AENAM` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`ZEBWD_SYS_ID`,`LANGU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

/*Data for the table `t_zebwdt1000t` */

insert  into `t_zebwdt1000t`(`ZEBWD_SYS_ID`,`LANGU`,`ZEBWD_SYS_IDT`,`ERDAT`,`ERNAM`,`REPID`,`AEDAT`,`AENAM`) values 
('ZSBILL1000','3','MENU1',NULL,NULL,NULL,NULL,NULL),
('ZSBILL1010','3','MENU1 - SUB1',NULL,NULL,NULL,'2018-01-03 15:50:10',NULL),
('ZSBILL1020','3','MENU1 - SUB2',NULL,NULL,NULL,'2018-01-03 15:50:23',NULL),
('ZSBILL1030','3','MENU1 - SUB3',NULL,NULL,NULL,'2018-01-03 15:50:48',NULL),
('ZSBILL2000','3','MENU2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2010','3','MENU2 - SUB1',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2020','3','MENU2 - SUB2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL2030','3','MENU2 - SUB3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3000','3','MENU3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3010','3','MENU3 - SUB1',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3020','3','MENU3 - SUB2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL3030','3','MENU3 - SUB3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4000','3','MENU4',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4010','3','MENU4 - SUB1',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4020','3','MENU4 - SUB2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4030','3','MENU4 - SUB3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4040','3','MENU4 - SUB4',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4050','3','MENU4 - SUB5',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4060','3','MENU4 - SUB6',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4070','3','MENU4 - SUB7',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4080','3','MENU4 - SUB8',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL4090','3','MENU4 - SUB9',NULL,NULL,NULL,'2018-01-18 00:00:00','odpia3'),
('ZSBILL5000','3','MENU5',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5010','3','MENU5 - SUB1',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5020','3','MENU5 - SUB2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5030','3','MENU5 - SUB3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL5040','3','MENU5 - SUB4',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6000','3','MENU6',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6010','3','MENU6 - SUB1',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6020','3','MENU6 - SUB2',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6030','3','MENU6 - SUB3',NULL,NULL,NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL6040','3','MENU6 - SUB4','2018-01-17 00:00:00','odpia3',NULL,NULL,NULL),
('ZSBILL6050','3','MENU6 - SUB5','2018-01-17 00:00:00','odpia3',NULL,NULL,NULL),
('ZSBILL7000','3','MENU7','2018-01-17 00:00:00','odpia3',NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL8000','3','MENU8','2018-01-17 00:00:00','odpia3',NULL,'2018-01-17 00:00:00','odpia3'),
('ZSBILL9000','3','MENU9',NULL,NULL,NULL,NULL,NULL),
('ZSBILL9010','3','MENU9 - SUB1',NULL,NULL,NULL,NULL,NULL),
('ZSBILL9020','3','MENU9 - SUB2',NULL,NULL,NULL,NULL,NULL),
('ZSBILL9030','3','MENU9 - SUB3',NULL,NULL,NULL,NULL,NULL),
('ZSBILL9040','3','MENU9 - SUB4','2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test'),
('ZSBILL9050','3','MENU9 - SUB5','2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test'),
('ZSBILL9060','3','MENU9 - SUB6','2018-01-15 00:00:00','test',NULL,'2018-01-15 00:00:00','test');

/*Table structure for table `t_zebwdt1040` */

DROP TABLE IF EXISTS `t_zebwdt1040`;

CREATE TABLE `t_zebwdt1040` (
  `ZEBWD_ROLE_ID` varchar(20) NOT NULL COMMENT 'ROLE ID',
  `ZEBWD_USER_ID` varchar(30) NOT NULL COMMENT 'USER ID',
  PRIMARY KEY (`ZEBWD_ROLE_ID`,`ZEBWD_USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `t_zebwdt1040` */

insert  into `t_zebwdt1040`(`ZEBWD_ROLE_ID`,`ZEBWD_USER_ID`) values 
('A001','test');

/*Table structure for table `t_zebwdt1050` */

DROP TABLE IF EXISTS `t_zebwdt1050`;

CREATE TABLE `t_zebwdt1050` (
  `ZEBWD_ROLE_ID` varchar(20) NOT NULL COMMENT 'ROLE ID',
  `ZEBWD_MENU_ID` varchar(30) NOT NULL COMMENT 'MENU ID',
  PRIMARY KEY (`ZEBWD_ROLE_ID`,`ZEBWD_MENU_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

/*Data for the table `t_zebwdt1050` */

insert  into `t_zebwdt1050`(`ZEBWD_ROLE_ID`,`ZEBWD_MENU_ID`) values 
('A001','ZSBILL1000'),
('A001','ZSBILL2000'),
('A001','ZSBILL3000'),
('A001','ZSBILL4000'),
('A001','ZSBILL5000'),
('A001','ZSBILL6000'),
('A001','ZSBILL7000'),
('A001','ZSBILL8000'),
('A001','ZSBILL9000');



SET SESSION join_cache_level=4;

SELECT DISTINCT T3.ZEBWD_SYS_ID,
                T4.ZEBWD_SYS_IDT,
                T3.ZEBWD_SYS_AV
FROM            T_ZEBWDT1040 T1,   /* A001/A002 */
                T_ZEBWDT1050 T2,
                T_ZEBWDT1000 T3,
                T_ZEBWDT1000T T4
WHERE           T1.ZEBWD_ROLE_ID = T2.ZEBWD_ROLE_ID
AND             T2.ZEBWD_MENU_ID = T3.ZEBWD_SYS_ID
AND             T3.ZEBWD_SYS_DEL = 'N'
AND             T3.ZEBWD_SYS_UP  = '0000000000'
AND             T3.ZEBWD_SYS_ID  = T4.ZEBWD_SYS_ID
AND             T1.ZEBWD_USER_ID = 'test'
ORDER BY        T3.ZEBWD_SYS_AV ASC;