SQL server 分组 group_contant 函数实现

sql Server STUFF()函数_JessieZao的博客-CSDN博客_sqlserver stuffSTUFF()函数将一个字段的所有值拼接在一个结果里select a.pkidStr from(SELECT STUFF(( SELECT ',' +convert(VARCHAR, PKID) FROM (select PKID from MN_KIND_INFO)tFOR XML PATH('')), 1, 1, '') AS pkidStr)a查询所有的pkid：将查询出的pkid，拼接在一起：...https://blog.csdn.net/JessieZao/article/details/108880252

Select
　　RegionID，
　　STUFF(
　　　　　　　(
　　　　　　　　　SELECT ',' + T.c1
　　　　　　　　　FROM #tmp T
　　　　　　　　　WHERE A.regionid = T.regionid
　　　　　　　　　FOR XML PATH('')
　　　　　　　), 1, 1, ''
　　　　　) as group_concat 
FROM #tmp A
Group by  RegionID

SELECT TOP
	10 * 
FROM
	(
	SELECT
		row_number ( ) OVER ( ORDER BY orderResult ASC, id DESC ) rownumber,* 
	FROM
		(
		SELECT
			t1.id AS id,
			t1.partition_id AS partitionId,
			t1.node_role_code AS nodeRoleCode,
			t1.cur_execute_userid AS curExecuteUserid,
			t1.check_result AS checkResult,
			t1.create_datetime AS createDatetime,
			t1.finish_state AS finishState,
			t1.link_name AS linkName,
			t1.sec_id AS secId,
			t1.unit_name AS unitName,
			t1.pro_name AS proName,
			t1.sec_name AS secName,
			t1.cu_name AS cuName,
			t1.cp_name AS cpName,
			t1.ccp_name AS ccpName,
			t1.ck_name AS ckName,
			t1.cck_name AS cckName,
			
			
			-- 改动
			STUFF((
						select ','+CAST( id  AS VARCHAR) from qc_file where partition_id = t1.partition_id 　 FOR XML PATH('')

						
						), 1, 1, '') as imagId ,
						
			-- 改动			
						
						
			t2.imgId AS imgId,
			t1.lastCheckStateName AS lastCheckStateName,
			t1.type_url AS typeUrl,
			(
			CASE
					T1.check_result 
					WHEN '5' THEN
					'1' 
					WHEN '2' THEN
					'2' 
					WHEN '1' THEN
					'3' 
					WHEN '4' THEN
					'4' 
					WHEN '3' THEN
					'5' ELSE '5' 
				END 
				) AS orderResult 
			FROM
				(
					(
					SELECT
						t1.id,
						t1.partition_id,
						t1.node_role_code,
						t1.cur_execute_userid,
						t3.check_result,
						t1.create_datetime,
						t1.link_name,
						t2.finish_state,
						t2.sec_id,
						t2.unit_name,
						t2.pro_name,
						t2.sec_name,
						t2.cu_name,
						t2.cp_name,
						t2.ccp_name,
						t2.ck_name,
						t2.cck_name,
						t3.check_state_name AS lastCheckStateName,
						t3.type_url 
					FROM
						( SELECT * FROM qc_flow_record WHERE id IN ( SELECT MIN ( id ) FROM qc_flow_record WHERE disabled = 0 AND cur_execute_userid = '2012' GROUP BY partition_id ) ) t1,
						( SELECT * FROM qc_partition ) t2,
						v_max_flow_record t3 
					WHERE
						t1.partition_id= t2.id 
						AND t1.cur_execute_userid = '2012' 
						AND t1.check_result IN ( 3, 4 ) 
						AND t1.partition_id= t3.partition_id 
						AND t1.pro_id = '275' 
					) t1
					LEFT JOIN (
						SELECT
							id AS imgId,
							connect_key AS connect_key,
							partition_id AS filePartitionId 
						FROM
							qc_file 
						WHERE
							disabled = 0 
							
							
							-- 改动
							AND id IN ( SELECT  id  FROM qc_file WHERE disabled = 0 GROUP BY partition_id,id ) 
							-- 改动
							
							
					) t2 ON t1.partition_id= t2.filePartitionId 
				) 
			) A 
		) B 
WHERE
	rownumber > 0