sql server
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.
Can this be solved with case when statement?
Since sqlfiddle is not working here's the schema:
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.
Using Exists clause you can achieve your desired result.
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-
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.
只需尝试：
select * from test where progress = 'tbd' and counter = 1
也许这会帮助你：
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
上一篇: 如何在angularjs中编写字符串格式，就像c＃一样？
下一篇: sql服务器