update TableName set 资产原值=资产原值+(select 资产原值 from tablename where 名称='技改1'),资产净值=资产净值+(select 资产净值 from tablename where 名称='技改1') where 名称='管理1' delete from tablename where 名称='技改1'
哇,名称也改了啊! update TableName set 名称='合计',资产原值=资产原值+(select 资产原值 from tablename where 名称='技改1'),资产净值=资产净值+(select 资产净值 from tablename where 名称='技改1') where 名称='管理1' delete from tablename where 名称='技改1'
select '合计' as 名称, 1 as 标识, 资产原值=资产原值+(select 资产原值 from tale where 名称 = '技改1'), 资产净值=资产原值+(select 资产净值 from tale where 名称 = '技改1') from table where 名称 = '管理1'union select 名称,标识,资产原值,资产净值 from table where 名称 in('生产1','测绘1') order by 标识
select 名称 = '合计' , 标识 = (select top 1 标识 from tb where 名称 = '管理1') , sum(资产原值) 资产原值,sum(资产净值) 资产净值 from tb where 名称 = '管理1' or 名称 = '技改1' union all select * from tb where 名称 <> '管理1' and 名称 <> '技改1'
------------------------------------ -- Author: happyflsytone -- Date:2008-11-18 23:17:41 -------------------------------------- Test Data: TA IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA Go CREATE TABLE TA(名称 NVARCHAR(3),标识 INT,资产原值 INT,资产净值 INT) Go INSERT INTO TA SELECT '管理1',1,200,150 UNION ALL SELECT '技改1',2,450,250 UNION ALL SELECT '生产1',3,150,80 UNION ALL SELECT '测绘1',4,350,120 GO --Start SELECT 名称 ,标识,sum(资产原值) as 资产原值,sum(资产净值) 资产净值 FROM (select 名称 = case when 标识< 3 then '合计 ' else 名称 end, 标识 = case when 标识< 3 then 1 else 标识 end, 资产原值 ,资产净值 from TA) a group by 名称 ,标识 --Result: /* 名称 标识 资产原值 资产净值 ----- ----------- ----------- ----------- 合计 1 650 400 生产1 3 150 80 测绘1 4 350 120*/ --End
INSERT INTO TableName SELECT '合计' AS 名称, 标识, (select sum(资产原值) from tablename where 标识 in(1,2)) AS 资产原值, (select sum(资产净值) from tablename where 标识 in(1,2)) AS 资产净值 FROM TableName WHERE 标识=1;DELETE FROM TableName WHERE 名称 IN('管理1','技改1');SELECT * FROM TableName /** 名称 标识 资产原值 资产净值 生产1 3 150 80 测绘1 4 350 120 合计 1 650 400 **/以上,在ACCESS2003中测试通过
select * from 表 where 名称 not in (管理1,技改1) union all select '合计' ,min(标识),sum(资产原值),sum(资产净值) from 表 where 名称 in (管理1,技改1) order by 2
create table #d ( name nvarchar(20), id int, price1 int, price2 int ) insert into #d select '管理1', 1, 200, 150 union all select '技改1', 2, 450, 250 union all select '生产1', 3, 150, 80 union all select '测绘1', 4, 350, 120 insert into #d select name='合计',id=1,SUM(price1) AS price1,SUM(price2) AS price2 from #d where name='管理1' or name='技改1' delete from #d where name='管理1' or name='技改1' select * from #d drop table #d
SELECT 名称='合计',标识=sum(t.标识),资产原值=sum(t.资产原值),资产净值=sum(t.资产净值) FROM TableName t WHERE t.名称 in('管理1','技改1') UNION SELECT * FROM tb WHERE 名称 not in('管理1','技改1') order by 名称 ,标识 go
declare @t table (名称 varchar(6),标识 int,资产原值 int,资产净值 int) insert into @t select '管理1',1,200,150 union all select '技改1',2,450,250 union all select '生产1',3,150,80 union all select '测绘1',4,350,120 insert into @t select 名称='总计',标识=1,资产原值=(select sum(资产原值) from @t where 标识 in(1,2)), 资产净值=(select sum(资产净值) from @t where 标识 in(1,2)) delete @t where 名称 in('管理1','技改1') select * from @t order by 标识总计 1 650 400 生产1 3 150 80 测绘1 4 350 120
delete from tablename where 名称='技改1'
update TableName set 名称='合计',资产原值=资产原值+(select 资产原值 from tablename where 名称='技改1'),资产净值=资产净值+(select 资产净值 from tablename where 名称='技改1') where 名称='管理1'
delete from tablename where 名称='技改1'
select '合计' as 名称, 1 as 标识, 资产原值=资产原值+(select 资产原值 from tale where 名称 = '技改1'),
资产净值=资产原值+(select 资产净值 from tale where 名称 = '技改1')
from table where 名称 = '管理1'union select 名称,标识,资产原值,资产净值
from table
where 名称 in('生产1','测绘1')
order by 标识
union all
select * from tb where 名称 <> '管理1' and 名称 <> '技改1'
-- Author: happyflsytone
-- Date:2008-11-18 23:17:41
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(名称 NVARCHAR(3),标识 INT,资产原值 INT,资产净值 INT)
Go
INSERT INTO TA
SELECT '管理1',1,200,150 UNION ALL
SELECT '技改1',2,450,250 UNION ALL
SELECT '生产1',3,150,80 UNION ALL
SELECT '测绘1',4,350,120
GO
--Start
SELECT
名称 ,标识,sum(资产原值) as 资产原值,sum(资产净值) 资产净值
FROM
(select 名称 = case when 标识< 3 then '合计 ' else 名称 end,
标识 = case when 标识< 3 then 1 else 标识 end,
资产原值 ,资产净值 from TA) a
group by 名称 ,标识
--Result:
/*
名称 标识 资产原值 资产净值
----- ----------- ----------- -----------
合计 1 650 400
生产1 3 150 80
测绘1 4 350 120*/
--End
SELECT '合计' AS 名称, 标识, (select sum(资产原值) from tablename where 标识 in(1,2)) AS 资产原值, (select sum(资产净值) from tablename where 标识 in(1,2)) AS 资产净值
FROM TableName
WHERE 标识=1;DELETE FROM TableName WHERE 名称 IN('管理1','技改1');SELECT * FROM TableName
/**
名称 标识 资产原值 资产净值
生产1 3 150 80
测绘1 4 350 120
合计 1 650 400
**/以上,在ACCESS2003中测试通过
union all
select '合计' ,min(标识),sum(资产原值),sum(资产净值)
from 表 where 名称 in (管理1,技改1) order by 2
(
name nvarchar(20),
id int,
price1 int,
price2 int
)
insert into #d select
'管理1', 1, 200, 150 union all
select '技改1', 2, 450, 250 union all
select '生产1', 3, 150, 80 union all
select '测绘1', 4, 350, 120
insert into #d
select name='合计',id=1,SUM(price1) AS price1,SUM(price2) AS price2 from #d where name='管理1' or name='技改1'
delete from #d where name='管理1' or name='技改1'
select * from #d
drop table #d
SELECT 名称='合计',标识=sum(t.标识),资产原值=sum(t.资产原值),资产净值=sum(t.资产净值) FROM TableName t WHERE t.名称 in('管理1','技改1')
UNION
SELECT * FROM tb WHERE 名称 not in('管理1','技改1')
order by 名称 ,标识
go
declare @t table (名称 varchar(6),标识 int,资产原值 int,资产净值 int)
insert into @t select '管理1',1,200,150
union all select '技改1',2,450,250
union all select '生产1',3,150,80
union all select '测绘1',4,350,120
insert into @t
select 名称='总计',标识=1,资产原值=(select sum(资产原值) from @t where 标识 in(1,2)),
资产净值=(select sum(资产净值) from @t where 标识 in(1,2))
delete @t where 名称 in('管理1','技改1')
select * from @t
order by 标识总计 1 650 400
生产1 3 150 80
测绘1 4 350 120