比如有如下数据：
id name age
1 ac1 5
2 bc1 10
3 cd2 8
4 dd2 18
查询过后
id name age 汇总
1 ac1 5 2
3 cd2 8 2 就是根据name列只要含有c1或d2就认为是相同的数据来分组，而用count时要一模一样的。像这样的查询语句要怎么写呢？谢谢！
------解决思路----------------------
/* 测试数据
WITH table1(id,name,age)AS(
SELECT 1,'ac1',5 UNION ALL
SELECT 2,'bc1',10 UNION ALL
SELECT 3,'cd2',8 UNION ALL
SELECT 4,'dd2',18
)*/
SELECT b.*,
a.cnt 汇总
FROM (
SELECT RIGHT(name,2) code,
COUNT(*) cnt
FROM table1
GROUP BY RIGHT(name,2)
) a
CROSS APPLY (
SELECT TOP 1 *
FROM table1
WHERE RIGHT(name,2) = a.code
ORDER BY id
) b
id name age 汇总
----------- ---- ----------- -----------
1 ac1 5 2
3 cd2 8 2
------解决思路----------------------
我也写个不一样的：
WITH table1(id,name,age)AS(
SELECT 1,'ac1',5 UNION ALL
SELECT 2,'bc1',10 UNION ALL
SELECT 3,'cd2',8 UNION ALL
SELECT 4,'dd2',18
)
select id,name,age, 汇总
from
(
select *,
row_number() over(partition by right(name,2) order by id) as rn,
count(*) over(partition by right(name,2)) as 汇总
from table1
)t
where rn = 1
/*
id name age 汇总
1 ac1 5 2
3 cd2 8 2
*/