SQL SELECT加入字符串集ID
我不得不面对一个MSSQL数据库,并在表中给出这样的信息:SQL SELECT加入字符串集ID
Users:
ID Name Countries
--------------------
1 User1 1,2,3
2 User2 2,5
Countries:
ID Country
----------
1 Australia
2 Germany
3 USA
4 Norway
5 Canada
现在,我所寻找的是一个select语句,这将使我喜欢这样的结果:
Result:
ID User CountriesByName
-----------------------------
1 User1 Australia, Germany, USA
2 User2 Germany, Canada
我宁愿不会依赖于特殊的MSSQL语法在一些特殊的解决方案,但没有办法,我用一些LINQ魔法:(
首先,您需要将该字符串分开。这里是一个可行的分裂功能:
Create Function [dbo].[split]
(@input varChar(8000) -- List of delimited items
,@delimit varChar(8000) = ',') -- delimiter that separates items
Returns @List Table ([item] varChar(8000)) As
Begin
Declare @item VarChar(8000);
while charIndex(@delimit, @input, 0) <> 0 Begin
Select
@item = rTrim(lTrim(subString(@input, 1, charIndex(@delimit, @input, 0) - 1))),
@input = rTrim(lTrim(subString(@input, charIndex(@delimit, @input, 0) + Len(@delimit), Len(@input))));
If Len(@item) > 0 Insert Into @List Select @item
End
If Len(@input) > 0 Insert Into @List Select @input
Return;
End
然后,你需要加入值回你的国家表,并重新加入他们的行列。这会让你大部分的方式:
Select ID
,[Name] as [User]
,(
Select [country] + ', '
From [Countries]
Where [ID] In (
Select Cast([item] As Integer)
From dbo.split(U.Countries, ',')
Where IsNumeric(item) = 1)
Order By [country]
For XML Path('')) As [CountriesByName]
From [Users] As U
但是,这留下了一个尾随逗号。您可能需要删除一些其他层,但以防万一,你必须这样做在SQL,这应该工作:
Select ID
,[User]
,Left([CountriesByName], Len([CountriesByName]) - 1) As [CountriesByName]
From (
Select ID
,[Name] as [User]
,(
Select [country] + ', '
From [Countries]
Where [ID] In (
Select Cast([item] As Integer)
From dbo.split(U.Countries, ',')
Where IsNumeric(item) = 1)
Order By [country]
For XML Path('')) As [CountriesByName]
From [Users] As U) As [Results]
正确的解决方案是规范化数据,因为如果你不需要编写这样的混乱代码(并且我们还没有写过这个函数至少一次吗? )但是,假设修改表格不是一种选择 - 它从来就不是,是吗? - 所以这可能是这个问题的正确答案。 – 2009-07-15 14:47:34
如果您标准化您的数据分成三个数据表,然后下面的作品,你要什么做。这是使用我自己的模式,因为我必须敲一些表来测试它)。
Select
UserId, UserName,
(
Select
CountryName + ','
From
Country As C
Inner Join
UserCountry As UC
On C.CountryId = UC.CountryId
Where
UC.UserId = [User].UserId
ORDER BY
CountryName
FOR XML PATH('')
) As Countries
从 [User];
首先,我将创建一个存储过程,该过程需要一串国家ID并使用动态SQL返回国家/地区名称列表。
create proc dbo.getCoutries(@ids nvarchar(200))
as
begin
declare @sql nvarchar(200)
set @sql = 'select @countries = @countries + Country+
'', '' from Countries where ID in ('[email protected]+')'
declare @countries nvarchar(200)
set @countries = ''
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@countries nvarchar(200) OUTPUT';
EXECUTE sp_executesql @sql,
@ParmDefinition,
@[email protected] OUTPUT;
select substring(@countries,1,len(@countries)-1) Countries
end
go
所以现在exec execCoutries'2,5'返回“德国,加拿大”。 一旦做到这一点,我们现在需要下面的代码运行 回报看起来像表:
Name Countries
User1 Australia, Germany, USA
User2 Germany, Canada
--The variable we will use to loop thru the user ids
declare @userId int
set @userId = 0
-- hold the string of country ids
declare @countryIds varchar(30)
-- a temp table that we will populate
-- it will have the users ID and then the
-- string of countries (i.e "Germany, Canada")
declare @results as
table
(userId int,
countries varchar(200))
--Loop thru each row in the Users table.
while 1=1
begin
select @userId=min(ID)
from Users
where ID > @userId
if @userId is null
break
--get the string of country ids for this user
select @countryIds=Countries
from Users
where ID = @userId
--use our stored proc to bring back the string of names
insert into @results
(countries)
exec getCoutries @countryIds
--update the userId for our new row
update @results
set UserId = @UserId
where UserId is null
end
-- return the user and his list of countries
select u.Name, r.Countries
from Users u
inner join @results r
on u.ID = r.UserId
GO
我认为你需要的国家进行实地正常化首先进入连接表“UserCountry”。 – 2009-07-15 14:22:36
不幸的是,我没有办法改变数据库方案 – BigBlackDog 2009-07-15 14:47:52