sql server 查询语句记录

分组查询连接字段

select [分组字段],stuff((select ',' + [连接字段] from  [表] where [分组字段]=a.[分组字段] for xml path('')),1,1,'') as name
from [表] a group by [分组字段]

横表根据状态查询用户加工任务

select ISNULL(ISNULL(t1.userId,t2.userId),t3.userId) userId,u.name userName,t1.titleCount,t2.titleCount as entityCount,t3.titleCount eventCount from(
        (select input_uid as userId, count(distinct a.date) as bookCount,COUNT(*) as titleCount from Edit_Register_Information a
        join News_Catalog b on a.book_id=b.book_id and a.date=b.date
        where a.input_state=2 and a.book_id=#{roleName} and <![CDATA[a.input_date>=#{begindate}]]> and <![CDATA[a.input_date<#{enddate}]]> group by  a.input_uid
        ) as t1
        full join
        (select entity_uid as userId, count(distinct a.date) as bookCount,COUNT(*) as titleCount from Edit_Register_Information a
        join News_Catalog b on a.book_id=b.book_id and a.date=b.date
        where a.entity_state=2 and a.book_id=#{roleName} and <![CDATA[a.entity_date>=#{begindate}]]> and <![CDATA[a.entity_date<#{enddate}]]> group by  a.entity_uid
        ) as t2  on t1.userId=t2.userId
        full join
        (select event_uid as userId, count(distinct a.date) as bookCount,COUNT(*) as titleCount from Edit_Register_Information a
        join News_Catalog b on a.book_id=b.book_id and a.date=b.date
        where a.event_state=2 and a.book_id=#{roleName} and <![CDATA[a.event_date>=#{begindate}]]> and <![CDATA[a.event_date<#{enddate}]]> group by  a.event_uid
        ) as t3
        on t1.userId=t3.userId or t2.userId=t3.userId
        ) left join SYS_USER u on ISNULL(ISNULL(t1.userId,t2.userId),t3.userId)=u.id_user