sql 序列计数

Question

我有一张表如下

userid    answer

 1         true

 1         true

 1         false

 1         true

 1         true

 1         true  

 2         true

 1         true

我想获得每个用户的最新真实序列计数

所以我会得到

userid   count

1         4
2         1

请帮忙

Answer 1

WITH Answers
AS
(
SELECT 1 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 2 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 3 AS xxxid , 1 AS userid, 'false' AS answer UNION ALL
SELECT 4 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 5 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 6 AS xxxid , 1 AS userid, 'true' AS answer UNION ALL
SELECT 7 AS xxxid , 2 AS userid, 'true' AS answer UNION ALL
SELECT 8 AS xxxid , 1 AS userid, 'true' AS answer )

SELECT   userid,
         COUNT(*) AS [COUNT]
FROM     Answers A
WHERE    NOT EXISTS
         (SELECT *
         FROM    Answers a2
         WHERE   answer    = 'false' /*change this to 0 if using bit datatype*/
         AND     a2.userid = a.userid
         )
OR       xxxid >
         (SELECT MAX(xxxid)
         FROM    Answers a2
         WHERE   answer    = 'false' /*change this to 0 if using bit datatype*/
         AND     a2.userid = a.userid
         )
GROUP BY userid