select x.a,x.b,max(right(x.a,(len(x.a)-3))) as maxcon from ( (select top 20 a,b from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020') x order by b desc
select x.a,x.b,max(right(x.a,(len(x.a)-3))) as maxcon from (select top 20 a,b from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020') x order by b desc
top 20 和MAX不可同用 TO: gyagp,ORDER BY不能用在子查询里最大值只有一个,你的意思是不是按什么分组?select top 20 a,max(right(a,(len(a)-3))) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' GROUP BY LEFT(a,3)
top 20 和MAX不可同用 TO: gyagp,ORDER BY不能用在子查询里最大值只有一个,你的意思是不是按什么分组?select top 20 a,max(right(a,(len(a)-3))) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' GROUP BY LEFT(a,3)
to OpenVMS(半知半解) : 语法是没错误,但是执行是抱错: Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
同意OpenVMS(半知半解)
用STORE PROCEDURE 可以。
to OpenVMS(半知半解) : 语法是没错误,但是执行是抱错: Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
to OpenVMS(半知半解) : 语法是没错误,但是执行是抱错: Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
to OpenVMS(半知半解) : 语法是没错误,但是执行是抱错: Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?为什么我总是不能回复~~:(
select top 20 a,b INTO #T1 from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b descSELECT * FROM #T1 UNION ALL SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b order by b desc
select top 20 a,b INTO #T1 from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b descSELECT * FROM #T1 UNION ALL SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b FROM #T1 order by b desc
sorry,少写一个FROM #T1select top 20 a,b INTO #T1 from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b descSELECT * FROM #T1 UNION ALL SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b FROM #T1 order by b desc
select top 20 a,b,(select top 1 right(a,(len(a)-3)) from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b desc) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b desc
上面错了 : select top 20 a,b,(select top 1 right(a,(len(a)-3)) from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by right(a,(len(a)-3)) desc) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b desc
ok~~~~~~~~现在好了~~~~~但我似懂非懂~~~~ 还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告: There is already an object named '#T1' in the database. 所以执行一次就要把“#T1”改个名字~~~~
ok~~~~~~~~现在好了~~~~~但我似懂非懂~~~~ 还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告: There is already an object named '#T1' in the database. 所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~` OpenVMS(半知半解): 我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)
ok~~~~~~~~现在好了~~~~~但我似懂非懂~~~~ 还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告: There is already an object named '#T1' in the database. 所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~`我怎么总是不能回复~~~~``:((((
ok~~~~~~~~现在好了~~~~~但我似懂非懂~~~~ 还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告: There is already an object named '#T1' in the database. 所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~` OpenVMS(半知半解): 我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)我怎么总是不能回复~~~~``:((((
ok~~~~~~~~现在好了~~~~~但我似懂非懂~~~~ 还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告: There is already an object named '#T1' in the database. 所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~` OpenVMS(半知半解): 我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)我怎么总是不能回复~~~~``:((((
To: CSDNM,你执行看看(有错)
select top 20 a,b INTO #T1 from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b desc 可能字符串后面有空, SELECT * FROM #T1 order by right(RTRIM(a),(len(RTRIM(a))-3)) 或 SELECT * FROM #T1 order by CONVERT(INT,right(RTRIM(a),(len(RTRIM(a))-3))) DROP TABLE #T1
(select top 20 a,b from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020') x
order by b desc
(select top 20 a,b from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020') x
order by b desc
TO: gyagp,ORDER BY不能用在子查询里最大值只有一个,你的意思是不是按什么分组?select top 20 a,max(right(a,(len(a)-3))) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020'
GROUP BY LEFT(a,3)
TO: gyagp,ORDER BY不能用在子查询里最大值只有一个,你的意思是不是按什么分组?select top 20 a,max(right(a,(len(a)-3))) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020'
GROUP BY LEFT(a,3)
语法是没错误,但是执行是抱错:
Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
语法是没错误,但是执行是抱错:
Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
语法是没错误,但是执行是抱错:
Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?
语法是没错误,但是执行是抱错:
Column 'x.a' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Column 'x.b' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.是什么意思?为什么我总是不能回复~~:(
UNION ALL
SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b
order by b desc
UNION ALL
SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b FROM #T1
order by b desc
UNION ALL
SELECT max(right(x.a,(len(x.a)-3))) AS a,'' as b FROM #T1
order by b desc
select top 20 a,b,(select top 1 right(a,(len(a)-3)) from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by right(a,(len(a)-3)) desc) as maxcon from tb1 where aname='st_app1' and cname='st_dev1' and b<='ts_2020' order by b desc
MAX的结果只有一个值(如果不分组),所以只能横排,不好与20条记录竖排
TO:CSDNM,你的也是与 gyagp的一样问题
还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告:
There is already an object named '#T1' in the database.
所以执行一次就要把“#T1”改个名字~~~~
还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告:
There is already an object named '#T1' in the database.
所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~` OpenVMS(半知半解):
我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)
不一样,我的取出了A,B字段.
横排的取出后需要定位才能得到做大值,竖排虽然多了内容但是方便.
你觉得呢?豆豆MM?你觉得呢?
还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告:
There is already an object named '#T1' in the database.
所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~`我怎么总是不能回复~~~~``:((((
还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告:
There is already an object named '#T1' in the database.
所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~`
OpenVMS(半知半解):
我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)我怎么总是不能回复~~~~``:((((
还有个小问题,这样我用rs("a") 取出的值是不是可以这样判断,最后一条就是a 的最大值?还有,上面的sql语句只能执行一次,因为它会报告:
There is already an object named '#T1' in the database.
所以执行一次就要把“#T1”改个名字~~~~竟然差点忘了道谢,谢谢大家~~`
OpenVMS(半知半解):
我对你的仰慕如滔滔江水~~~~~,如黄河泛滥~~~~~~~ :)我怎么总是不能回复~~~~``:((((
可能字符串后面有空,
SELECT * FROM #T1 order by right(RTRIM(a),(len(RTRIM(a))-3))
或
SELECT * FROM #T1 order by CONVERT(INT,right(RTRIM(a),(len(RTRIM(a))-3)))
DROP TABLE #T1