是这个吗?SELECT TOP 3 WITH TIES xyz into # FROM table ORDER BY xyz DESC
1. select count(1) from #
2. select
max(num)
from
(
select xyz,[num]=count(1) from # group by xyz
)t3. select min(xyz) from #
1. select count(1) from #
2. select
max(num)
from
(
select xyz,[num]=count(1) from # group by xyz
)t3. select min(xyz) from #
SELECT TOP 3 WITH TIES xyz into # FROM table ORDER BY xyz DESC--得到的记录条数
1. select count(1) from #--得到列中最小的数字重复的个数
2. select
count(1)
from
#
where
xyz=(select min(xyz) from #)--得到列中最小的数字的值
3. select min(xyz) from #
insert into t# values(10 ,10, 10, 10)
insert into t# values(10 ,10, 8, 8)
insert into t# values(10 ,8, 8, 6)
insert into t# values(10 ,8, 8, 6)
insert into t# values(10 ,8, 8, 6)select * from t#10 10 10 10
10 10 8 8
10 8 8 6
10 8 8 6
10 8 8 61.
select count(a) as a记录数,count(b)as b记录数,count(c)as c记录数,count(d) as d记录数 from t#2.
select count(a)from t# where a= (select min(a) from t#)
union all
select count(b) from t# where b= (select min(b) from t#)
union all
select count(c) from t# where c= (select min(c) from t#)
union all
select count(d) from t# where d= (select min(d) from t#)3.
select min(a) as a最小值,min(b)as b最小值,min(c)as c最小值,min(d) as d最小值 from t#完全满足要求...不过这个问题也太经典了!!!, 不知道我晚上3点做好值不值啊..................
min()求最小值..
select
count(a.xyz)
from
(SELECT TOP 3 WITH TIES xyz FROM table ORDER BY xyz DESC) a2、
select
top 1 count(a.xyz)
from
(SELECT TOP 3 WITH TIES xyz FROM table ORDER BY xyz DESC) a
group by
a.xyz
order by
count(a.xyz) desc3、
select
min(a.xyz)
from
(SELECT TOP 3 WITH TIES xyz FROM table ORDER BY xyz DESC) a
我是在SQLServer2000里测试的,SELECT TOP 3 WITH TIES xyz FROM table ORDER BY xyz DESC
可以通过,但是select
min(a.xyz)
from
(SELECT TOP 3 WITH TIES xyz FROM table ORDER BY xyz DESC) a这个语句提示“列名withties无效”
再帮我看看吧!!
在Sqlserver2000里,如果去掉了“WITH TIES”,即:
select
min(a.xyz)
from
(SELECT TOP 3 xyz FROM table ORDER BY xyz DESC) a也是可以通过的!!!!!!!!!!!!!!!!!!!
为什么呀!!!!!!!!!!!!!!!!!!!!!!