SQLServer多个子查询结果拼接和分组查询最新一组数据

多个子查询结果拼接

select * from (
select count(id) cont1 from table_a where delflag = 0 
) a
FULL JOIN (
select count(id) cont2 from table_b where delflag = 0
) b on 1=1
FULL JOIN (
select count(id) cont3 from table_c where delflag = 0 
) c on 1=1

分组取最新一条

select * from (
SELECT  ROW_NUMBER() over (PARTITION By my_type order by my_index desc) as inde,*
FROM (
SELECT my_type,my_index FROM TABLE_a
) c
) t
where inde = 1
--根据my_type分组,根据my_index排序,排序后取第一个(inde = 1)

分组查询最新一组数据

select top 7 * from (
select row_number() over(partition by [c1] order by [c2] desc) as rid,*
from table_A where type = '1' 
) as tt where rid =1 
order by case hymc when '其他' then 999 else 1 end asc ,CAST (c3 AS NUMERIC(10,2) ) desc
--备注：根据c1分组 ，根据c2排序，最终结果中“其他”排在最后，再根据c3倒序