update a
set col2=ltrim(a.col1*100/b.col)+'%'
from tb a
join(select item_no,sum(col1)col from tb group by item_no) b
on a.item_no=b.item_no
select * from tb
/**
ITEM_NO COL1 col2
------- ----------- ----------
A 100 50%
A 100 50%
B 100 33%
B 100 33%
B 100 33%
(5 行受影响)
**/
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([ITEM_NO] varchar(1),[COL1] int,[col2] sql_variant)
insert #tb
select 'A',100,null union all
select 'A',100,null union all
select 'B',100,null union all
select 'B',100,null union all
select 'B',100,null
--------------------------------查询开始------------------------------
update #tb set col2=ltrim((cast(100.0/(select count(*) from #tb where ITEM_NO=a.ITEM_NO) as decimal(9,2))))+'%'
from #tb a
select * from #tb
/*
ITEM_NO COL1 col2
------- ----------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 100 50.00%
A 100 50.00%
B 100 33.33%
B 100 33.33%
B 100 33.33%
(5 行受影响)
*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ITEM_NO] [nvarchar](10),[COL1] [int],[COL2] [varchar](10))
INSERT INTO [tb]
SELECT 'A','100',NULL UNION ALL
SELECT 'A','100',NULL UNION ALL
SELECT 'B','100',NULL UNION ALL
SELECT 'B','100',NULL UNION ALL
SELECT 'B','100',NULL
--SELECT * FROM [tb]
-->SQL查询如下:
-->SQL查询如下:
;WITH t AS
(
SELECT *, COL3 = LTRIM(COL1*100/SUM(COL1)OVER(PARTITION BY ITEM_NO))+'%'
FROM [tb]
)
UPDATE t SET col2=col3
SELECT * FROM tb
/*
ITEM_NO COL1 COL2
---------- ----------- -------------
A 100 50%
A 100 50%
B 100 33%
B 100 33%
B 100 33%
(5 行受影响)
*/
-- ============================================= -- Author: Maco_wang -- Create date: 2011-03-&...-- Description: 参考htl258(Tony)的思路，改写的计算个税的函数 -- ================================...
---------------------------------------------------------------------------------- ...-- Author : htl258(Tony) -- Date : 2010-06-25 22:23:18 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
HTL，即常说的推挽，就是NPN+PNP的组合，所以其同时支持PNP与NPN控制电路。电路简单，好用，工作电压较宽，编码器常用5到30V均可（注：HTL输出信号电平一般等同工作电压减三极管压降）。有较强的抗干扰能力，它的...
------------------------------...-- Author: htl258(Tony) -- Title : 字符串处理(模糊无序搜索) -- Date : 2009-09-06 21:26:00 -- http://blog.csdn.net/htl258 -- 引用请保留此信息. -----------------...
基于【谭松波ChnSentiCorp-Htl-ba-4000: 平衡语料，正负类各2000篇】的【改良版】语料库。原语料库夹杂着许多重复评论、垃圾评论，以及被错误分类的评论，影响分类模型的评价指数。本语料库4000评论都已经过人工整理...
-------------------------------------------------------------------- ...-- Author : 原著： 改编：htl258(Tony) -- Date : 2010-04-24 06:39:55 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.16
----------------------------------------------------------------...-- Author : 原著： 改编：htl258(Tony) -- Date : 2010-04-24 06:39:55 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X
摘自：http://blog.csdn.net/htl258/article/details/5525814 ------------------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2010-04-25 01:10:28 --...
--原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5540795.aspx -------------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2010-04-28 23:22:15
本文原网址：http://blog.csdn.net/htl258/article/details/5695391 -----------------------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2010-06-25 22:23:...
-- 原帖地址：http://blog.csdn.net/htl258/archive/2010/04/28/5537235.aspx ---------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2010-04-28 02:00:28 --
http://blog.csdn.net/htl258/article/details/5543694 -------------------------------------------------------------------------- ...-- Author : htl258(Tony) -- Date : 2010-04-29 19:07:45 -- V
原文：http://blog.csdn.net/htl258/article/details/5659935 ----------------------------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2010-06-10 01:49:18 -
基于【谭松波ChnSentiCorp-Htl-ba-4000: 平衡语料，正负类各2000篇】的【改良版】语料库。原语料库夹杂着许多重复评论、垃圾评论，以及被错误分类的评论，影响分类模型的评价指数。本语料库4000评论都已经过人工整理...
原文：http://blog.csdn.net/htl258/article/details/4574401 -------------------------------------------- ...--作者：htl258(Tony) --日期：-09-21 02:30 --转载请保留此信息 ----------------------------
转自：http://blog.csdn.net/htl258/article/details/5525814 -------------------------------------------------------------------------- ...-- Author : htl258(Tony) -- Date : 2010-04-25 01:10:28 --
原文出处：http://blog.csdn.net/htl258/article/details/5695391（因无法转载，故复制，感谢原创） ---------------------------------------------------------------...-- Author : htl258(Tony) -- Date : 2
【天通卫星电话】HTL1100功能型天通一号卫星电话规格书.pdf
转自 http://blog.csdn.net/htl258/archive/2010/04/29/5537235.aspx<br /> -------------------------------------------------------------------------- -- Author : htl258(Tony) -- ...
原帖：...Author: htl258(Tony) -- SuBject: SQL2005行列互转整理 -- Date : 2009-08-20 21:00:00 -----------------------------
1 /*------------------------------------------------------------------ 2 3 -- Author : htl258(Tony) 4 5 -- Date : 2010-04-15 22:07:01 6 7 -- Version: Microsoft SQL Server 2008 (RTM) - ...
--------------------------------------------------------------------- ...--Author : htl258(Tony) --Date: 2010-04-28 23:22:15 --Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X8...
htl258 Tony 的无私帮助,解决了我数据库的设计缺陷的问题`` oracle数据库 表是这样的 表名:bookuse_book 表字段: DATECREATED DATE 用来记录录入的时间 CONSUMPTION NUMBER 用来记金额 ...
-------------------------------------------------------...-- Author : SQL2005分页存储过程htl258(Tony) -- Date : 2009-09-22 13:00:50 -- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X...
