SQL:没有游标,如何选择记录,为重复项制作唯一的整数id(类似身份)?
如果您有下面的 select 语句,其中 PK 是主键:
select distinct dbo.DateAsInt( dateEntered) * 100 as PK,
recordDescription as Data
from MyTable
并且输出是这样的(为了清晰起见,第一个数字被间隔):
PK Data
2010 01 01 00 New Years Day
2010 01 01 00 Make Resolutions
2010 01 01 00 Return Gifts
2010 02 14 00 Valentines day
2010 02 14 00 Buy flowers
并且您想要输出这样的内容:
PK Data
2010 01 01 01 New Years Day
2010 01 01 02 Make Resolutions
2010 01 01 03 Return Gifts
2010 02 14 01 Valentines day
2010 02 14 02 Buy flowers
是否可以使“00”在PK中单选有“身份”号码效果吗?否则,如何为该日期的每个找到的活动将数字增加 1?
当我输入时,我已经在考虑尝试像 Sum(case when ?? then 1 end) 和 group by 之类的东西。
编辑:(下面由 JohnFX 提供的答案)
这是最终答案:
select PK + row_number() over
(PARTITION BY eventid order by eventname) as PK,
recordDescription
from (select distinct -- Removes row counts of excluded rows)
dbo.DateAsInt( dateEntered) as PK,
recordDescription as Data
from MyTable) A
order by eventid, recordDescription
If you have the select statement below where the PK is the primary key:
select distinct dbo.DateAsInt( dateEntered) * 100 as PK,
recordDescription as Data
from MyTable
and the output is something like this (the first numbers are spaced for clarity):
PK Data
2010 01 01 00 New Years Day
2010 01 01 00 Make Resolutions
2010 01 01 00 Return Gifts
2010 02 14 00 Valentines day
2010 02 14 00 Buy flowers
and you want to output something like this:
PK Data
2010 01 01 01 New Years Day
2010 01 01 02 Make Resolutions
2010 01 01 03 Return Gifts
2010 02 14 01 Valentines day
2010 02 14 02 Buy flowers
Is it possible to make the "00" in the PK have an "identity" number effect within a single select? Otherwise, how could you increment the number by 1 for each found activity for that date?
I am already thinking as I type to try something like Sum(case when ?? then 1 end) with a group by.
Edit: (Answer provided by JohnFX below)
This was the final answer:
select PK + row_number() over
(PARTITION BY eventid order by eventname) as PK,
recordDescription
from (select distinct -- Removes row counts of excluded rows)
dbo.DateAsInt( dateEntered) as PK,
recordDescription as Data
from MyTable) A
order by eventid, recordDescription
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为您正在寻找 ROW_NUMBER 和关联的 PARTITION 子句。
如果 DateEntered 有重复值,您可能还需要查看 DENSE_RANK() 和 RANK() 取决于您执行操作的具体需求在这些情况下使用增量器。
I think you are looking for ROW_NUMBER and the associated PARTITION clause.
If DateEntered has duplicate values you probably also want to check out DENSE_RANK() and RANK() depending on your specific needs for what to do with the incrementor in those cases.
日期时间列永远不应该用作主键。另外,如果您有一个不确定列,则不能:
因为
2010 01 01 01
与2010 02 14 01
具有相同的标识。最好的方法是分开一个标识列,并将您的假期日期放在另一列中,然后将每个字段的转换为
nvarchar
值连接起来。Datetime columns should never ever be used as primary key. Plus, if you had an indetity column, you couldn't have:
As
2010 01 01 01
would have the same identity as2010 02 14 01
.The best approach would be to have an identity column apart, and have your holiday date in another column, and concatenate the converted to
nvarchar
value of each of these fields.