sql - SQL 中的替换和分组依据

Question

嗨，我想按已替换字符但同时保留原始值的字段对我的结果进行分组。

这就是我所拥有的，但我需要在不更改结果的情况下返回原始 URL 值。

select Count(ID) as TotalClicks, Replace(URL, '/', '') as DistinctURL
  from email_click_throughs
 where emailid = @ID
group by  Replace(URL, '/', '')
order by TotalClicks desc

Answer 1

这将为您提供您所描述的内容，但它使“替换(URL ...”上的分组变得多余，所以我可能会误解:

select Count(ID) as TotalClicks, URL as DistinctURL
from email_click_throughs
where emailid = @ID
group by Replace(URL, '/', ''), URL
order by TotalClicks desc

如果您只想取回一个 URL，即使在删除 '/' 后两个 URL 相同，您可以执行类似的操作

select Count(ID) as TotalClicks, Max(URL) as DistinctURL 
from email_click_throughs 
where emailid = @ID group by Replace(URL, '/', '') 
order by TotalClicks desc

但是您无法控制哪些“匹配”被丢弃。