后面跟个数量(小问题)

[iey000]

表a:

---------------------------
id      num
A        10
A        11
B        34
A        88
B        23
---------------------------

我现在想对表A进行检索,想要的结果是:

---------------------------
id      num    order
A       10      3
A       11      3
A       88      3
B       34      2
B       23      2
---------------------------

意思应该很简单吧,我想在后面加个count(id)显示出来,怎么写?

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([id] varchar(1),[num] int)
insert [TB]
select 'A',10 union all
select 'A',11 union all
select 'B',34 union all
select 'A',88 union all
select 'B',23
GO



--> 查询结果
SELECT * FROM [TB]

SELECT [TB].id,[TB].num ,T.[order]
FROM [TB],(select id,COUNT(1) as [order] from TB a group by id)T
where TB.id=T.id
--> 删除表格
--DROP TABLE [TB]

[喜-喜]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id char(2),
num int
)
go
--插入测试数据
insert into tb select 'A',10
union all select 'A',11
union all select 'B',34
union all select 'A',88
union all select 'B',23
go
--代码实现

select *,[order]=(select count(*) from tb where [id]=t.[id])
from tb t

/*测试结果

id num order
---------------------
A 	10	3
A 	11	3
B 	34	2
A 	88	3
B 	23	2

(5 行受影响)
*/

[abuying]

select a.id,a.name,b.order  from tb a inner join (select id,count(id) as order from tb group by id ) as b on
a.id=b.id

[envykok]

if object_id('DurationSGDB.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id char(2),
num int
)
go
--插入测试数据
insert into tb select 'A',10
union all select 'A',11
union all select 'B',34
union all select 'A',88
union all select 'B',23
go



SELECT tb.id, tb.num, tb_2.[order] 
FROM tb
INNER JOIN 
(SELECT id,COUNT(*) AS [order] FROM tb GROUP BY id) AS tb_2 ON tb.id=tb_2.id