27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT productBar AS 产品编码 ,
CASE WHEN COUNT(step) =4 THEN '成功' ELSE '失败' END AS 组装序号状态 ,
SUM(timeUsed) AS 组装用时
FROM product
GROUP BY productBar;
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
productBar VARCHAR(10),
step INT,
timeUsed INT
)
GO
INSERT INTO t VALUES('2001000001',1,10);
INSERT INTO t VALUES('2001000001',2,13);
INSERT INTO t VALUES('2001000001',3,12);
INSERT INTO t VALUES('2001000001',4,18);
INSERT INTO t VALUES('2001000002',1,11);
INSERT INTO t VALUES('2001000002',2,31);
INSERT INTO t VALUES('2001000002',3,23);
INSERT INTO t VALUES('2001000003',1,21);
INSERT INTO t VALUES('2001000003',2,22);
INSERT INTO t VALUES('2001000003',3,23);
INSERT INTO t VALUES('2001000003',4,23);
-- 以上为测试数据
SELECT productBar
,SUM(timeUsed) AS 总时间
,STUFF((SELECT ','+ LTRIM(b.step) FROM t AS b WHERE a.productBar=b.productBar FOR XML PATH('') ),1,1,'') AS steps
,CASE WHEN (SELECT count(distinct b.step) FROM t AS b WHERE a.productBar=b.productBar )=4 THEN '已完成' ELSE '未完成' END AS 完成状态
FROM t AS a
GROUP BY productBar
/*
productBar 总时间 steps 完成状态
2001000001 53 1,2,3,4 已完成
2001000002 65 1,2,3 未完成
2001000003 89 1,2,3,4 已完成
*/