´Ó¡¾¸÷´óÈí¼þ¹«Ë¾±ÊÊÔѹÖáÌ⡿ѧϰSQLÓï¾ä
´Ó²©¿ÍÔ°Öп´µ½Ò»ÆªÎÄÕ£¬½éÉÜ´óÈí¼þ¹«Ë¾ÃæÊÔʱ³£³£»á³öµÄÁ½µÀSQLÌ⣨¼û¸½Â¼£©¡£
ÎÒ¾õµÃÊÜÒæºÜ¶à£¬ÔÚ´Ë֮ǰ£¬ÎÒÒ»Ö±¾õµÃ£¬SQL2008ËƺõÌṩÁËÕâ·½ÃæµÄÖ§³Ö£¬µ«¸üµÍµÄ°æ±¾£¬°üÀ¨2005£¬·ÇÓαê×ö²»³öÀ´£¨Ë®Æ½¹»²Ë£©¡£×ܽáÐĵÃÈçÏ£º
1¡¢ Ç¿´óµÄgroup by
1 select stdname,
2 isnull(sum( case stdsubject when ' »¯Ñ§ ' then Result end), 0 ) [»¯Ñ§],
3 isnull(sum( case stdsubject when ' Êýѧ ' then Result end), 0 ) [Êýѧ],
4 isnull(sum( case stdsubject when ' ÎïÀí ' then Result end), 0 ) [ÎïÀí],
5 isnull(sum( case stdsubject when ' ÓïÎÄ ' then Result end), 0 ) [ÓïÎÄ]
6 from #student
7 group by stdname
ÔÚÕâÀgroup byÓësum + case½áºÏ£¬¿ÉÒÔ½«±í1ÖеļǼ£¨ÐУ©±ä³É±í2µÄ×ֶΣ¨ÁУ©¡£SumÀïÃæÈç¹ûûÓÐcase£¬ÄÇô³öÀ´µÄÖµ£¬Ö»ÄÜÊÇÈ«²¿¿ÆÄ¿µÄ×ܺͣ¬ÓÃÁËcaseÒԺ󣬾ÍÊÇij¿ÆµÄ³É¼¨£»È»ºóÕâÀïÓÃÁ˺ü¸¸ösum£¬Ã¿¸ö¿ÆÄ¿Ò»¸ösum£¬ÓÚÊDZí1Öб¾À´Ä³ÈËij¿ÆÕ¼Ò»Ìõ¼Ç¼µÄ“ÐД¾Í±ä³ÉÁ˱í2ÀïijÈËÒ»Ìõ¼Ç¼£¬Ã¿¿Æ×öÒ»¸ö×Ö¶ÎÁË¡£
ÕâÖÖÐÄ˼ÇÉÃîºÍ¶ÔÓï·¨µÄÊìÁ·ÔËÓÃÈÃÈË»÷½ÚÔÞ̾¡£
2¡¢ ÀûÓÃselect from (select from)µÄģʽÉú³ÉSQLÓï¾ä
1 declare @sql varchar( 4000 )
2 set @sql = ' select stdname '
3 select @sql = @sql + ' ,isnull(sum(case stdsubject when ''' + stdsubject + ''' then Result end),0) [ ' + stdsubject + ' ] '
4 from (select distinct stdsubject from #student) as a
5 select @sql = @sql + ' from #student group by stdname '
6 print @sql
7 exec(@sql)
ΪÁË×Ô¶¯Ð´ÉÏËùÓеĿÆÄ¿£¬ÕâÀïÏȽ«¿ÆÄ¿ÐÅÏ¢ÌáÁ¶³öÀ´£º
4 from (select distinct stdsubject from #student) as a
ÀûÓÃ֮ƴ½ÓÉú³ÉSQLÓï¾ä¡£µ±È»ÏÖʵÖУ¬Èç¹û#student±íºÜ´ó£¬ÕâÖÖ×ö·¨²¢²»Í×£¬Ó¦¸Ã¶¼ÓÐÒ»¸öרÃŵĿÆÄ¿Àà±ð±íµÄ¡£
3¡¢ ÔÚÁÙʱ¿âÖÐÌáÁ¶³ö×Ö¶ÎÃû¡£ÁÙʱ±íÊÇÕæʵ´æÔÚµÄ±í£¬±£´æÔÚ[tempdb]ÖУ¬¿ÉÒÔÀûÓÃobject_id('tempdb.dbo.±íÃû')
Ïà¹ØÎĵµ£º
CREATE proc [dbo].[proc_DeleteTemplet] (@templeId varchar(15),@errorMessage varchar(50) output)
as
begin
declare @error int
set @error =0
begin tran
delete from tc_templet_Head where fBillNo=@templeId
set @error=@error+@@error
delete from tc_templet_ ......
private void Create_Pz(string Sourcename)
{
string OutMsg;
SqlParameter[] sortPara = { new SqlParameter("@ps_IsType", Pz ......
SQL:1999Á¬½Ó
SQL:1999Á¬½ÓÔÚÓï·¨ÉϺʹ«Í³µÄORACLEÁ¬½ÓÓÐןܴóµÄ²»Í¬¡£SQL:1999Á¬½ÓµÄÀàÐÍÔÚfromÓï¾äÖÐÖ¸Ã÷¡£ORACLE 9IÖ§³ÖµÄSQL:1999Á¬½Ó°üÀ¨£º
l CROSSÁ¬½Ó
l NATURALÁ¬½Ó
l ......
SqlserverÕⶫÎ÷ûÉÙ¸úËû´ò½»µÀ,¸Õѧ¿ª·¢ÓïÑÔʱ¾ÍÒѾ¸úËû½Ó´¥ÁË,²»ÒªËµºÜ¾«Í¨,µ«¾ÍÓï·¨»¹ËãÊÇÊìϤ,µ«ÏÖÔÚ¿ªÊ¼Ð´C#,·¢ÏÖºÜC#¶àÁËÒ»ÖÖSqlServerµÄ²ÎÊý´«È뷽ʽ,ÒÔÍùÎÒÃdz£³£¶ÔÒª´«²ÎµÄSqlÓï¾ä¶¼ÊÇÖ±½Óͨ¹ýÆ´´ÕSqlÓï¾äµÄ·½Ê½À´ÊµÏÖ,µ«ÏÖÔÚC#ΪʲôҪרÃÅ×öÒ»¸öSql²ÎÊýµÄÀàÀ´ÊµÏÖÄØ,¾¹ý²éÕÒ²ÅÖªµÀ,ÕâÑù×öÊÇÓеÀÀíµÄ,Ò»Ê ......