20130118SQL记录

update l
set
l.state='X'
from
links l
inner join
(
select max(ID) as ID from links where [time]>'2013/1/1' and state<>'X' group by title having count(1)>5
) b
on l.id=b.id and 1=2

--具体重复的记录 l.id>=a.minID and l.id<=a.maxID 采用主键筛选，加快筛选效率
select l.ID,l.title,l.url,l.author,l.time,l.hospital,l.state,
a.maxId,a.MinID,a.repeat
from links l
,
(select title,max(ID) maxID,min(ID) minID,count(1) repeat
from links
where [time]>'2013/1/1'
group by title
having count(1) >3
)a
where l.id>=a.minID and l.id<=a.maxID and l.title=a.title
order by a.minID desc

 

 

 

 

 

select c.employee,
count(CASE WHEN c.hospital = '********' then 1 else null end) as 定州,
count(CASE WHEN c.hospital = '********' then 1 else null end) as 辛集,
count(CASE WHEN c.hospital = '********' then 1 else null end) as 任丘,
count(CASE WHEN c.hospital = '********' then 1 else null end) as 藁城,
count(CASE WHEN c.hospital = '********' then 1 else null end) as 安国
from customers c
where state='就诊'
and medicaled>='2012/12/1'
and medicaled<'2013/1/1'
group by employee
order by employee