我用这条语句时
SELECT 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)
返回这个结果

但如果我想得到更精确一点，得到每个model前10片的 avg(SpanTime) ??

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)

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 后不能运行呢？

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啦。。。。。。高

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