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 &
Ïà¹ØÎĵµ£º
Ò»°ãµÄ¹«Ë¾Í¨³£»áÔÚËûÃǵÄÐÅϢϵͳ¼Ü¹¹ÖÐÒýÈë¶àÖÖÊý¾Ý¿âƽ̨£¬Í¬Ê±ÒýÈëÈýµ½ËÄÖÖ²»Í¬µÄRDBMS½â¾ö·½°¸µÄÖдóÐ͹«Ë¾Ò²²¢²»ÉÙ¼û£¬µ±È»ÕâЩ¹«Ë¾ÀïÃæµÄDBAÃÇͨ³£Ò²ÐèҪͬʱӵÓйÜÀí¶àÖÖ²»Í¬Æ½Ì¨µÄ¼¼ÄÜÁË¡£
Ö»ÔÚÒ»ÖÖÆ½Ì¨ÉÏÕ¹¿ª¹¤×÷µÄÊý¾Ý¿âר¼ÒÃÇҲͨ³£»áÆÚ´ý×ÅÔÚËûÃǵÄÏÂÒ»·Ý¹¤×÷ÖÐÄÜѧµ½µã²»Ò»ÑùµÄ¶«Î÷£¬ÄÇЩÓÐÓÂÆøµÄÈËÃÇÔòÔ¸Òâ ......
-- ˵Ã÷:
-- 1. Ҫɾ³ý±í table_name, ÐëÏÈÅжϸñíÊÇ·ñÕý±»Êý¾Ý¿âÖÐÆäËü±íËùÒýÓÃ.
-- 2. Èç¹ûδ±»ÒýÓÃ, ¿ÉÖ±½Ó DROP TABLE table_name; ·ñÔò±ØÐëÏÈɾ³ýÒýÓñíµÄÔ¼Êø, ÔÙ DROP TABLE table_name.
-- 3. ÁíÍâ, table_name ÊÇ·ñÕýÒýÓÃÆäËü±íµÄÇé¿öÎÞÐ뿼ÂÇ.
-- ´´½¨Ö÷±í
CREATE TABLE tParent
......
Ö´ÐÐ Êý¾Ý¿â²éѯʱ£¬ÓÐÍêÕû²éѯºÍÄ£ºý²éѯ֮·Ö¡£
Ò»°ãÄ£ºýÓï¾äÈçÏ£º
SELECT ×Ö¶Î from ±í WHERE ij×Ö¶Î Like Ìõ¼þ
ÆäÖйØÓÚÌõ¼þ£¬SQLÌṩÁËËÄÖÖÆ¥Åäģʽ£º
1£¬%£º±íʾÈÎÒâ0¸ö»ò¶à¸ö×Ö·û¡£¿ÉÆ¥ÅäÈÎÒâÀàÐͺͳ¤¶ÈµÄ×Ö·û£¬ÓÐЩÇé¿öÏÂÈôÊÇÖÐÎÄ£¬ÇëÔËÓÃÁ½¸ö°Ù·ÖºÅ£¨%%£©±íʾ¡£
±ÈÈç SELECT * from [user] WHERE u_na ......
SQL Server ·ÖÒ³
½¨Á¢±í£º
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar ......
SQL Server 2008ÖеıíÖµÐͲÎÊý
×÷ÕߣºAl Tenhundfeld ÒëÕß Õź£Áú¡¡
±íÖµÐͲÎÊý£¨Table-valued parameters£©ÊÇSQL Server 2008ÖÐÒýÈëµÄÒ»ÖÖÐÂÌØÐÔ£¬ËüÌṩÁËÒ»ÖÖÄÚÖõķ½Ê½£¬Èÿͻ§¶ËÓ¦ÓÿÉÒÔֻͨ¹ýµ¥¶ÀµÄÒ»Ìõ²Î»¯ÊýSQLÓï¾ä£¬¾Í¿ÉÒÔÏòSQL Server·¢ËͶàÐÐÊý¾Ý¡£
±íÖ ......