22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[结束日期] Date)
Insert #T
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'李四','2017-10-05' union all
select N'李四','2017-11-06' union all
select N'王五','2018-1-5' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-20'
Go
--测试数据结束
SELECT 姓名 ,
COUNT(DISTINCT 结束日期) AS 天数
FROM #T
GROUP BY 姓名
HAVING COUNT(DISTINCT 结束日期) > 1
--借#1数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[结束日期] Date)
Insert #T
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'李四','2017-10-05' union all
select N'李四','2017-11-06' union all
select N'王五','2018-1-5' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-20'
GO
SELECT DISTINCT 姓名
FROM #t a
WHERE EXISTS(
SELECT 1 FROM #t b WHERE a.姓名=b.姓名
AND a.结束日期!=b.结束日期
)
/*
姓名
李四
马六
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[结束日期] Date)
Insert #T
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'张三','2017-10-02' union all
select N'李四','2017-10-05' union all
select N'李四','2017-11-06' union all
select N'王五','2018-1-5' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-10' union all
select N'马六','2017-1-20'
Go
--测试数据结束
Select 姓名 from #T GROUP BY 姓名 HAVING COUNT(DISTINCT 结束日期)>1