分没了不好意思哈~~select count(*) from (select distinct License from Result where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License) temp我想对此句添加排序 所以写下如下语句
select count(*) from (select distinct License from Result where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License order by TestTime) temp结果不正确,实在不知道该怎么联合起来使用SQL的分组并且排序了,遂来问问
分组的同时排序该如何写?
group by License) temp我想对此句添加排序 所以写下如下语句
select count(*) from (select distinct License from Result where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License order by TestTime) temp结果不正确,实在不知道该怎么联合起来使用SQL的分组并且排序了,遂来问问
分组的同时排序该如何写?
我用的SQLSERVER2000
在你说的情况下好象不用排序
group by License) temp
哎,下午
有点晕
我试试 min,我的意思就是为了排完序取得第一条记录,,用min吧,能分组排最小时间值的那个
select count(*) from (
select distinct License , min(TestTime) as TestTime from Result
where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License
order by TestTime
) temp
order by 作为子句的时候,必须有top 关键字所以可以这样
select count(*) from (select distinct top 100 percent License from Result where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License order by TestTime) temp
用top 100 percent 就可以百分百选出来了
如果order by TestTime和Group By License的字段名称会有冲突,(Oracle中有着要求)
就可以这样了
select count(*) from (select distinct License from (Select top 100 percent * from Result order by TestTime) Result2 where DeviceID = 1 AND Result = '1' AND TestTime between '1966-12-11 12:11:22' AND '2006-12-11 12:11:22'
group by License) temp