如表A
xd rm
1001 A
1001 B
1001 C
1002 E
1002 F
1003 A
1003 B
1003 C
1003 D
想得到结果为:
xd rm 记录数
1001 A 3
1001 B 3
1001 C 3
1002 E 2
1002 F 2
1003 A 4
1003 B 4
1003 C 4
1003 D 4
就是统计 相同XD 的总数量,用一列显示出来,
------解决方案--------------------
select *,(select count(1) from a where xd=b.xd)
from a b
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-15 11:56:55
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([xd] int,[rm] varchar(1))
insert [a]
select 1001,'A' union all
select 1001,'B' union all
select 1001,'C' union all
select 1002,'E' union all
select 1002,'F' union all
select 1003,'A' union all
select 1003,'B' union all
select 1003,'C' union all
select 1003,'D'
--------------开始查询--------------------------
select * ,COUNT(1)OVER(PARTITION BY xd )记录数
from [a]
----------------结果----------------------------
/*
xd rm 记录数
----------- ---- -----------
1001 A 3
1001 B 3
1001 C 3
1002 E 2
1002 F 2
1003 A 4
1003 B 4
1003 C 4
1003 D 4
*/
------解决方案--------------------
2005及以上
select *,count(*) over(partition by xd) [记录数]
from a