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
提示函数不能包含函数
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
提示函数不能包含函数
解决方案 »
- 共享最新收集的一套数据库图标
- DataGrid控件数据源问题
- 可是windows/fonts下有字体呀(字体通用对话框的使用,如何安装字体)
- 緊急求救﹗﹗﹗﹕我找了N天N夜都找不到 VN6.0 英文版(或繁體版)安裝盤﹐難道網上的東西都是不可信的嗎???
- 请高手指教;把一个足够大的二进制数据数组存到数据库里去!!!
- 要买vb.net的盗版,是几张盘?名字是VB.NET还是microsoft studio .net?
- 如何讓控件在不同的分辨率下都是居中From的
- VB如下方案,我该选用哪个控件来显示数据库表信息?这个题目有点难,详情请点击链接。
- 如何制作HTML帮助文件。请指教
- 如何使webbrowser控件不显示图片(解决了不止20分)
- Winsock连接的问题
- ████████一个难解决的问题████████
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
我获取最大值是这么做的
不知道对楼主是否有帮助
TestAddress, TestCondition, Parameter_Value,
西沙 户外 ss
西沙 户外 ss
西沙 户外 ss
西沙 户外 nn
西沙 户外 nn
西沙 户外 ee
西沙 室内 ff
西沙 室内 ss
西沙 室内 ff
..........
我想查询的结果是:
TestAddress, TestCondition, Parameter_Value, RSNum
西沙 户外 ss 3
西沙 室内 ff 2
...
就是显示相同的TestAddress, TestCondition里Parameter_Value记录最多那条!
(SELECT TestAddress, TestCondition RSNum
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)
(SELECT T1.TestAddress, T1.TestCondition T1.RSNum
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_Valu) T1
GROUP BY TestAddress, TestCondition) T2
from
(select T1.TestAddress,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) 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)
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)
FROM EnvironmentValue T1 INNER JOIN
EnvironmentValue T2 ON T1.BeginTime = T2.BeginTime AND
T1.TestAddress = T2.TestAddress AND T1.TestCondition = T2.TestCondition
WHERE (T1.Patameter = '温度') AND (T2.Patameter = '相对湿度') AND
(T1.Parameter_Value > 10) AND (T1.Parameter_Value < 30) AND
(T2.Parameter_Value > 70) AND (T1.Parameter_Value < 80)
GROUP BY T1.TestAddress, T1.TestCondition
UNION
SELECT T1.TestAddress, T1.TestCondition, COUNT(T1.Parameter_Value) AS Expr2
FROM EnvironmentValue T1 INNER JOIN
EnvironmentValue T2 ON T1.BeginTime = T2.BeginTime AND
T1.TestAddress = T2.TestAddress AND T1.TestCondition = T2.TestCondition
WHERE (T1.Patameter = '温度') AND (T2.Patameter = '相对湿度') AND
(T1.Parameter_Value > 10) AND (T1.Parameter_Value < 30) AND
(T2.Parameter_Value >= 80)
GROUP BY T1.TestAddress, T1.TestCondition