
ABORT;
BEGIN;
CREATE VIEW Categ AS
SELECT sn, CAST(記録日時 AS DATE) AS dd, CAST(記録日時 AS TIME) AS tt
FROM (
	SELECT sn,記録日時 FROM 体温記録
	EXCEPT
	SELECT a.sn,a.記録日時 FROM 体温記録 AS a, 体温記録 AS b
	WHERE a.sn=b.sn
	AND CAST(a.記録日時 AS DATE)=CAST(b.記録日時 AS DATE) AND a.記録日時<b.記録日時
) AS C
WHERE 記録日時>'2021-06-01';

CREATE VIEW Categ3 AS
SELECT sn, dd,tt FROM Categ WHERE tt BETWEEN '00:00' AND '08:30';

CREATE VIEW Categ2 AS
SELECT sn, dd,tt FROM Categ WHERE tt BETWEEN '08:30' AND '09:00';

CREATE VIEW Categ1 AS
SELECT sn, dd,tt FROM Categ WHERE tt BETWEEN '09:00' AND '10:45';

CREATE VIEW Categ0 AS
SELECT sn, dd,tt FROM Categ WHERE tt BETWEEN '10:45' AND '24:00';

CREATE VIEW RANKING AS
SELECT ssn AS 出席番号, 氏名, x3 ,x2 ,x1, x0,(3*x3+2*x2+x1) AS 計
FROM (
	SELECT sn, SUM(G3) AS x3, SUM(G2) AS x2, SUM(G1) AS x1, SUM(G0) AS x0
	FROM (
		SELECT sn,count(*) AS G3, 0 AS G2 ,0 AS G1, 0 AS G0 FROM Categ3 GROUP BY sn
		UNION
		SELECT sn,0 AS G3, count(*) AS G2 ,0 AS G1, 0 AS G0 FROM Categ2 GROUP BY sn
		UNION
		SELECT sn,0 AS G3, 0 AS G2 ,count(*) AS G1, 0 AS G0 FROM Categ1 GROUP BY sn
		UNION
		SELECT sn,0 AS G3, 0 AS G2 ,0 AS G1, count(*) AS G0 FROM Categ0 GROUP BY sn
		UNION
		SELECT sn,0 AS G3, 0 AS G2 ,0 AS G1, 0 AS G0 FROM メンバー
		) AS x
	GROUP BY sn
	) AS y NATURAL JOIN メンバー
WHERE ssn<50
ORDER BY 計 DESC;

SELECT *, (x3+x2+x1+x0) AS スタンプ数 FROM RANKING
LIMIT 10;





