用户ID生成 [英] User Id Generation
问题描述
大家好，
我正面临一个问题，我非常感谢你的一些建议。
这就是事情：
我有一张名为Companies的表格，如下所示：
Id Name
55 Foo Corp
56 Ku Corp
我还有一张引用用户的桌子：
Id CompanyId Name
1 55 John
2 56 Marc
3 55 Michael
4 55 Terry
5 56 Dorcel
我需要的是能够根据公司ID计算用户的ID
例如我希望：
< br $>
CustomId
C55U1（公司55用户1）John
C56U1 Marc
C55U2 Michael
C55U3特里
C56U2 Dorcel
（我只需要相对于自己公司的用户索引，因为我可以得到他休息使用计算列规范）
我想这样做，因为用户ID暴露给用户并使用Native incremental（+1）id会向某人公开这个号码自上次创建用户以来其他公司创建的用户。
我想避免在同一时间创建多个用户的情况下进行COUNT查询不是很有效。
我能想到的唯一可靠的解决方案是为每家公司创建一个用户表，但我认为它们可能是一个更智能的解决方案，但我对SQL的了解有限。这就是我寻求帮助的原因:)
非常感谢。
Hi all,
I am facing an issue on which I really appreciate some advices.
Here is the thing:
I have a table called Companies which is like this:
Id Name
55 Foo Corp
56 Ku Corp
I have an other table referencing the Users:
Id CompanyId Name
1 55 John
2 56 Marc
3 55 Michael
4 55 Terry
5 56 Dorcel
What I need is to be able to compute the Id of the Users based on the Company Id
For Example I would like to have:
CustomId
C55U1 (Company 55 User 1) John
C56U1 Marc
C55U2 Michael
C55U3 Terry
C56U2 Dorcel
(I just need the User index relative to its own company as I can get the rest using a computed column specification)
I want to do that because the users id are exposed to the users and using the Native incremental (+1) id will expose to someone the number of created user by the other companies since he last created an user.
I would like to avoid having to do a COUNT query in case several users are created in the same time and this might not be very efficient.
The only reliable solution I can think of is to create a user table for each company but I think they might be a smarter solution but my knowledge of SQL is limited. This is why I ask for your help :)
Thanks a lot.
推荐答案
你可以通过利用ROW_NUMBER（）的PARTITION BY子句。但是，结果可能会随着新用户添加到您的表中而变化，当您对其进行排序时，它会影响返回的值。
Happy Coding !!!
~~ CK
You can do this by utilizing the PARTITION BY clause of the ROW_NUMBER(). However, there''s a possibility that the result could vary as new users can be added to your table and when you sort it, it''ll affect the returned value.
Happy Coding!!!
~~ CK
我可以通过两种方式立即想到这样做：创建计算列或添加新列领域。
这里是计算列解决方案：
There are two ways I can immediately think of doing this one: Create a computed column or add a new field.
Here''s the computed column solution:
ck9663
感谢您的回答，这可能是一个良好的开端，可以为您找到合适的解决方案。
b0010100也感谢您的输入，但问题是比仅仅格式化Id更复杂一些。它更多的是维护一个独立于每个公司用户的识别序列。格式化本身不是问题。
以下是我面临的问题的更多细节：
我不希望计算id每次我请求它，因为它应该保持不变，因此如果我们保持逻辑，则不需要多次计算静态值。 （因此计算不能持久的列不是一个选项）
用户可以删除=>因此，带有分区的RowNumber（如你所提到的）会在你删除用户的情况下返回不同的结果。
最后我需要的是（理想情况下）内置的，可靠的（保证在每种情况下的唯一性），这将在创建新用户时为我提供给定公司以前创建的用户数。请注意，因为可以删除用户，所以不能使用Count查询，因为结果可能会从一次变为另一次。
这就是为什么最后唯一的解决方案（基于满足所有这些要求的基本sql知识是将用户存储到公司专用表中并使用简单的自动计算标识列（羞耻创建几个（数百到数千......）表只是为了存储一些用户每一个...）
如果有人告诉我表的数量实际上并不重要，并且我的数据库中可以有大量的表而不会影响性能，可靠性然后我可能不情愿地考虑上面的解决方案。
此外，每个包含触发器的解决方案都不好，因为在查询后调用触发器并且没有人能确保触发器将被运行（停电......）我希望100％确定我可以依赖我想要的自定义ID。
非常感谢。
Rgds
ck9663
Thanks for you answer which may me a good start leading to the right solution.
b0010100 thanks also for your input however the problem is a little bit more complex than just formatting the Id. It is more about maintaining an identification sequence that are independent for each company users. The formating itself is not a problem.
Here are more details on the issue I am facing:
I don''t want the id to be computed each time I request it as it supposed to stay always the same and therefore if we stay logical there is no need to compute a static value several times. (therefore computed column that cannot be made persistent is not a option)
Users can be deleted => therefore the RowNumber with partition will (as you mentioned) return different results in case you delete a user.
At the end all I need is something (ideally) built in, reliable (that insure uniqueness in every situation), that will give me the number of previously created users for a given company when creating a new user. Please note that because the users can be deleted a Count query cannot be used as the result may change from one time to an other.
This is why at the end the only solution (based on my basic sql knowledge) that fulfill all these requirements is to store the users into a company dedicated table and use a simple auto compute identity column (shame to create several (hundreds to thousands...) tables just to store a few users in each one...)
If someone tells me that the number of table does not actually matter and that I can have a massive number of tables in my database without impacting the performance, reliability then I might unwillingly consider the above solution.
Also every solution that include triggers is also not good as triggers are called after the query and no one can ensure that the trigger will be run (power outage ...) and I want to be 100% sure that I can rely on the Custom Id I am trying to put in place.
Thanks a lot.
Rgds
这篇关于用户ID生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
