SQL求租

nlpz 2010-11-11 11:53:59
---ta
C1 C2
1 aa
2 aa
3 aa
4 bb
5 bb
------要求结果
C1 C2 C3
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2

...全文
77 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
fpzgm 2010-11-11
  • 打赏
  • 举报
回复


--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([C1] int,[C2] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'

---2000
select
*,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1)
from
tb t

---2005,2008
select
*,c3=row_number()over(partition by c2 order by c1)
from
tb
/* C1 C2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2

(5 行受影响)
*/

-晴天 2010-11-11
  • 打赏
  • 举报
回复
create table [tb]([C1] int,[C2] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'
go
select c1,c2,c1-(select min(c1) from tb where c2=a.c2)+1 as c3 from tb a
go
drop table tb
/*
c1 c2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2

(5 行受影响)
*/
--小F-- 2010-11-11
  • 打赏
  • 举报
回复
2000的
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-11 12:10:40
-- Version:
-- 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]([C1] int,[C2] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'
--------------开始查询--------------------------
---2000
select
*,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1)
from
tb t

---2005
select
*,c3=row_number()over(partition by c2 order by c1)
from
tb
----------------结果----------------------------
/* C1 C2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2

(5 行受影响)
*/
王向飞 2010-11-11
  • 打赏
  • 举报
回复
select *,ROW_NUMBER()OVER (PARTITION BY [c2] ORDER BY [c2]) AS C3
from ta
nlpz 2010-11-11
  • 打赏
  • 举报
回复
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
GO
CREATE TABLE [ta] ([c1] [int],[c2] [nvarchar](10))
INSERT INTO [ta]
SELECT '1','aa' UNION ALL
SELECT '2','aa' UNION ALL
SELECT '3','aa' UNION ALL
SELECT '4','bb' UNION ALL
SELECT '5','bb'
chenguang79 2010-11-11
  • 打赏
  • 举报
回复
CREATE TABLE tt(
id int,
c2 varchar(10)
)



INSERT INTO tt
SELECT 1,'aa' UNION ALL
SELECT 2,'aa' UNION ALL
SELECT 3,'aa' UNION ALL
SELECT 4,'bb' UNION ALL
SELECT 5,'bb'


SELECT *,ROW_NUMBER() OVER(partition by c2 ORDER BY id) AS c3 FROM tt

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧