sql - SQL Server 中的日期计数

Question

我有一个表(SQL Server)

      Date     |    Time          |  Id      |  Patient
   -------------------------------------------------------
    2018-06-19 | 18:02:49.0000000 | 1800257  |  Patient1
    2018-06-19 | 18:04:24.0000000 | 1800258  |  Patient2
    2018-06-19 | 18:35:35.0000000 | 1800259  |  Patient3

如何获取同一查询中相同日期的计数

我得试试这个

select COUNT(con.AppDate) as Appointment,con.AppDate as Date 
from Consultation as con 
inner join DoctorMaster as doc on con.DrCode=doc.id 
where con.DrCode='2'      
GROUP BY CAST(AppDate AS DATE),con.AppDate

使用这个我只得到日期计数，但我想要多一列，即用于安排的时间

预期输出:

 CountOfDate  |     Date     |     Time     |  Patient
----------------------------------------------------------
    3         |   2018-06-19 |   18:02:49   |  Patient1
    3         |   2018-06-19 |   19:10:10   |  Patient1
    3         |   2018-06-19 |   19:02:50   |  Patient1

如何显示每行的日期计数？

Answer 1

根据您似乎想要的示例数据:

select *, count(*) over (partition by date) as CountOfDate, 
          min(Patient) over (partition by date) as Patient
from table t;