表1
ID title1 money1 title2 money2 title3 money3 title4 money4
1 开发部1 100 市场部1 200 开发部2 200 市场部2 300
2 开发3 100 市场3 200 开发4 200 市场4 300表2
Dep totelDep
开发部 开
开发 开
市场部 市
市场 市求得结果
开 600
市 1000
ID title1 money1 title2 money2 title3 money3 title4 money4
1 开发部1 100 市场部1 200 开发部2 200 市场部2 300
2 开发3 100 市场3 200 开发4 200 市场4 300表2
Dep totelDep
开发部 开
开发 开
市场部 市
市场 市求得结果
开 600
市 1000
解决方案 »
- object_id与select * from dbo.sysobjects where id = object_id的区别
- SQL查询时的怪异现象?
- mysql更新问题,多语句
- 新安装的sqlserver2005没有数据库,也没有实例
- 请问怎么开1433!服务器网络实用工具里面tcp已经是1433了!放火墙关过!也没用!谢谢大家!(一定给分)
- 数据库损坏如何修复???
- Sql 2005 多表字段合并问题
- 没有任何用户使用和链接SQL数据服务,网络也有活动
- 求助,查找某表指定列中存在的相同值,可能存在多个,并且对比该值,用另一列中建立日期不同作为线索,只保留最新的行
- Sql语句疑难问题,请大家帮忙看看
- 如何利用shutdown.exe 的at实现定时关机
- 救命啊,恢复一个表的所有数据
(
select title1 title , money1 money from tb1
union all
select title2 title , money2 money from tb1
union all
select title3 title , money3 money from tb1
union all
select title4 title , money4 money from tb1
) n
where charindex(m.Dep,n.title) > 0
group by m.totelDep
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (ID int,title1 varchar(7),money1 int,title2 varchar(7),money2 int,title3 varchar(7),money3 int,title4 varchar(7),money4 int)
insert into #ta
select 1,'开发部1',100,'市场部1',200,'开发部2',200,'市场部2',300 union all
select 2,'开发3',100,'市场3',200,'开发4',200,'市场4',300
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (Dep varchar(6),totelDep varchar(2))
insert into #tb
select '开发部','开' union all
select '开发','开' union all
select '市场部','市' union all
select '市场','市'select totelDep,money1=sum(money1)
from (
select title1,money1 from #ta
union all
select title2,money2 from #ta
union all
select title3,money3 from #ta
union all
select title4,money4 from #ta
)a,
(select distinct totelDep from #tb) b
where a.title1 like totelDep+'%'
group by totelDep
totelDep money1
-------- -----------
开 600
市 1000(2 行受影响)--select * from #tb
Dep totelDep
开发部 部门1
开发 部门1
市场部 部门2
市场 部门2
哪???
[Id] int identity(1,1)
,[title1] NVARCHAR(255)
,[money1] DECIMAL(9,2)
,[title2] NVARCHAR(255)
,[money2] DECIMAL(9,2)
,[title3] NVARCHAR(255)
,[money3] DECIMAL(9,2)
,[title4] NVARCHAR(255)
,[money4] DECIMAL(9,2)
)INSERT INTO TB SELECT '开发部1',100,'市场部1',200,'开发部2',200,'市场部2',300
UNION ALL SELECT '开发3',100,'市场3',200,'开发4',200,'市场4',300
GOCREATE TABLE TB2(
[Dep] NVARCHAR(255)
,[totelDep] NVARCHAR(255)
)INSERT INTO TB2
SELECT '开发部','开'
UNION ALL SELECT '开发','开'
UNION ALL SELECT '市场部','市'
UNION ALL SELECT '市场','市'GOSELECT * FROM TB
SELECT * FROM TB2SELECT '开',SUM(CASE WHEN CHARINDEX('开',[title1])>0 THEN money1 ELSE 0 END+CASE WHEN CHARINDEX('开',[title2])>0 THEN money2 ELSE 0 END+CASE WHEN CHARINDEX('开',[title3])>0 THEN money3 ELSE 0 END+CASE WHEN CHARINDEX('开',[title4])>0 THEN money4 ELSE 0 END) FROM TB
UNION ALL
SELECT '市',SUM(CASE WHEN CHARINDEX('市',[title1])>0 THEN money1 ELSE 0 END+CASE WHEN CHARINDEX('市',[title2])>0 THEN money2 ELSE 0 END+CASE WHEN CHARINDEX('市',[title3])>0 THEN money3 ELSE 0 END+CASE WHEN CHARINDEX('市',[title4])>0 THEN money4 ELSE 0 END) FROM TB
-------- -----------
开 900
市 1500
insert into tb1
select 1,'开发部1',100,'市场部1',200,'开发部2',200,'市场部2',300 union all
select 2,'开发3',100,'市场3',200,'开发4',200,'市场4',300
create table tb2 (Dep varchar(6),totelDep varchar(2))
insert into tb2
select '开发部','开' union all
select '开发','开' union all
select '市场部','市' union all
select '市场','市'goselect m.totelDep , sum(money) money from tb2 m,
(
select replace(replace(replace(replace(title1,'1',''),'2',''),'3',''),'4','') title , money1 money from tb1
union all
select replace(replace(replace(replace(title2,'1',''),'2',''),'3',''),'4','') title , money2 money from tb1
union all
select replace(replace(replace(replace(title3,'1',''),'2',''),'3',''),'4','') title , money3 money from tb1
union all
select replace(replace(replace(replace(title4,'1',''),'2',''),'3',''),'4','') title , money4 money from tb1
) n
where m.Dep = n.title
group by m.totelDep
drop table tb1 , tb2/*
totelDep money
-------- -----------
开 600
市 1000(所影响的行数为 2 行)
*/
insert into tb1
select 1,'开发部1',100,'市场部1',200,'开发部2',200,'市场部2',300 union all
select 2,'开发3',100,'市场3',200,'开发4',200,'市场4',300
create table tb2 (Dep varchar(6),totelDep varchar(2))
insert into tb2
select '开发部','开' union all
select '开发','开' union all
select '市场部','市' union all
select '市场','市'goselect m.totelDep , sum(money) money from tb2 m,
(
select replace(replace(replace(replace(title1,'1',''),'2',''),'3',''),'4','') title , money1 money from tb1
union all
select replace(replace(replace(replace(title2,'1',''),'2',''),'3',''),'4','') title , money2 money from tb1
union all
select replace(replace(replace(replace(title3,'1',''),'2',''),'3',''),'4','') title , money3 money from tb1
union all
select replace(replace(replace(replace(title4,'1',''),'2',''),'3',''),'4','') title , money4 money from tb1
) n
where m.Dep = n.title
group by m.totelDep
drop table tb1 , tb2