解决方案 »
- select查询语句
- 查看数据库中各表的关系
- |zyciis| 我用分页控件进我的表内容进行分页,那现在我想知道某一个ID号到底在哪一页中呢 谢谢 急
- 视图创建索引
- 这两个查询语句,那个性能会高一些?
- ~~~~~~~~~~~~~~~~~~~~请帮我看看如何形成如下数据集~~~~~~~~~~~~~
- 存储过程的错误问题提示很急的呀
- 为什么总是要注册一个特定的“WEBFWQ07\ASPNET”用户才能成功连接数据库?
- 可用分的50%用于寻求问题的解法!将SQL语句转换成ASP的语句!操作对象是ACCESS数据库。高手帮忙!
- 关于sql server 多表大数据实时计算的问题
- 新建数据库结构
- 拆分字符串
A J|K|M|Y
B N|P|Y|Z
C G
D Q|Y如何得到如下内容:title content
A J
A K
A M
A Y
B N
B P
B Y
B Z
C G
D Q
D Y即,遇到|则截取,然后形成一个新行。多谢!!
GO
CREATE TABLE TB
(
TITLE VARCHAR(5),
CONTENT VARCHAR(50)
)
INSERT INTO TB
SELECT 'A','J|K|M|Y' UNION ALL
SELECT 'B','N|P|Y|Z' UNION ALL
SELECT 'C','G' UNION ALL
SELECT 'D','Q|Y'SELECT a.TITLE ,CONTENT = substring(a.CONTENT , b.number , charindex('|' , a.CONTENT + '|' , b.number) - b.number)
FROM TB a JOIN MASTER..spt_values b on b.type='p' and b.number between 1 and len(CONTENT)
where substring('|'+CONTENT,b.number,1)='|';with tt as
(select TITLE,CONTENT=cast(left(CONTENT,charindex('|',CONTENT+'|')-1) as nvarchar(100)),Split=cast(stuff(CONTENT+'|',1,charindex('|',CONTENT+'|'),'') as nvarchar(100)) from tb
union all
select TITLE,CONTENT=cast(left(Split,charindex('|',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex('|',Split),'') as nvarchar(100)) from tt where split>''
)
select TITLE,CONTENT from tt order by TITLE option (MAXRECURSION 0)
在关键字 'with' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 21
第 21 行: 'MAXRECURSION' 附近有语法错误。
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (title varchar(11),content varchar(111) )
insert into #tb
select 'A','J|K|M|Y' union all
select 'B','N|P|Y|Z' union all
select 'C','G' union all
select 'D','Q|Y' Select
a.title,content=substring(a.content,b.number,charindex('|',a.content+'|',b.number)-b.number)
from
#tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.content)
where
substring('|'+a.content,b.number,1)='|'
title content
----------- ---------------------------------------------------------------------------------------------------------------
A J
A K
A M
A Y
B N
B P
B Y
B Z
C G
D Q
D Y(11 行受影响)
drop table tb
Go
Create table tb([title] nvarchar(1),[content] nvarchar(7))
Insert tb
select N'A',N'J|K|M|Y' union all
select N'B',N'N|P|Y|Z' union all
select N'C',N'G' union all
select N'D',N'Q|Y'
Go
SELECT
a.[title],
[content]=SUBSTRING(a.[content],number,CHARINDEX('|',a.[content]+'|',number)-b.number)
FROM tb a
JOIN master..spt_values b
ON b.type='p'
AND CHARINDEX('|','|'+a.[content],number)=number
/*
title content
----- -------
A J
A K
A M
A Y
B N
B P
B Y
B Z
C G
D Q
D Y(11 row(s) affected)
*/