楼主这样肯定可以:select UserName,CardNo, Count(*) S into #temp from zhengmei..pass group by UserName ,CardNo order by S desc)SELECT identity(int,1,1) as Rank, LO.USERNAME, LO.CARDNO, LO.S
FROM #TEMP LO
FROM #TEMP LO
(
UserName VARCHAR(32),
CardNo VARCHAR(32)
)INSERT INTO @PassRecord
SELECT '赵德齐', '6999643' UNION ALL
SELECT '管理员', '6999000'UNION ALL
SELECT '赵德齐', '6999643'UNION ALL
SELECT '管理员', '6999000'UNION ALL
SELECT '王五', '1946884'UNION ALL
SELECT '赵德齐', '6999643'
select identity(int,1,1) as Rank ,UserName,CardNo, Count(*) S
into #temp
from @PassRecord group by UserName ,CardNo order by S desc SELECT * FROM #temp /*
Rank UserName CardNo S
----------- -------------------------------- -------------------------------- -----------
1 赵德齐 6999643 3
2 管理员 6999000 2
3 王五 1946884 1
*/
insert into passrecord select '赵德齐','6999643'
insert into passrecord select '管理员','6999000'
insert into passrecord select '赵德齐','6999643'
insert into passrecord select '管理员','6999000'
insert into passrecord select '王五','1946884'
insert into passrecord select '赵德齐','6999643'
declare @n int
select @n=count(*) from(
select distinct username from passrecord
)t
select Rank=@n-count(*)+1,UserName,CardNo from PassRecord group by UserName,CardNo order by @n-count(*)
go
drop table PassRecord
/*
Rank UserName CardNo
----------- ---------- ----------
1 赵德齐 6999643
2 管理员 6999000
3 王五 1946884
*/
insert into passrecord select '赵德齐','6999643'
insert into passrecord select '管理员','6999000'
insert into passrecord select '赵德齐','6999643'
insert into passrecord select '管理员','6999000'
insert into passrecord select '王五','1946884'
insert into passrecord select '赵德齐','6999643'select Rank=count(*),UserName,CardNo from PassRecord group by UserName,CardNo order by count(*)
go
drop table PassRecord
/*
Rank UserName CardNo
----------- ---------- ----------
1 王五 1946884
2 管理员 6999000
3 赵德齐 6999643
*/
FROM #TEMP LO
这里提示identity 必须和 into 一起使用
/*
Rank UserName CardNo
----------- ---------- ----------
1 王五 1946884
2 管理员 6999000
3 赵德齐 6999643
*/这个就是我想要的结果!
我的数据库是 SQL Server 2000
我表里面有数据 ,我执行你上面的
select identity(int,1,1) as Rank ,UserName,CardNo, Count(*) S
into #temp
from @PassRecord group by UserName ,CardNo order by S desc SELECT * FROM #temp 却得出的是3 赵德齐 6999643 3
2 管理员 6999000 2
1 王五 1946884 1
莫非是版本不对?
from #temp
这样就好了
s UserName CardNo
----------- -------------------------------- --------------------------------
1 王五 1946884
2 管理员 6999000
3 赵德齐 6999643
*/
Rank UserName CardNo
----------- -------------------------------- --------------------------------
1 王五 1946884
2 管理员 6999000
3 赵德齐 6999643
*/
我拷贝我要的结果拷贝错了
我要的结果是:/*
Rank UserName CardNo S
----------- -------------------------------- -------------------------------- -----------
1 赵德齐 6999643 3
2 管理员 6999000 2
3 王五 1946884 1
*/晴天 你的结果少了字段
这样的效果
/*
Rank UserName CardNo S
----------- -------------------------------- -------------------------------- -----------
1 赵德齐 6999643 3
2 管理员 6999000 2
3 王五 1946884 1
*/
但我试了二楼的方法
得出的是
/*
Rank UserName CardNo S
----------- -------------------------------- -------------------------------- -----------
3 赵德齐 6999643 3
2 管理员 6999000 2
1 王五 1946884 1
*/我的数据库是 SQL Server 2000
莫非版本问题?
DECLARE @PassRecord TABLE
(
UserName VARCHAR(32),
CardNo VARCHAR(32)
)INSERT INTO @PassRecord
SELECT '赵德齐', '6999643' UNION ALL
SELECT '管理员', '6999000'UNION ALL
SELECT '赵德齐', '6999643'UNION ALL
SELECT '管理员', '6999000'UNION ALL
SELECT '王五', '1946884'UNION ALL
SELECT '赵德齐', '6999643'
select identity(int,1,1) as Rank ,UserName,CardNo, Count(*) S
into #temp
from @PassRecord group by UserName ,CardNo order by S desc select * from #temp
整个代码执行了
还是
/*
Rank UserName CardNo S
----------- -------------------------------- -------------------------------- -----------
3 赵德齐 6999643 3
2 管理员 6999000 2
1 王五 1946884 1
*/ 怎么回事呢???
SQL Server 2000 真搞不懂这个
只是觉得自己还没有找到方法...