查询进度问题
要求全输入的,按拆分后的子段做统计，输入部分的，按输入的做统计
--数据
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A(List_id varchar(10), F_ListID varchar(10))
insert into #A
select '112','111' union all
select '113','111' union all
select '111','111' union all
select '213','211' union all
select '212','211' union all
select '211','211' union all
select '313','311' union all
select '312','311' union all
select '311','311' union all
select '413','411' union all
select '412','411' union all
select '411','411' union all
select '513','511' union all
select '512','511' union all
select '511','511' union all
select '613','611' union all
select '612','611' union all
select '611','611'
if object_id('tempdb.dbo.#A') is not null drop table #B
create table #B(List_id varchar(10), Status varchar(10))
insert into #B
--全输入的,拆分后的子段全完成的为完成
select '112','完成' union all
select '113','完成' union all
select '111','未接收' union all
select '213','在操作' union all
select '212','完成' union all
select '211','完成' union all
--输入部分的，按输入的完成为完成
select '312','在操作' union all
select '313','完成' union all
select '412','完成' union all
select '411','在操作' union all
select '511','在操作' union all
select '613','在操作'
--结果
F_ListID Status
---------- ------
111 完成
211 在操作
311 在操作
411 完成
511 在操作
611 在操作
[解决办法]
USE test
GO
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A(List_id varchar(10), F_ListID varchar(10))
insert into #A
select '112','111' union all
select '113','111' union all
select '111','111' union all
select '213','211' union all
select '212','211' union all
select '211','211' union all
select '313','311' union all
select '312','311' union all
select '311','311' union all
select '413','411' union all
select '412','411' union all
select '411','411' union all
select '513','511' union all
select '512','511' union all
select '511','511' union all
select '613','611' union all
select '612','611' union all
select '611','611'
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B(List_id varchar(10), Status varchar(10))
insert into #B
--全输入的,拆分后的子段全完成的为完成
select '112','完成' union all
select '113','完成' union all
select '111','未接收' union all
select '213','在操作' union all
select '212','完成' union all
select '211','完成' union all
--输入部分的，按输入的完成为完成
select '312','在操作' union all
select '313','完成' union all
select '412','完成' union all
select '411','在操作' union all
select '511','在操作' union all
select '613','在操作'
IF object_id('tempdb..#')IS NOT NULL
DROP TABLE #
;WITH tmp_Result AS (
SELECT
a.List_id
,a.F_ListID
,b.Status
FROM #A AS a
LEFT JOIN #B AS b ON a.List_id=b.List_id
)
SELECT
*
INTO #
FROM tmp_Result
SELECT
a.F_ListID
,CASE
WHEN NOT EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.Status IS NULL
)
THEN CASE
WHEN NOT EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.List_id>a.F_ListID
AND o.Status<>N'完成'
)
THEN N'完成'
WHEN EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.Status=N'在操作'
)
THEN N'在操作'
ELSE '未接收'
END
WHEN EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND Status IS NOT NULL
HAVING COUNT(1)>1
)
THEN CASE
WHEN EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.List_id>a.F_ListID
AND o.Status=N'在操作'
)
THEN N'在操作'
WHEN EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.List_id>a.F_ListID
AND o.Status=N'未接收'
)
THEN N'未接收'
ELSE N'完成'
END
ELSE (SELECT
Status
FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.Status IS NOT NULL
)
END AS Status
FROM # AS a
GROUP BY a.F_ListID
/*
F_ListID Status
---------- ----------
111 完成
211 在操作
311 在操作
411 完成
511 在操作
611 在操作
*/
