27,579
社区成员
发帖
与我相关
我的任务
分享
create table [tb]([名称] varchar(1),[数量] int)
insert [tb]
select 'A',1 union all
select 'A',2 union all
select 'A',3 union all
select 'B',1 union all
select 'B',2 union all
select 'B',3
select *,
cast(数量 as varchar(10))+'/'+cast((SELECT SUM(数量) FROM tb WHERE 名称=A.名称) as varchar(10))
from tb as a
/*
A 1 1/6
A 2 2/6
A 3 3/6
B 1 1/6
B 2 2/6
B 3 3/6
*/
declare @T table (名称 varchar(1),数量 int)
insert into @T
select 'A',1 union all
select 'A',2 union all
select 'A',3 union all
select 'B',1 union all
select 'B',2 union all
select 'B',3
select *,
新列=LTRIM(数量)+'/'+LTRIM((SELECT SUM(数量) FROM @t WHERE 名称=A.名称))
from @T a
/*
名称 数量 新列
---- ----------- -------------------------
A 1 1/6
A 2 2/6
A 3 3/6
B 1 1/6
B 2 2/6
B 3 3/6
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-19 23:16:45
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([名称] varchar(1),[数量] int)
insert [tb]
select 'A',1 union all
select 'A',2 union all
select 'A',3 union all
select 'B',1 union all
select 'B',2 union all
select 'B',3
--------------开始查询--------------------------
select *,数量*1.0/(select sum(数量) from tb where 名称=t.名称) from tb t
----------------结果----------------------------
/* 名称 数量
---- ----------- ---------------------------------------
A 1 0.166666666666
A 2 0.333333333333
A 3 0.500000000000
B 1 0.166666666666
B 2 0.333333333333
B 3 0.500000000000
*/