SQL ¶àÐÐÖµºÏ²¢ÎªÒ»¸öÖµ
·½·¨(1)
SELECT stuff((select ','+ltrim(ColumnName) from #A for xml path('')
),1,1,'')
/*
102,103,104,105
*/
·½·¨(2)
DECLARE @s NVARCHAR(1000)='';
SELECT @s+=ColumnName+',' from #A;
SELECT @s;
Ïà¹ØÎĵµ£º
select ks.login_name,ks.exam_name,ks.start_time,ks.end_time,cj.score
from (
select u.user_id,u.login_name,e.* from cphrms.EXAM_USER eu, cphrms.users u, cphrms.exam_info e
where eu.user_id = u.user_id and eu.exam_id = e.exam_id
) ks
left ......
-----------------------------------------------------------------------------------------------------------------------
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '¹ã¶«Ê¡')
insert into tb values('002' , '001' , '¹ãÖÝÊÐ')
insert i ......
¶à±íÁª½Ó²éѯ
Ò»¡¢¶à±íÁª½Ó²éѯµÄ·ÖÀà
¶à±íÁª½Ó²éѯʵ¼ÊÉÏÊÇͨ¹ý¸÷¸ö±íÖ®¼ä¹²Í¬ÁеĹØÁªÐÔÀ´²éѯÊý¾ÝµÄ£¬ËüÊǹØÏµÊý¾Ý¿â²éѯ×îÖ÷ÒªµÄÌØÕ÷¡£
Áª½Ó²éѯ¿É·ÖΪÈý´óÀ࣬·ÖÁíΪ£º
1£® ÄÚÁª½Ó¡£
2£® ÍâÁª½Ó¡£
3£® ½»²æÁª½Ó¡£
ÄÇôÎÒÃÇÒ»ÆðÀ´¿´Ò»ÏÂÈçºÎʹÓö ......
ÈçºÎÅжÏ×Ö¶ÎÊÇ·ñ´æÔÚ
if col_length('±íÃû','×Ö¶Î1') is null ALTER TABLE ±íÃû ADD ×Ö¶Î1 Nvarchar(50) if col_length('±íÃû','×Ö¶Î2') is null ALTER TABLE ±íÃû ADD ×Ö¶Î2 Nvarchar(50) ");
ɾ³ý×Ö¶Î
if col_length('±íÃû','×Ö¶Î1,') is not null ALTER TABLE ±íÃû drop c ......