34,838
社区成员




CREATE TABLE #T
(NAME VARCHAR(10),
DT VARCHAR(10),
M_A INT,
N_A INT,
E_A INT,
M_B INT,
N_B INT,
E_B INT)
INSERT INTO #T
SELECT 'A','0422',1,0,0,1,2,3
WITH CTE
AS
(SELECT NAME,DT,M_A,'MORNING' AS TYPE FROM #T UNION ALL
SELECT NAME,DT,N_A,'NOON' AS TYPE FROM #T UNION ALL
SELECT NAME,DT,E_A,'EVENING' AS TYPE FROM #T UNION ALL
SELECT NAME,DT,-1*M_B,'MORNING' AS TYPE FROM #T UNION ALL
SELECT NAME,DT,-1*N_B,'NOON' AS TYPE FROM #T UNION ALL
SELECT NAME,DT,-1*E_B,'EVENING' AS TYPE FROM #T)
SELECT NAME,DT,TYPE,CASE WHEN TYPE='MORNING' THEN '早上异常'
WHEN TYPE='NOON' THEN '中午异常' ELSE '晚上异常' END AS RESULT
FROM CTE
GROUP BY NAME,DT,TYPE
HAVING SUM(M_A)<>0