T-SQL - 根据两列获取数据

I want to create the following output from below image:

我想从下面的图像创建以下输出：

The input data is from a view (Select * from test). The goal is to get all data where the progress column contains the text tbd and the counter number is 1.

输入数据来自视图（Select * from test）。目标是获取进度列包含文本tbd且计数器编号为1的所有数据。

Can this be solved with case when statement?

这可以用声明的情况解决吗？

Since sqlfiddle is not working here's the schema:

由于sqlfiddle不在这里工作的架构：

CREATE TABLE test
(
    [ID] [int] NOT NULL,
    [Counter] [int] NOT NULL,
    [Name] nvarchar(200) NULL,
    [Progress] nvarchar(200) NOT NULL
)

INSERT INTO test
VALUES (1, 1, 'userA', 'tbd'),
       (1, 2, 'userB', 'done'),
       (1, 3, 'userC', 'tbd'),
       (2, 1, 'userB', 'done'),
       (2, 5, 'userA', 'tbd'),
       (3, 1, 'userD', 'tbd'),
       (3, 2, 'userA', 'done'),
       (3, 7, 'userC', 'tbd'),
       (3, 11, 'userB', 'tbd')

I couldn't get it to work.

我无法让它发挥作用。

Hope you can help me out.

希望你能帮助我。

Thanks alot.

非常感谢。

3 个解决方案

#1

Using Exists clause you can achieve your desired result.

使用Exists子句可以获得所需的结果。

Query

询问

SELECT
    *
FROM test t
WHERE EXISTS (SELECT 1 FROM test
            WHERE t.ID = ID
            AND progress = 'tbd'
            AND counter = 1)

Result

结果

ID  Counter  Name    Progress
-----------------------------
1      1     userA   tbd
1      2     userB   done
1      3     userC   tbd
3      1     userD   tbd
3      2     userA   done
3      7     userC   tbd
3      11    userB   tbd

And another alternative solution is simple SELF JOIN like this-

另一种替代解决方案是简单的SELF JOIN，就像这样 -

Query

询问

SELECT
    le.ID, le.Counter, le.Name, le.Progress
    FROM test le
INNER JOIN test re ON le.ID = re.ID
WHERE re.progress = 'tbd'
    AND re.counter = 1

Above query returns the same result.

上面的查询返回相同的结果。

#2

Simply try:

只需尝试：

select * from test where progress = 'tbd' and counter = 1

#3

Maybe this will help you:

也许这会对你有所帮助：

select  *,
        row_number() over(partition by    Progress order by   Counter)
        rowID
into #tmp
from    Test

select  *
from    #tmp
where   ID in(
            select  ID
            from    #tmp
            where   Counter = 1
                    and Progress = 'tbd'
        )
order by    ID,
            rowID