34,588
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-14 14:10:56
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[用户名] nvarchar(4))
insert [huang]
select 1,'aa' union all
select 2,'bb' union all
select 3,'aa' union all
select 4,'cc' union all
select 5,'aa' union all
select 6,'bb' union all
select 7,'cc'
--------------生成数据--------------------------
select * ,(SELECT COUNT([用户名]) FROM huang WHERE huang.[用户名]=a.[用户名] AND huang.id<a.id)老用户数
from [huang] A
----------------结果----------------------------
/*
ID 用户名 老用户数
----------- ---- -----------
1 aa 0
2 bb 0
3 aa 1
4 cc 0
5 aa 2
6 bb 1
7 cc 1
*/