下面的T-SQL显示了它下面的图表中显示的结果。我想以一种排除UserID为1、95和161的行的方式编写T-SQL。这些行只出现一次,我不想将它们包含在结果集中。我想要的是显示UserID多次出现的行。任何帮助都将不胜感激。非常感谢!
select top(100) UserID, PhotoLabel
from dbo.Photos
where left(PhotoLabel, 8) in (
select distinct top(10) left(PhotoLabel, 8) as date
from dbo.Photos
order by left(PhotoLabel,8) desc
)
order by UserID asc, left(PhotoLabel,8);
UserID PhotoLabel
====== ==============
1 20160702064633
2 20150915101504
2 20150915101307
2 20150915101152
95 20150726135443
159 20160330234026
159 20160330234018
161 20160223112742发布于 2016-09-19 04:44:54
只需将条件添加到where子句:
select top(100) UserID, PhotoLabel
from dbo.Photos
where left(PhotoLabel, 8) in (select distinct top(10) left(PhotoLabel, 8) as date
from dbo.Photos
order by left(PhotoLabel, 8) desc
) and
UserId not in (1, 95, 161)
order by UserID asc, left(PhotoLabel, 8);注意:
如果您想“自动”提取单例(即不列出它们),则使用窗口函数。您还可以使用窗口函数来替换in:
with t as (
select UserID, PhotoLabel
from dbo.Photos
where left(PhotoLabel, 8) in (select distinct top(10) left(PhotoLabel, 8) as date
from dbo.Photos
order by left(PhotoLabel,8) desc
)
)
select t.*
from (select t.*, count(*) over (partition by userid) as cnt
from t
) t
where cnt > 1
order by userid, left(PhotoLabel, 8);发布于 2016-09-19 12:52:34
Select *
From dbo.Photos
Where UserID in
(
Select UserID
From dbo.Photos
Group by UserID
Having count(UserID)>1
)https://stackoverflow.com/questions/39562480
复制相似问题