Question

SQL分组查询最大值最小值

如下图：表结构和数据

期望展示效果：Bshi 中‘00’表示成功的记录，其它表示失败的记录，testMax 列展示该ID和ZID下成功的记录中最大值，testMin 列展示该ID和ZID下成功的记录中最小值，success表示该ID和ZID下成功的记录总数，fail表示该ID和ZID下失败的记录总数





Answer 1

查询语句参考如下：
select
UserName,--
Convert(char(8),dealTm,112) as date,--日期
min(dealTm) as earliestTime ,--最大
max(dealTm) as latestTime--最小
from t2
group by Convert(char(8),dealTm,112) ,UserName

Answer 2

--语句如下：
select ID,ZID,count(CASE Bshi when '00' then 1 else 0 end) as success,count(CASE Bshi when '00' then 0 else 1 end) as fail,max(Qzhi) as testMax,min(Qzhi)  as testMin from 表名 GROUP BY ID,ZID

追问

分组是对的，成功总数，失败总数，最大值和最小值都不对

Answer 3

在没有好的办法前，我用了临时表
select ID, ZID, bshi, count(Bshi) success, max(cast(Qzhi as int)) testMax, min(cast(Qzhi as int)) testMin
into #tempsuccess
from zhidao
where Bshi = '00'
group by ID, ZID, Bshi

select ID, ZID, count(Bshi) fail
into #tempfail
from zhidao
where Bshi <> '00'
group by ID, ZID

select distinct ID, ZID
into #tempid
from zhidao

select A.ID,A.ZID, isnull(B.success,0), isnull(C.fail,0), B.testmax, B.testmin from #tempid A
full outer join #tempsuccess B on A.ID = B.ID and A.ZID = B.ZID
full outer join #tempfail C on A.ID = C.ID and A.ZID = C.ZID

Answer 4

select ID,ZID,count(CASE Bshi when '00' then 1 else 0 end) as success,count(CASE Bshi when '00' then 0 else 1 end) as fail,max(CASE Bshi when '00' then CaQzhi else 0 end) as testMax,min(CASE Bshi when '00' then CaQzhi else CaQzhi end) as testMin from 表名 GROUP BY ID,ZID

试试