＋设为首页 ＋添加到收藏夹
SELECT TestAddress, TestCondition, Parameter_Value, COUNT(Parameter_Value)
AS RSNum
FROM EnvWindValue
WHERE (Patameter = 风向) AND (BeginTime BETWEEN 1999/02/19 AND 2003/09/19)
GROUP BY TestAddress, TestCondition, Parameter_Value
ORDER BY RSnum DESC
查询结果为
TestAddress, TestCondition, Parameter_Value, RSNum
西沙 户外 ss 3
西沙 户外 nn 2
西沙 户外 ee 1
西沙 室内 ff 3
西沙 室内 ss 2
.........................
我想再排序 以TestAddress, TestCondition分组排 取RSNum最大的值
SELECT TestAddress, TestCondition MAX(RSNum) AS MaxValue
FROM (SELECT TestAddress, TestCondition, Parameter_Value, COUNT(Parameter_Value)
AS RSNum
FROM EnvWindValue
WHERE (Patameter = 风向) AND (BeginTime BETWEEN 1999/02/19 AND
2003/09/19)
GROUP BY TestAddress, TestCondition, Parameter_Value
ORDER BY RSnum DESC)
GROUP BY TestAddress, TestCondition
运行,提示RSNum字段语法错误
我要结果显示为:
TestAddress, TestCondition, Parameter_Value, RSNum
西沙 户外 ss 3
西沙 室内 ff 3
.........................
我用SELECT TestAddress, TestCondition, Parameter_Value, MAX(COUNT(Parameter_Value))
AS RSNum
FROM EnvWindValue
WHERE (Patameter = 风向) AND (BeginTime BETWEEN 1999/02/19 AND 2003/09/19)
GROUP BY TestAddress, TestCondition, Parameter_Value
ORDER BY RSnum DESC
提示函数不能包含函数
*******显示公司编号 Convert 函数强制转换数据类型
sql = "Select Max(Convert(int,Y_YSDWBH_BH)) From Y_YSDWBH Where Y_YSDWBH_BH like __" Left(Y_YSDWBH_BH,2)=" & str & "" like " & str & "__"
Set rs = ExecuteSQL(sql)
str1 = rs.Fields(0).Value
我获取最大值是这么做的
不知道对楼主是否有帮助
select T2.TestAddress as TA,T2.TestCondition AS TC,T3.Parameter_Value as PV,T2.RsNum1 as RN2
from
(select T1.TestAddress,TestCondition,max(T1.RSNum) as RsNum1
from
(SELECT TestAddress, TestCondition, Parameter_Value, COUNT(Parameter_Value) AS RSNum
FROM EnvWindValue
WHERE (Patameter = 风向) AND (BeginTime BETWEEN 1999/02/19 AND 2003/09/19)
GROUP BY TestAddress, TestCondition, Parameter_Value) T1
group by TestAddress,TestCondition) T2,
(SELECT TestAddress, TestCondition, Parameter_Value, COUNT(Parameter_Value) AS RSNum
FROM EnvWindValue
WHERE (Patameter = 风向) AND (BeginTime BETWEEN 1999/02/19 AND 2003/09/19)
GROUP BY TestAddress, TestCondition, Parameter_Value) T3
where (T2.TestAddress=T3.TestAddress) and (T2.RsNum1=T3.RsNum) and (T2.TestCondition=T3.TestCondition)
