解决方案 »
- 请各位大哥看看有什么错误
- 在ssms中可以连接中去,但是在程序中就不行。郁闷。。咋回事呢?
- 几行生成自增列的代码,功能没问题,想问一下为何可以这样用,语法依据是什么,有代码
- 关于sp_configure的问题,求解?
- 数据库连接备份时问题?
- 怎样在SqlServer中,用select语句取特定一行?在线等待
- 怎样在win 2000 professionnal下建立sql server 2000的数据源?
- 一个select语句的写法
- Access数据库中如何定义各表之间的关系类型?
- 存储过程中如何判断一条语句执行正确?
- vs2010连接sql server2008相关问题
- SQL 2000数据库还原
drop table test
go
create table test
(
id int,
name varchar(10),
[key] varchar(20)
)
go
insert test
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go
select
id,
a.name,
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<=len([key])
and type='p'
and substring(','+[key],number,1)=','
/*
id name key
-----------------------------
1 lisa li
1 lisa is
1 lisa sa
2 sophia ab
2 sophia cd
2 sophia ef
3 lori 12
3 lori 34
3 lori 23
*/
对指定字符串分割
(
ID VARCHAR(50),
VAL VARCHAR(50)
)INSERT INTO #TB1
( ID, VAL )
SELECT 'A','aa,bb,cc'
UNION ALL
SELECT 'B','!!,%%,@@'
UNION ALL
SELECT 'C','EE,aA,123'SELECT A.ID,
B.V
FROM (SELECT ID,CONVERT(XML,'<root><v>' + REPLACE(Val,',','</v><v>')+'</v></root>') AS Val FROM #TB1) A
OUTER APPLY (
SELECT t.c.value('.','varchar(50)') AS V
FROM A.Val.nodes('/root/v') AS t(c)) B
nice! 请问下第二个问题怎么破?
cte(leaf,ID,PID,levelR) AS (
--从叶子开始递归
SELECT ID,ID,PID,1
FROM digui
WHERE NOT EXISTS (SELECT *
FROM digui c
WHERE c.PID = digui.ID)
--向上递归
UNION ALL
SELECT c.leaf,p.ID,p.PID,c.levelR+1
FROM digui p
JOIN cte c ON p.ID = c.PID
--加根节点。如果有 ('zgID',NULL) 这样的记录,下面就不需要了。
UNION ALL
SELECT c.leaf,c.PID,NULL,c.levelR+1
FROM cte c
WHERE c.PID IS NOT NULL
AND NOT EXISTS (SELECT *
FROM digui p
WHERE p.ID = c.PID)
)
,t1 (leaf,Levels) AS (
SELECT leaf, MAX(levelR)
FROM cte
GROUP BY leaf
)
,t2 (leaf,ID,level) AS (
SELECT c.leaf, c.ID, t1.Levels-c.levelR+1
FROM cte c
JOIN t1 ON t1.leaf = c.leaf
)
SELECT [1], [2], [3], [4]
FROM t2
PIVOT (
MAX(ID)
FOR LEVEL IN ([1], [2], [3], [4])
) AS p