查询进度有关问题

查询进度问题
要求全输入的,按拆分后的子段做统计，输入部分的，按输入的做统计
--数据
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&gt;a.F_ListID
                                                        AND o.Status&lt;&gt;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)&gt;1
) 
THEN CASE 
WHEN EXISTS(SELECT 1 FROM # AS o
WHERE o.F_ListID=a.F_ListID
AND o.List_id&gt;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&gt;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        在操作
*/

查询进度有关问题

热点推荐