我有一张表vote:
id data
01 30
01 12
01 15
02 6
03 7如何用SQL语句修改,改完后的新表为:
id data
01 57
02 6
03 7意思就是如果id相同,那么data相加,并更新到表vote中。在线急等,谢谢各位大侠们。
id data
01 30
01 12
01 15
02 6
03 7如何用SQL语句修改,改完后的新表为:
id data
01 57
02 6
03 7意思就是如果id相同,那么data相加,并更新到表vote中。在线急等,谢谢各位大侠们。
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-05 23:42:16
-- Version:
-- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
-- May 3 2005 23:18:38
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[data] int)
insert [tb]
select '01',30 union all
select '01',12 union all
select '01',15 union all
select '02',6 union all
select '03',7
--------------开始查询--------------------------
update tb set data=b.data from tb a,(select id,sum(data) as data from tb group by id)b where a.id=b.idselect * from tb
----------------结果----------------------------
/* id data
---- -----------
01 57
01 57
01 57
02 6
03 7(所影响的行数为 5 行)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-06 00:20:04
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [vote]
IF OBJECT_ID('[vote]') IS NOT NULL
DROP TABLE [vote]
GO
CREATE TABLE [vote] ([id] [nvarchar](10),[data] [int])
INSERT INTO [vote]
SELECT '01','30' UNION ALL
SELECT '01','12' UNION ALL
SELECT '01','15' UNION ALL
SELECT '02','6' UNION ALL
SELECT '03','7'--SELECT * FROM [vote]-->SQL2008查询如下:
MERGE [vote] A
USING (
SELECT RN=ROW_NUMBER()OVER(ORDER BY ID),ID,SUM([data]) [data]
FROM [vote]
GROUP BY id
) B
ON A.ID=B.ID AND A.[data]=B.[data]
WHEN NOT MATCHED THEN INSERT VALUES(B.ID,B.[data])
WHEN NOT MATCHED BY SOURCE THEN DELETE ;SELECT * FROM vote
/*
id data
---------- -----------
01 57
02 6
03 7(3 行受影响)
*/
MERGE [vote] A
USING (
SELECT ID,SUM([data]) [data] --可以省略RN
FROM [vote]
GROUP BY id
) B
ON A.ID=B.ID AND A.[data]=B.[data]
WHEN NOT MATCHED THEN INSERT VALUES(B.ID,B.[data])
WHEN NOT MATCHED BY SOURCE THEN DELETE ;SELECT * FROM vote
/*
id data
---------- -----------
01 57
02 6
03 7(3 行受影响)
*/
这样数据直接到新表test