add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQLite
SQL Server
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
5.5
5.6
5.7
8.0
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
3.8
3.16
3.27
3.39
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
2.11
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
run
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
CREATE TABLE `workorder` ( `WoNumber` varchar(20) NOT NULL, `MachCode` varchar(15) NOT NULL, `PlannedMoldCode` varchar(10) NOT NULL, `PartyNumber` smallint(6) NOT NULL, `PlanProdCycleTime` smallint(6) NOT NULL, `CalAverageCycleTime` float(15,1) unsigned NOT NULL, `ProductRawMaterial` varchar(30) NOT NULL, `PlanProdStartDate` date NOT NULL, `PlanProdFinishDate` int(10) unsigned NOT NULL, `WoStartDate` datetime DEFAULT NULL, `WoFinishDate` datetime DEFAULT NULL, `WoWorkTime` int(10) unsigned NOT NULL, `WoSystemProductivity` smallint(6) unsigned NOT NULL, `AssignStaff` varchar(50) DEFAULT '', `WoStatus` smallint(6) NOT NULL, `WoSortNumber` int(10) unsigned NOT NULL, `CycleCount` int(11) unsigned NOT NULL, `ControlDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP, `WoProductionStatus` smallint(6) NOT NULL DEFAULT '0', `Creator` varchar(50) NOT NULL, `Changer` varchar(50) NOT NULL, `CreateDate` datetime NOT NULL, PRIMARY KEY (`WoNumber`) USING BTREE, KEY `WoNumber` (`WoNumber`) USING BTREE, KEY `WoNumber_2` (`WoNumber`) USING BTREE, KEY `WoNumber_3` (`WoNumber`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
CREATE TABLE `machine` ( `MachCode` varchar(15) NOT NULL, `MachModel` varchar(30) NOT NULL, `FirstProdDate` date NOT NULL, `MachCapacity` smallint(6) NOT NULL, `MachStatus` smallint(6) NOT NULL, `NowMoldOnMach` varchar(10) NOT NULL DEFAULT '', `NowMachOperator` varchar(50) NOT NULL DEFAULT '', `NowWorkOrder` varchar(20) NOT NULL DEFAULT '', `IPNumber` varchar(15) NOT NULL, `Creator` varchar(50) NOT NULL, `Changer` varchar(50) NOT NULL, `ControlDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `OperatorLoginDate` datetime DEFAULT NULL, `Message` varchar(500) DEFAULT NULL, `MessageReaded` smallint(6) DEFAULT '0', `StaffName` varchar(50) DEFAULT 'OSIS', `StaffImage` varchar(255) DEFAULT '', `StopDesc` varchar(30) DEFAULT 'OSIS', `StopTime` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`MachCode`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; CREATE TABLE `rawmaterials` ( `RawMaterialCode` varchar(15) NOT NULL, `RawMaterialDescription` varchar(30) NOT NULL, `RawMaterialColor` varchar(30) NOT NULL, PRIMARY KEY (`RawMaterialCode`) USING BTREE, KEY `RawMaterialCode` (`RawMaterialCode`) USING BTREE ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
CREATE TABLE `staff` ( `StaffCode` varchar(15) DEFAULT NULL, `StaffCardCode` varchar(10) DEFAULT NULL, `StaffName` varchar(50) NOT NULL, `StaffPassword` varchar(10) NOT NULL, `StaffStatus` smallint(6) NOT NULL DEFAULT '2', `StaffDateOfStart` date NOT NULL, `StaffBirthDay` date DEFAULT NULL, `StaffGender` varchar(5) DEFAULT NULL, `StaffRoleA` smallint(6) NOT NULL, `StaffEmail` varchar(100) NOT NULL, `StaffImageLink` varchar(255) DEFAULT NULL, `AccountName` varchar(50) NOT NULL, `StaffRoleB` smallint(6) NOT NULL, `StaffRoleD` smallint(6) NOT NULL, `StaffRoleE` smallint(6) NOT NULL, `StaffRoleC` smallint(6) NOT NULL, `StaffRoleF` smallint(6) NOT NULL, `StaffRoleG` smallint(6) NOT NULL, `StaffRoleH` smallint(6) NOT NULL, `StaffRoleI` smallint(6) NOT NULL, `StaffRoleJ` smallint(6) NOT NULL, `StaffRoleK` smallint(6) NOT NULL, `StaffRoleL` smallint(6) NOT NULL, `StaffRoleM` smallint(6) NOT NULL, `StaffRoleN` smallint(6) NOT NULL, `StaffConnection` smallint(6) NOT NULL DEFAULT '2', `MachineWorked` varchar(15) DEFAULT NULL, `WorkOrderWorked` varchar(20) DEFAULT NULL, `StaffGroup` varchar(50) NOT NULL, `Creator` varchar(50) NOT NULL, `Changer` varchar(50) DEFAULT NULL, PRIMARY KEY (`AccountName`) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `workorderb` ( `xWoNumber` varchar(20) NOT NULL, `xMachCode` varchar(15) NOT NULL, `xPlannedMoldCode` varchar(10) NOT NULL, `xPartyNumber` smallint(6) NOT NULL, `xStaffName` varchar(50) NOT NULL, `xStopCode` smallint(6) NOT NULL, `xStopStartTime` datetime NOT NULL, `xStopFinishTime` datetime DEFAULT NULL, `xStopTime` int(11) DEFAULT NULL, PRIMARY KEY (`xMachCode`,`xStopStartTime`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
SELECT workorder.WoNumber,machine.MachModel,machine.MachStatus,rawmaterials.RawMaterialDescription,rawmaterials.RawMaterialColor,staff.StaffName,staff.StaffCode,SUM(workorderb.xStopTime) FROM workorder LEFT JOIN machine ON machine.MachCode=workorder.MachCode LEFT JOIN rawmaterials ON rawmaterials.RawMaterialCode=workorder.ProductRawMaterial LEFT JOIN staff ON staff.AccountName=workorder.AssignStaff LEFT JOIN workorderb ON workorderb.xWoNumber=workorder.WoNumber WHERE workorder.WoStatus=3 GROUP BY workorder.WoNumber ORDER BY workorder.PlanProdStartDate DESC, workorder.WoSortNumber, workorder.WoNumber LIMIT 100