2021 A과제 - A

	SELECT 
	    CONCAT(t1.ONo, '-', t1.Opos) AS `ONo-Opos`,
	    MAX(CASE WHEN t1.StepNO = 1 THEN TIMESTAMPDIFF(SECOND, t1.START, t1.END) END) AS `Step 1`,
	    MAX(CASE WHEN t2.StepNO = 2 THEN TIMESTAMPDIFF(SECOND, t2.START, t2.END) END) AS `Step 2`,
	    MAX(CASE WHEN t3.StepNO = 3 THEN TIMESTAMPDIFF(SECOND, t3.START, t3.END) END) AS `Step 3`,
	    TIMESTAMPDIFF(SECOND, t1.START, t3.END) 
      - IFNULL(MAX(CASE WHEN t1.StepNO = 1 THEN TIMESTAMPDIFF(SECOND, t1.START, t1.END) END), 0)
      - IFNULL(MAX(CASE WHEN t2.StepNO = 2 THEN TIMESTAMPDIFF(SECOND, t2.START, t2.END) END), 0)
      - IFNULL(MAX(CASE WHEN t3.StepNO = 3 THEN TIMESTAMPDIFF(SECOND, t3.START, t3.END) END), 0) AS transport,
	    TIMESTAMPDIFF(SECOND, t1.START, t3.END) AS total
	FROM 
	    tblstep t1
	LEFT JOIN 
	    tblstep t2 ON t1.ONo = t2.ONo AND t1.Opos = t2.Opos AND t2.StepNO = 2
	LEFT JOIN 
	    tblstep t3 ON t1.ONo = t3.ONo AND t1.Opos = t3.Opos AND t3.StepNO = 3
	WHERE                                                           
	    t1.StepNO = 1
	GROUP BY 
	    `ONo-Opos`;

이 쿼리는 실시간으로 값은 나오지만 마지막 공정이 끝나면 나오지 않는다.

2021 A과제 - B

SELECT 
    CONCAT(t1.ONo, '-', t1.Opos) AS `ONo-Opos`,
    MAX(CASE WHEN t1.StepNO = 1 THEN TIMESTAMPDIFF(SECOND, t1.START, t1.END) END) AS `Step 1`,
    MAX(CASE WHEN t2.StepNO = 2 THEN TIMESTAMPDIFF(SECOND, t2.START, t2.END) END) AS `Step 2`,
    MAX(CASE WHEN t3.StepNO = 3 THEN TIMESTAMPDIFF(SECOND, t3.START, t3.END) END) AS `Step 3`,
    TIMESTAMPDIFF(SECOND, t1.START, t3.END)
    - IFNULL(MAX(CASE WHEN t1.StepNO = 1 THEN TIMESTAMPDIFF(SECOND, t1.START, t1.END) END), 0)
    - IFNULL(MAX(CASE WHEN t2.StepNO = 2 THEN TIMESTAMPDIFF(SECOND, t2.START, t2.END) END), 0)
    - IFNULL(MAX(CASE WHEN t3.StepNO = 3 THEN TIMESTAMPDIFF(SECOND, t3.START, t3.END) END), 0) AS transport,
	 TIMESTAMPDIFF(SECOND, t1.START, t3.END) AS total
FROM 
    tblfinstep t1
LEFT JOIN 
    tblfinstep t2 ON t1.ONo = t2.ONo AND t1.Opos = t2.Opos AND t2.StepNO = 2
LEFT JOIN 
    tblfinstep t3 ON t1.ONo = t3.ONo AND t1.Opos = t3.Opos AND t3.StepNO = 3
WHERE 
    t1.StepNO = 1
GROUP BY 
    `ONo-Opos`;

이 쿼리는 실시간으로 값이 나오진 않지만 마지막 공정이 끝나면 모든 값이 나온다.

2021 A과제 - A + B

SELECT 
    CONCAT(ONo, '-', Opos) AS ONo_Opos,
    MAX(CASE WHEN StepNO = 1 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_1,
    MAX(CASE WHEN StepNO = 2 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_2,
    MAX(CASE WHEN StepNO = 3 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_3,
    MAX(CASE WHEN StepNO = 4 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_4,
    MAX(CASE WHEN StepNO = 5 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_5,
    MAX(CASE WHEN StepNO = 6 THEN TIMESTAMPDIFF(SECOND, START, END) END) AS Step_6,
    TIMESTAMPDIFF(SECOND, MIN(START), MAX(END)) -
    SUM(TIMESTAMPDIFF(SECOND, START, END)) AS transport,
    TIMESTAMPDIFF(SECOND, MIN(START), MAX(END)) AS total
FROM (
    SELECT * FROM tblstep
    UNION ALL
    SELECT * FROM tblfinstep
) AS combined
GROUP BY ONo, Opos;

이 쿼리는 위에서 본 쿼리의 장점들을 합쳐 놓은 모습이다.

SELECT sd.WPNo, sd.StepNo, sd.OpNo, pd.Short, cnt.WorkplanCount
FROM tblstepdef sd
JOIN tblworkplandef pd ON sd.WPNo = pd.WPNo
JOIN (
    SELECT WPNo, COUNT(*) as WorkplanCount
    FROM tblstepdef
    GROUP BY WPNo
) cnt ON sd.WPNo = cnt.WPNo
ORDER BY sd.WPNo, sd.StepNo;

이 쿼리는 Woekplan의 이름과 그 Plan의 갯수를 나타내는 쿼리이다.

SELECT ResourceID, ResourceName, IP
FROM tblresource
WHERE ResourceID > 0
ORDER BY ResourceID;

이 쿼리는 지금 무슨 공정이 있고 IP는 무엇인지 알려주는 쿼리이다.

INSERT INTO tblquality (ONo,OPos,PNo,Nop,Tom,Mvm,Msr) VALUES (1001, 1, 2345, 'Test part 2345', '2024-11-07 12:03:40', 40, 0);
INSERT INTO tblquality (ONo, OPos, PNo, Nop, Tom, Mvm, Msr) VALUES (1000, 1, 2345, CONCAT('Test past ', PNo), NOW(), 40, 0);
INSERT INTO tblquality (ONo, OPos, PNo, Nop, Tom, Mvm, Msr) VALUES (?, ?, ?, CONCAT('Test past ', ?), NOW(), ?, ?);
SELECT ONo,OPos,PNo,Nop,Tom,Mvm,if (Msr = 0, 'NOK','OK')as Msr FROM tblquality