SQL语句

Question

本帖最后由 hegongda 于 2015-01-30 15:21:30 编辑

Answer 1

declare @d1 date ='2015-1-1' --范围左边界
declare @d2 date ='2015-1-21'--范围右边界
;
with d as (
select DATEADD(dd,v.number,@d1) d
from master..spt_values v 
where v.type='p' and DATEADD(dd,v.number,@d1)<=@d2
)
select d.d 日期,COUNT(tb.id) 人数
from d left join tb on d.d between tb.start and tb.[end]
group by d.d
order by d.d

Answer 2

按天统计
DECLARE @StartDate DATETIME='2015-01-01',
@EndDate DATETIME = '2015-02-01'

SELECT CONVERT(VARCHAR(10),START,120),COUNT(1) AS 病人数
FROM TB
WHERE START >= @StartDate
  AND START < @EndDate
  GROUP BY CONVERT(VARCHAR(10),START,120)

Answer 3

--当天住院病人=住院病人+新入院病人-出院病人select zy.nowdate,zy.pcount+ry.pcount-cy.pcount pcount
from (select convert(varchar(10),start,120) nowdate,count(1) pcount
from tb
where convert(varchar(6),start,112)<=201501
group by convert(varchar(10),start,120)) zy
,(select convert(varchar(10),start,120) nowdate,count(1) pcount
from tb
where convert(varchar(6),start,112)=201501
group by convert(varchar(10),start,120) ry
,(select convert(varchar(10),end,120) nowdate,count(1) pcount
from tb
where convert(varchar(6),end,112)=201501
group by convert(varchar(10),end,120) cy
where zy.nowdate=ry.nowdate and ry.nowdate=cy.nowdate

Answer 4

好像是这样select COUNT(CONVERT(varchar(10),start_time,112)) as start_time_count,CONVERT(varchar(10),start_time,112)as start_time into #t1
from #info t1 where CONVERT(varchar(6),start_time,112)='201501' 
group by  CONVERT(varchar(10),start_time,112)
select COUNT(CONVERT(varchar(10),end_time,112))   as end_time_count  ,CONVERT(varchar(10),end_time,112)as end_time into #t2
from #info t2 where CONVERT(varchar(6),start_time,112)='201501' 
group by  CONVERT(varchar(10),end_time,112)
select * from #t1 t1 
select * from #t2 t2
select 
case when end_time_count is null then start_time_count else start_time_count-end_time_count end  as endcount,
t1.start_time
from #t1 t1 left join #t2 t2 on t1.start_time=t2.end_time

Answer 5

好像是这样select COUNT(CONVERT(varchar(10),start_time,112)) as start_time_count,CONVERT(varchar(10),start_time,112)as start_time into #t1
from #info t1 where CONVERT(varchar(6),start_time,112)='201501' 
group by  CONVERT(varchar(10),start_time,112)
select COUNT(CONVERT(varchar(10),end_time,112))   as end_time_count  ,CONVERT(varchar(10),end_time,112)as end_time into #t2
from #info t2 where CONVERT(varchar(6),start_time,112)='201501' 
group by  CONVERT(varchar(10),end_time,112)
select * from #t1 t1 
select * from #t2 t2
select 
case when end_time_count is null then start_time_count else start_time_count-end_time_count end  as endcount,
t1.start_time
from #t1 t1 left join #t2 t2 on t1.start_time=t2.end_time

Answer 6

病人名字                      入院时间                                 出院时间
病人A                            2015-1-10                             2015-1-26
病人b                            2015-1-10                             2015-1-26
病人c                           2015-1-10                             2015-1-23
病人d                            2015-1-15                             2015-1-26
病人e                            2015-1-25                             2015-1-28
病人f                            2015-1-19                            2015-1-30
统计    2015-1-10到2015-1-30之间的每天的住院人数，