Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL Server2005 ÖÐÓà Pivot Éú³É½»²æ±í

×î½üÔÚ×öÒ»¸öÊý¾Ýͳ¼Æ£¬ÒªÇó¶ÔÒ»¸ö±íÖеÄÊý¾Ý°´ÕÕÁ½¸öά¶È³ÊÏÖ£¬Ò²¾ÍÊÇ´«Í³µÄ½»²æ±í
±ÈÈ磬ÓÐÒ»¸öÎÊÌâ±í£¬ÓÐÈý¸ö×ֶΣ¬£¨±êÌâ¡¢ÎÊÌâÀà±ð¡¢ÎÊÌâ״̬£©
ÒªÇó°´ÕÕ²»Í¬µÄÀà±ð£¬·Ö±ðͳ¼Æ´¦¸÷¸ö״̬µÄÎÊÌâÊýÁ¿£¨È磺²úÆ·ÎÊÌâÖÐδ´¦ÀíµÄÊýÁ¿¡¢·þÎñÎÊÌâÖÐÒÅÁôÎÊÌâÊýÁ¿µÈµÈ£©¡£
¾­¹ý²éÕҺͳ¢ÊÔ£¬ÖÕÓÚÉú³ÉÁ˽á¹û£¬ÏÖÔÚ·ÖÏí¸ø´ó¼Ò¡£
ͨ¹ý Sql 2005 ÖÐµÄ Pivot º¯Êý£¬¿ÉÒÔ·½±ãµÄÖÆ×÷½»²æ±í¡£
¹ØÓÚPivotµÄÓ÷¨£¬¾Í²»Ïêϸ˵ÁË£¬´ó¼Ò¿ÉÒÔ°Ù¶Èһϣ¬»òÕß¿´sql serverµÄ°ïÖúÎĵµ
ÏÂÃæÊÇ´úÂ벿·Ö
 --ÎÊÌâ·ÖÀà±í
CREATE TABLE [dbo].[QuestionClass](
[QuestionClassID] [int] IDENTITY(1,1) NOT NULL,
[QuestionClassName] [nvarchar](50) NULL,
CONSTRAINT [PK_QuestionClass] PRIMARY KEY CLUSTERED
(
[QuestionClassID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--ÎÊÌâ״̬±í
CREATE TABLE [dbo].[QuestionState](
[QuestionStateID] [int] IDENTITY(1,1) NOT NULL,
[QuestionStateName] [nvarchar](50) NULL,
CONSTRAINT [PK_QuestionState] PRIMARY KEY CLUSTERED
(
[QuestionStateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--ÎÊÌâ¼Ç¼±í
CREATE TABLE [dbo].[Question](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[Topic] [nvarchar](50) NULL,
[ClassID] [int] NULL,
[StateID] [int] NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--³õʼ»¯×´Ì¬Êý¾Ý
insert into QuestionState(QuestionStateName)
select 'δ´¦Àí' union all
select 'ÒÑ´¦Àí' union all
select 'ÎÞЧ·´À¡'

--³õʼ»¯Àà±ðÊý¾Ý
insert into QuestionClass(QuestionClassName)
select '²úÆ·ÎÊÌâ' union all
select 'ÏúÊÛÎÊÌâ' union all
select '·þÎñÎÊÌâ' union all
select 'ÆäËûÎÊÌâ'
--³õʼ»¯ÎÊÌâÊý¾Ý
insert into Question(Topic,ClassID,St


Ïà¹ØÎĵµ£º

PL/SQL¿ª·¢Öж¯Ì¬SQLµÄʹÓ÷½·¨

ÄÚÈÝÕªÒª£ºÔÚPL/SQL¿ª·¢¹ý³ÌÖУ¬Ê¹ÓÃSQL£¬PL/SQL¿ÉÒÔʵÏִ󲿷ݵÄÐèÇ󣬵«ÊÇÔÚÄ³Ð©ÌØÊâµÄÇé¿öÏ£¬ÔÚPL/SQLÖÐʹÓñê×¼µÄSQLÓï¾ä»òDMLÓï¾ä²»ÄÜʵÏÖ×Ô¼ºµÄÐèÇ󣬱ÈÈçÐèÒª¶¯Ì¬½¨±í»òij¸ö²»È·¶¨µÄ²Ù×÷ÐèÒª¶¯Ì¬Ö´ÐС£Õâ¾ÍÐèҪʹÓö¯Ì¬SQLÀ´ÊµÏÖ¡£±¾ÎÄͨ¹ý¼¸¸öʵÀýÀ´ÏêϸµÄ½²½â¶¯Ì¬SQLµÄʹÓᣡ¡¡¡
¡¡¡¡±¾ÎÄÊÊÒ˶ÁÕß·¶Î§£ºOracle³ ......

SQL×Ô¶¯Éú³ÉʵÌåÀà

declare @tablename varchar(20)
select @tablename = 'o_ems'
-------------Éú³É±äÁ¿,ʹÓñí¸ñÐÎʽ(ctrl + D)Êä³ö£¬È¡µÚÒ»¸ö×ֶεÄÖµ----------------------------------
select 'private '+  
Case when t.name='varchar' Then 'string'
    when t.name='char' Then 'string' &nb ......

oraleÖÐsqlÓï¾ä¶ÔnullÖµµÄ´¦Àí

µÚÒ»ÖÖ·½·¨£ºÊ¹ÓÃNVLº¯Êý´¦ÀíNULLÖµ¡£
ÆäÓï·¨¸ñʽÊÇNVL(exp1,exp2)¡£ÆäÖвÎÊýexp1ºÍexp2¿ÉÒÔʹÈÎÒâÊý¾ÝµÄÀàÐÍ£¬µ«Á½ÕßÊý¾ÝÀàÐͱØÐëÆ¥Å䡣ʾÀý£ºselect ename,sal,comm,sal+nvl(comm,0) as salary from emp;
µÚ¶þÖÖ·½·¨£ºÊ¹ÓÃNVL2º¯Êý´¦ÀíNULLÖµ¡£
ÆäÓï·¨¸ñʽÊÇNVL2(exp1,exp2,exp3)¡£ÕâÊÇoracle9iÐÂÔö¼ÓµÄº¯Êý¡£Èç¹ûexp1 ......

PL/SQL ѧϰ


1¡¢INSTR4(string1,string2[,a][,b]) ·µ»Østring1Öаüº¬string2µÄλÖÃaºÍbÊÇÒÔUCS4´úÂëµãΪµ¥Î»¡£
ÒÔÉϺ¯Êý·µ»Østring1Öаüº¬string2µÄλÖᣴÓ×ó±ß¿ªÊ¼É¨Ãèstring1,ÆðʼλÖÃÊÇA¡£Èç¹ûAΪ¸ºÊýÄÇô´ÓÓұ߿ªÊ¼É¨Ãè¡£µÚB´Î³öÏÖµÄλÖý«±»·µ»Ø¡£AºÍBȱʡ¶¼Îª1£¬¼´·µ»ØÔÚ
string1ÖеÚÒ»´Î³öÏÖstring2µÄλÖá£Èç¹ûstring2ÔÚAº ......

SQLÖ´ÐÐ˳Ðò

Ò»¡¢sqlÓï¾äµÄÖ´Ðв½Ö裺
1£©Óï·¨·ÖÎö£¬·ÖÎöÓï¾äµÄÓï·¨ÊÇ·ñ·ûºÏ¹æ·¶£¬ºâÁ¿Óï¾äÖи÷±í´ïʽµÄÒâÒå¡£
2£© ÓïÒå·ÖÎö£¬¼ì²éÓï¾äÖÐÉæ¼°µÄËùÓÐÊý¾Ý¿â¶ÔÏóÊÇ·ñ´æÔÚ£¬ÇÒÓû§ÓÐÏàÓ¦µÄȨÏÞ¡£
3£©ÊÓͼת»»£¬½«Éæ¼°ÊÓͼµÄ²éѯÓï¾äת»»ÎªÏàÓ¦µÄ¶Ô»ù±í²éѯÓï¾ä¡£
4£©±í´ïʽת»»£¬ ½«¸´Ô SQL ±í´ïʽת»»Îª½Ï¼òµ¥µÄµÈЧÁ¬½Ó±í´ïʽ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ