表A(日期,工号,备注)
如何查询出工号为‘123‘,但出现了同一个日期有不同备注的记录?
比方说:
123|2007-7-5|备注1|
123|2007-7-5|备注2|
123|2007-7-5|备注3|
123|2007-7-5|备注4|
如何查询出工号为‘123‘,但出现了同一个日期有不同备注的记录?
比方说:
123|2007-7-5|备注1|
123|2007-7-5|备注2|
123|2007-7-5|备注3|
123|2007-7-5|备注4|
insert into @t
select 123,'2007-7-5','备注1' union all
select 123,'2007-7-5','备注2' union all
select 123,'2007-7-5','备注3' union all
select 123,'2007-7-5','备注4' union all
select 123,'2007-7-6','备注4' union all
select 123,'2007-7-5','备注4' union all
select 12,'2007-7-5','备注4'select id,date,re from @t
group by id,date,re
declare @t table(id int,date datetime,re nvarchar(10))
insert into @t
select 123,'2007-7-5','备注1' union all
select 123,'2007-7-5','备注2' union all
select 123,'2007-7-5','备注3' union all
select 123,'2007-7-5','备注4' union all
select 123,'2007-7-6','备注4' union all
select 123,'2007-7-5','备注4' union all
select 12,'2007-7-5','备注4'
--如果 同一个日期有不同备注的记录数量 可以为1个的话
select distinct a.id,a.date,a.re from @t a
where a.id=123
/*
id date re
----------- ------------------------------------------------------ ----------
123 2007-07-05 00:00:00.000 备注1
123 2007-07-05 00:00:00.000 备注2
123 2007-07-05 00:00:00.000 备注3
123 2007-07-05 00:00:00.000 备注4
123 2007-07-06 00:00:00.000 备注4
*/--如果备注数量大于一个的话
select distinct a.id,a.date,a.re from @t a right join
(
select id,date,re=count(distinct re) from @t
where id=123
group by id,date
having count(*)>1
)b
on a.date=b.date
where a.id=123
order by a.date
/*
id date re
----------- ------------------------------------------------------ ----------
123 2007-07-05 00:00:00.000 备注1
123 2007-07-05 00:00:00.000 备注2
123 2007-07-05 00:00:00.000 备注3
123 2007-07-05 00:00:00.000 备注4
*/