22,206
社区成员
发帖
与我相关
我的任务
分享
select KSH,XM,CJ,MC=RANK() OVER(ORDER BY CJ DESC),BZ=(SELECT COUNT(1) FROM #rank WHERE CJ=R.CJ) from #rank R
create table tb(ksh varchar(10), xm varchar(10), cj int, mc int,bz int)
insert into tb
select '000111','叶丹',170,0,0
union all select '000112','叶丹丹',170,0,0
union all select '000113','叶小',169,0,0
union all select '000114','叶小小',168,0,0
union all select '000115','叶大',168,0,0
union all select '000116','叶大大',168,0,0
union all select '000117','叶大小',167,0,0
go
update tb set
mc = (select count(cj) from tb where cj > t.cj) + 1 ,
bz = (select count(1) from tb where cj = t.cj)
from tb t
select * from tb
/*
ksh xm cj mc bz
---------- ---------- ----------- ----------- -----------
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
000113 叶小 169 3 1
000114 叶小小 168 4 3
000115 叶大 168 4 3
000116 叶大大 168 4 3
000117 叶大小 167 7 1
(所影响的行数为 7 行)
*/
drop table tb
--借用#3兄台的演示数据
select ksh,xm,cj,mc=rank() over(order by cj desc),bz=count(*) over(partition by cj) from #rank
/*
ksh xm cj mc bz
---------- ---------- ----------- -------------------- -----------
000117 叶大小 167 7 1
000114 叶小小 168 4 3
000115 叶大 168 4 3
000116 叶大大 168 4 3
000113 叶小 169 3 1
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
(7 row(s) affected)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 22:56:54
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ksh] varchar(6),[xm] varchar(6),[cj] int,[mc] sql_variant,[bz] sql_variant)
insert [tb]
select '000111','叶丹',170,null,null union all
select '000112','叶丹丹',170,null,null union all
select '000113','叶小',169,null,null union all
select '000114','叶小小',168,null,null union all
select '000115','叶大',168,null,null union all
select '000116','叶大大',168,null,null union all
select '000117','叶大小',167,null,null
--------------开始查询--------------------------
select
b.*,a.bz
from
(select cj,count(1) as bz from tb group by cj)a ,
(select ksh,xm,cj,mc=(select count(cj) FROM tb where cj>t.cj)+1 from tb t)b
where
a.cj=b.cj
order by
ksh
----------------结果----------------------------
/*ksh xm cj mc bz
------ ------ ----------- ----------- -----------
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
000113 叶小 169 3 1
000114 叶小小 168 4 3
000115 叶大 168 4 3
000116 叶大大 168 4 3
000117 叶大小 167 7 1
(7 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-16 22:56:54
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ksh] varchar(6),[xm] varchar(6),[cj] int,[mc] sql_variant,[bz] sql_variant)
insert [tb]
select '000111','叶丹',170,null,null union all
select '000112','叶丹丹',170,null,null union all
select '000113','叶小',169,null,null union all
select '000114','叶小小',168,null,null union all
select '000115','叶大',168,null,null union all
select '000116','叶大大',168,null,null union all
select '000117','叶大小',167,null,null
--------------开始查询--------------------------
select
b.*,a.bz
from
(select cj,count(1) as bz from tb group by cj)a ,
(select ksh,xm,cj,mc=(select count(cj) FROM tb where cj>t.cj)+1 from tb t)b
where
a.cj=b.cj
----------------结果----------------------------
/* ksh xm cj mc bz
------ ------ ----------- ----------- -----------
000117 叶大小 167 7 1
000114 叶小小 168 4 3
000115 叶大 168 4 3
000116 叶大大 168 4 3
000113 叶小 169 3 1
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
(7 行受影响)
*/
SELECT
*,mc=(SELECT COUNT(cj) FROM tb WHERE cj>a.cj)+1
FROM
tb a
ORDER BY
mc
--建表
create table #rank(ksh varchar(10), xm varchar(10), cj int, mc int,bz int)
insert into #rank
select '000111','叶丹',170,0,0
union all select '000112','叶丹丹',170,0,0
union all select '000113','叶小',169,0,0
union all select '000114','叶小小',168,0,0
union all select '000115','叶大',168,0,0
union all select '000116','叶大大',168,0,0
union all select '000117','叶大小',167,0,0
--统计
select identity(int,1,1) as mc,cj,count(0) as bz into #temp from #rank
group by cj
order by cj desc
--更新
update #rank set mc=b.mc,bz=b.bz
from #rank as a,#temp as b
where a.cj=b.cj
update #rank set mc=(select count(0)+1 from #rank where a.cj<cj)
from #rank as a
--结果
select * from #rank
ksh xm cj mc bz
---------- ---------- ----------- ----------- -----------
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
000113 叶小 169 3 1
000114 叶小小 168 4 3
000115 叶大 168 4 3
000116 叶大大 168 4 3
000117 叶大小 167 7 1
(所影响的行数为 7 行)
--建表
create table #rank(ksh varchar(10), xm varchar(10), cj int, mc int,bz int)
insert into #rank
select '000111','叶丹',170,0,0
union all select '000112','叶丹丹',170,0,0
union all select '000113','叶小',169,0,0
union all select '000114','叶小小',168,0,0
union all select '000115','叶大',168,0,0
union all select '000116','叶大大',168,0,0
union all select '000117','叶大小',167,0,0
--统计
select identity(int,1,1) as mc,cj,count(0) as bz into #temp from #rank
group by cj
order by cj desc
--更新
update #rank set mc=b.mc,bz=b.bz
from #rank as a,#temp as b
where a.cj=b.cj
--结果
select * from #rank
ksh xm cj mc bz
---------- ---------- ----------- ----------- -----------
000111 叶丹 170 1 2
000112 叶丹丹 170 1 2
000113 叶小 169 2 1
000114 叶小小 168 3 3
000115 叶大 168 3 3
000116 叶大大 168 3 3
000117 叶大小 167 4 1
(所影响的行数为 7 行)