解决方案 »
- 根据所下载书的名字,按下载次数从大到小排列
- 如何快速执行多条Sql语句(如insert,update)
- sql2000有像MS access这样的工具吗?
- 太简单了,高手千万别进来
- sql server2008 报表管理器出现错误:xml文档(1,2150)中有错误“”(十六进制值 0*01)是无效字符,行1,位置2150
- join 概念
- 数据库日志无法收缩
- 如何将#临时表的内容,添加到@内存表中.
- 一个关于SQL的字符串的问题
- 在执行 xp_cmdshell 的过程中出错。调用 'CreateProcess' 失败,错误代码: '5'。
- 索引创建了以后,怎么用啊????只会创建不会用啊!!!!!!!
- 二个库中的二个表同步的问题...
WITH s1 AS (
SELECT col_e, col_p, SUM(col_v) v
FROM t1
GROUP BY col_e, col_p
)
UPDATE t1
SET t1.col_v = t1.col_v + t2.col_v / s1.v
FROM t1, s1, t2
WHERE t1.col_e = s1.col_e AND t1.col_p = s1.col_p
AND t1.col_e = t2.col_e AND t1.col_p = t2.col_p
--再添加不存在的
INSERT INTO t1
SELECT *
FROM t2
WHERE NOT EXISTS (SELECT *
FROM t1
WHERE t1.col_e = t2.col_e AND t1.col_p = t2.col_p)
(
select 'e1','p1','v1' union all
select 'e1','p2','v2' union all
select 'e2','p3','v3' union all
select 'e2','p4','v4'
),t2(col_e,col_p,col_v) as
(
select 'e1','p','v5' union all
select 'e2','p','v6' union all
select 'e3','p','v7'
)select g.col_e,g.col_p,g.col_v+'+'+L.col_v+'*'+g.k as col_v from
(select *,
stuff((select '+'+a.col_v from t1 as a where a.col_e=t1.col_e for xml path(''))+')',1,1,'(') as k
from T1) as g left join t2 as L on g.col_e=L.col_e
union all select * from t2 where not exists(select * from t1 where t1.col_e=t2.col_e)
(
select 'e1','p1','v1' union all
select 'e1','p2','v2' union all
select 'e2','p3','v3' union all
select 'e2','p4','v4'
),t2(col_e,col_p,col_v) as
(
select 'e1','p','v5' union all
select 'e2','p','v6' union all
select 'e3','p','v7'
)select g.col_e,g.col_p,g.col_v+'+'+L.col_v+'*'+g.col_v+'/'+g.k as col_v from (select *,stuff((select '+'+a.col_v from t1 as a where a.col_e=t1.col_e for xml path(''))+')',1,1,'(') as kfrom T1) as g left join t2 as L on g.col_e=L.col_e union all select * from t2 where not exists(select * from t1 where t1.col_e=t2.col_e)