SQL 2005ÒçÓÃÖ®£º·Ö²ðÁÐÖµ
ÎÊÌâÃèÊö£º
Óбítb, ÈçÏÂ:
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
Óû°´,·Ö²ðvaluesÁÐ, ·Ö²ðºó½á¹ûÈçÏÂ:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
1. ¾ÉµÄ½â¾ö·½·¨
SELECT TOP 8000
id = IDENTITY(int, 1, 1)
INTO #
from syscolumns a, syscolumns b
SELECT
A.id,
SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
from tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #
-- 2. еĽâ¾ö·½·¨
-- ʾÀýÊý¾Ý
DECLARE @t TABLE(id int, [values] varchar(100))
INSERT @t SELECT 1, 'aa,bb'
UNION ALL SELECT 2, 'aaa,bbb,ccc'
-- ²éѯ´¦Àí
SELECT
A.id, B.value
from(
SELECT id, [values] = CONVERT(xml,
'<root><v>' + REPLACE([values], ',', '</v><v>') + '</v></root>')
from @t
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)')
from A.[values].nodes('/root/v') N(v)
)B
/*--½á¹û
id value
----------- --------
1 aa
1 bb
2 aaa
2 &nbs
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
¡¡¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
......
Êý¾Ý¿âµÄÐÔÄܲâÊÔ¿ÉÒÔ°ïÖúÄãÌáǰ֪µÀÄãµÄϵͳµÄ¸ºÔØÄÜÁ¦£¬¿ÉÒÔ°ïÖúÄã¸Ä½øÏµÍ³µÄʵʩ»òÉè¼Æ£¬¿ÉÒÔ°ïÖúÄãÈ·¶¨Ò»Ð©Éè¼ÆºÍ±à³ÌÔÔò. µ«ÊÇ£¬ÕâÀïÃæÒ²ÓÐÏÝÚå. Èç¹û²»Ð¡ÐÄ£¬Äã»á×Ô¼º°Ñ×Ô¼ºÏݽøÈ¥£¬È´×îÖÕ²»Ã÷°×ÊÇʲôÔÒò. ÕâÀÎÒÄÃһλÏÈÉúΪÀý£¬À´¿´¿´ËûÔõô×Ô¼º°Ñ×Ô¼º¸ãºýÍ¿µÄ.
×î½ü, ÏëÆðÔÚ´æ´¢¹ý³ÌÖо¿¾¹ÊÇʹÓÃÁÙʱ±í»¹ÊÇÊ ......
I'm continually trying to track down what service packs are installed on various SQL Servers I support. I can never find the right support page on Microsoft's site. So here's an article with all the SQL Server version information I can track down. If you know of any older versions or can help me fil ......
Suppose we have a recursive hierarchy stored in a relational database and we want to write it to XML. This might be for a variety of reasons – e.g. as a pre-cached input to a UI control, to export to another system using a pre-defined format, etc.
In SQL Server 2000, in order to ......
DB2 SQL PL
SQL PLÊÇDB2ËùÖ§³ÖµÄ¹ý³Ì»¯ÓïÑÔ£¬ËüÊÇSQL/PSM±ê×¼µÄÒ»¸ö×Ó¼¯¡£Æä¸ù¾ÝÓ¦Ó÷¶Î§²»Í¬£¬ÓÖ·ÖΪInline SQL PL£¬Embeded SQL PLºÍCompiled SQL PL¡£
Inline SQL PL
ÊÊÓ÷¶Î§£º´¥·¢Æ÷¡¢º¯ÊýºÍ·½·¨£¬Ö§³Ö²¿·ÖSQL PL£¬Ê¹ÓÃʱҪעÒâһЩÏÞÖÆ
Óï·¨¹æÔò£ºBEGIN ATOMIC ... END
Embeded SQL PL
ÊÊÓ÷¶Î§£ºÇ¶Èëʽ£¬Åäº ......