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

SQL SERVER£ºselect ×Ö·û´®Æ´½ÓÎÊÌâ

Ò»¡¢ÎÊÌâ
select UserID,LastName,FirstName,UserName from SYSUser
UserID LastName FirstName UserName
------------------------------------------------------
1      A        B        C
2      A1      B1        C1
3      A2      B2        C2
¡¡¡¡ÎÒÏë°ÑÕâ¸öselect³öÀ´µÄ¼Í¼¼¯Æ´½Ó³ÉÒ»¸ö×Ö·û´®¡£Ó¦¸ÃÔõôʵÏÖ£¿ÀýÈ磺
strUserName=C,c2,c2
¡¡¡¡¶þ¡¢½â¾ö
¡¡¡¡SQL codeÎÊÌâÃèÊö£ºÎÞÂÛÊÇÔÚsql 2000£¬»¹ÊÇÔÚ SQL 2005 ÖÐ,¶¼Ã»ÓÐÌṩ×Ö·û´®µÄ¾ÛºÏº¯Êý£¬ËùÒÔ£¬µ±ÎÒÃÇÔÚ´¦ÀíÏÂÁÐÒªÇóʱ£¬»á±È½ÏÂé·³£º
¡¡¡¡Óбítb, ÈçÏ£º
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
¡¡¡¡ÐèÒªµÃµ½½á¹û£º
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
¡¡¡¡¼´£¬ group by id, Çó value µÄºÍ£¨×Ö·û´®Ïà¼Ó£©
¡¡¡¡·½·¨1¡¢ ¾ÉµÄ½â¾ö·½·¨
-- 1. ´´½¨´¦Àíº¯Êý
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    from tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- µ÷Óú¯Êý
SELECt id, values=dbo.f_str(id)
from tb
GROUP BY id
¡¡¡¡·½·¨2¡¢ÐµĽâ¾ö·½·¨
¡¡¡¡1¡¢Ê¾ÀýÊý¾Ý
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
¡¡¡¡2¡¢²éѯ´¦Àí
SELECT * from(
    SELECT DISTINCT id
    from @t
)A
OUTER APPLY(
    SELECT
    &nbs


Ïà¹ØÎĵµ£º

½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖÆºÍÊÂÎñ¸ôÀë¼¶±ð

Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......

sqlµ÷ÓÅÖ® ѰÕÒÎÒÃÇ×î¸ÐÐËȤµÄÓï¾ä

Tuning an Application / Reducing Load
If your whole application is performing suboptimally, or if you are attempting to
reduce the overall CPU or I/O load on the database server, then identifying
resource-intensive SQL involves the following steps:
1. Determine which period in the day you would ......

SQL——Êý¾ÝÍêÕûÐÔ


Ò»£®Êý¾ÝÍêÕûÐÔ
     ¾ÍÊÇΪÁË·ÀÖ¹Êý¾Ý¿âÖдæÔÚ²»·ûºÏÓïÒ广¶¨µÄÊý¾Ý£¬ºÍ·ÀÖ¹ÒòÊäÈëÊä³ö´íÎó¶øÔì³ÉµÄÎÞЧ²Ù×÷»òÕß´íÎóÐÅÏ¢¶øÌá³öµÄ£¬Öƶ¨µÄһЩ¶ÔÔª×é¡¢ÊôÐÔºÍÓòµÄÒ»Ð©Ô¼ÊøºÍ¹æ¶¨¡£
     ½â¾ö°ì·¨ÓÉ£º¹æÔò£¬Ä¬ÈÏÖµ£¬Ô¼ÊøºÍ´¥·¢Æ÷µÈ¡£
¶þ£®Ô¼Êø
    ......

´æ´¢¹ý³Ì£¬ Éú³ÉSQLÓï¾ä

USE [sfaecrm]
GO
/****** ¶ÔÏó:  StoredProcedure [dbo].[proc_insert]    ½Å±¾ÈÕÆÚ: 05/29/2009 06:16:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_insert] (@tablename varchar(256))
as
begin
        set ......

SQL ServerÖеÄT SQLµÄ»ù±¾¶ÔÏó


    SQLµÄ»ù±¾¶ÔÏóÖ÷ÒªÓг£Á¿£¬±íʾ·û£¬·Ö¸ô·û£¬±£Áô¹Ø¼ü×Ö¡£
 1¡¢³£Á¿
 ³£Á¿ÊÇÒ»¸ö°üº¬ÎÄ×ÖÓëÊý×Ö£¬Ê®Áù½øÖÆ»òÊý×Ö³£Á¿¡£Ò»¸ö×Ö·û´®³£Á¿°üº¬µ¥ÒýºÅ('')»òË«ÒýºÅ("")×Ö·û¼¯ÖеÄÒ»¸ö»ò¶à¸ö×Ö·û¡£
 Èç¹ûÏëÔÚµ¥ÒýºÅ·Ö¸ôµÄ×Ö·û´®ÖÐÓõ½µ¥¶ÀµÄÒýºÅ£¬¿ÉÒÔÔÚÕâ¸ö×Ö·ûÖÐÓû§Á¬ÐøµÄµ¥ÒýºÅ£¨¼´ÓÃÁ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ