这个top查询该怎么写 - 编程论坛

#2

MIQIKing2010-03-28 07:40

SELECT 10 top LEFT(SN,6) as Model,
count(*) as TestQty,
avg(SpanTime) as TestingTime
FROM FTNTRACK
Where STATION='FT01'
and result = 'true'
group by LEFT([SN],6)

#3

jack_xu20462010-03-28 12:25

SELECT TOP 5 * from FTNTRACK where STATION = 'FT01' AND LEFT(SN,6) = 'FWF50B' and result = 'true' ORDER BY ENDTIME DESC
SELECT TOP 5 * from FTNTRACK where STATION = 'FT01' AND LEFT(SN,6) = 'FG100C' and result = 'true' ORDER BY ENDTIME DESC
这两句分别运行都不出错，为什么之间添加union 后不能运行呢？

#4

jack_xu20462010-03-28 14:44

SELECT LEFT(SN,6) as Model,count(*) as TestQty,avg(SpanTime) as TestingTime FROM (SELECT * FROM (SELECT TOP 10 SN,STATION,STARTTIME,ENDTIME,RESULT,SpanTime from FTNTRACK where STATION = 'FT01' AND LEFT(SN,6) = 'FWF50B' and result = 'true' ORDER BY ENDTIME desc) A union all SELECT * FROM (SELECT TOP 10 SN,STATION,STARTTIME,ENDTIME,RESULT,SpanTime from FTNTRACK where STATION = 'FT01' AND LEFT(SN,6) = 'FG100C' and result = 'true' ORDER BY ENDTIME DESC) B
) C Where STATION='FT01'and result = 'true'group by LEFT([SN],6)

只有本站会员才能查看附件，请登录

OK啦。。。。。。高

#5

lshdn2010-04-06 13:59

如果用union all 的话，order by 只能在最后一个句子中用。