我的任务是清理 SQL Server 2008 中 CS2009 的一些数据。在我们的配置文件数据库中，有两个表：
- 地址
UserObject
地址：
地址 ID、线路 1、线路 2
guid、varchar、varchar
用户对象：
userid_id、addressguid、aactivtydate
guid、varchar、日期时间
我需要删除所有早于 X 的 UserObject（即没有活动）。这显然不是问题。当我想删除属于该人的地址时，我的问题就出现了。它们以连续的方式存储在一个字段中。例如，如果用户有 2 个地址，则可能类似于：
2;{guid1};{guid2}
第一个数字是 guid 的数量，全部用分号分隔。地址表没有 user_id 字段可以将其绑定起来（非常烦人）。地址表中唯一的 ID 是地址 GUID。
我的问题是如何用 SQL 解决这个问题（我的 sql 有点差）。我想它会是这样的：
- 选择所有用户其中activitydate > > X
- 将地址 guid 字符串分开并获取每个 guid
- 删除地址表中的每个 guid
- 删除用户
我想删除地址可以使用子字符串或其他内容来删除初始数字和分号，然后用逗号替换其余的分号并将其放入 when IN 子句。
我不知道循环、最好的方法等等。
谢谢
I have the task of cleaning up some of our data in SQL Server 2008 for CS2009. In our profiles database there are two tables:
- Addresses
-
UserObject
Address:
address_id, line1, line2
guid, varchar, varchar
UserObject:
userid_id, addressguids, acitivtydate
guid, varchar, datetime
I need to remove all UserObjects that are older than X (i.e. have had not activity). That's obviously not a problem. My issue comes when I want to remove the addresses that belong to that person. They are stored in one field in a concat fashion. For example if the user had 2 addresses it could look something like:
2;{guid1};{guid2}
The first number being the number of guids, all seperated by semi colons. The Address table doesn't have a field for the user_id to be able to tie it back (very annoying). The only ID in the Address table is the address guid.
My question is how I could sort this out in SQL, (my sql is a bit poor). I would imagine it would be something like:
- select all users where activitydate > X
- pull address guid string apart and get each guid
- delete each guid in address table
- delete user
I guess to delete the addresses could substring or something to remove the inital number and semicolon then replace rest of the semi colons with commas and put that into a when IN clause.
I have no clue about the looping, best ways of doing it and the like.
Thanks
发布评论
评论（2）
我认为分阶段进行：
首先为 user_address 建立一个新表。这应该有 user_id 和 address_guid
然后编写一个过程或循环将地址引导分解到这个新结构中。
查询并检查您是否正确。擦拭它，如果没有的话重新开始。
完成后，使用它来进行删除。应该简单得多。
i think do it in stages:
first build yourself a new table for user_address. this should have user_id, and address_guid
then write a proc or loop to break out the address guids into this new structure.
query it and check that you got it right. wipe it and start over if you didn't.
when done, use this to do the deletes. should be much simpler.
如果您不想使用新表来执行此操作，如前面所回答的那样（顺便说一句，该答案没有任何问题），那么您可以编写一个表值函数来执行一些字符串操作并返回您的中的所有 guid 'addressguiids' 字段位于新行中，包含用户 ID。
完成此操作后，您可以使用 CROSS APPLY 或 OUTER APPLY 来加入该函数，就好像它是一个表 如这篇 technet 文章中所述。
If you don't want to do it using a new table, as previously answered (nothing wrong with that answer, by the way), then you could write a table-valued function that does some string manipulation and returns all the guids in your 'addressguids' field on a new row, with the userid.
Once you've done that, you could then use CROSS APPLY or OUTER APPLY to join the function as if it were a table as described in this technet article.