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
Ïà¹ØÎĵµ£º
Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......
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 ......
Ò»£®Êý¾ÝÍêÕûÐÔ
¾ÍÊÇΪÁË·ÀÖ¹Êý¾Ý¿âÖдæÔÚ²»·ûºÏÓïÒ广¶¨µÄÊý¾Ý£¬ºÍ·ÀÖ¹ÒòÊäÈëÊä³ö´íÎó¶øÔì³ÉµÄÎÞЧ²Ù×÷»òÕß´íÎóÐÅÏ¢¶øÌá³öµÄ£¬Öƶ¨µÄһЩ¶ÔÔª×é¡¢ÊôÐÔºÍÓòµÄÒ»Ð©Ô¼ÊøºÍ¹æ¶¨¡£
½â¾ö°ì·¨ÓÉ£º¹æÔò£¬Ä¬ÈÏÖµ£¬Ô¼ÊøºÍ´¥·¢Æ÷µÈ¡£
¶þ£®Ô¼Êø
......
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µÄ»ù±¾¶ÔÏóÖ÷ÒªÓг£Á¿£¬±íʾ·û£¬·Ö¸ô·û£¬±£Áô¹Ø¼ü×Ö¡£
1¡¢³£Á¿
³£Á¿ÊÇÒ»¸ö°üº¬ÎÄ×ÖÓëÊý×Ö£¬Ê®Áù½øÖÆ»òÊý×Ö³£Á¿¡£Ò»¸ö×Ö·û´®³£Á¿°üº¬µ¥ÒýºÅ('')»òË«ÒýºÅ("")×Ö·û¼¯ÖеÄÒ»¸ö»ò¶à¸ö×Ö·û¡£
Èç¹ûÏëÔÚµ¥ÒýºÅ·Ö¸ôµÄ×Ö·û´®ÖÐÓõ½µ¥¶ÀµÄÒýºÅ£¬¿ÉÒÔÔÚÕâ¸ö×Ö·ûÖÐÓû§Á¬ÐøµÄµ¥ÒýºÅ£¨¼´ÓÃÁ ......