Users表中有Slaves列。值是用竖线分隔的,如:AAA|BBB|CCC|DDD。我现在想把它转化成'AAA','BBB','CCC','DDD'。以便于使用 Where in 语句得到SlaverPrice(价格)的总和,问题是查出来的值都是NULL,代码如下所示。请朋友们帮帮忙,问题究竟出在哪里?
SELECT UserNo, Icon, Gold, Slaves, SlaverPrice, SaleExp,
(SELECT SUM(U.SlaverPrice)
FROM users U
WHERE U.UserNo IN ('''' + REPLACE(Slaves, '|', ''',''') + '''')) AS Expr1
FROM Users
SELECT UserNo, Icon, Gold, Slaves, SlaverPrice, SaleExp,
(SELECT SUM(U.SlaverPrice)
FROM users U
WHERE U.UserNo IN ('''' + REPLACE(Slaves, '|', ''',''') + '''')) AS Expr1
FROM Users
/*
标题:分拆列值
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-20
地点:广东深圳
描述有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/--1. 旧的解决方法(sql server 2000)
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. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/
(SELECT SUM(U.SlaverPrice)
FROM users U
WHERE charindex('|'+U.UserNo + '|' , '|'+Slaves + '|') > 0
FROM Users
select '''' + REPLACE('AAA|BBB|CCC|DDD', '|', ''',''') + ''''
--------------------------
'AAA','BBB','CCC','DDD'(1 行受影响)
这样是没问题
可能要放到动态语句里
(SELECT SUM(U.SlaverPrice)
FROM users U
WHERE charindex('|'+U.UserNo + '|' , '|'+Slaves + '|') > 0
FROM Users 是正解!
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-17 16:50:22
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(15))
insert [tb]
select 'AAA|BBB|CCC|DDD'
--------------开始查询--------------------------
SELECT COL,
COL1 = PARSENAME(REPLACE(COL,'|','.'),4),
COL2 = PARSENAME(REPLACE(COL,'|','.'),3), ----替换一下 '.' 因为 parsename 只认 '.'
COL3 = PARSENAME(REPLACE(COL,'|','.'),2),
COL4 = PARSENAME(REPLACE(COL,'|','.'),1)
FROM TB
----------------结果----------------------------
/*COL COL1 COL2 COL3 COL4
--------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AAA|BBB|CCC|DDD AAA BBB CCC DDD(1 行受影响)
*/
where in 里面可以用字符串啊
红色部分是用 Replace() 计算出来的。