求助SQL高手
id pid gid money time
1 1 1 30.00 2012-04-07 21:25:06.810
2 2 1 80.00 2012-04-07 00:00:00.000
3 3 1 60.00 2012-04-07 00:00:00.000
4 4 1 120.00 2012-04-07 00:00:00.000
5 5 5 110.00 2012-04-07 00:00:00.000
6 6 6 200.00 2012-04-07 00:00:00.000
如何得到如下结果
id pid gid money time
1 1 1 290.00 2012-04-07 21:25:06.810
2 2 1 80.00 2012-04-07 00:00:00.000
3 3 1 60.00 2012-04-07 00:00:00.000
4 4 1 120.00 2012-04-07 00:00:00.000
5 5 5 110.00 2012-04-07 00:00:00.000
6 6 6 200.00 2012-04-07 00:00:00.000
[解决办法]
什么规则?gid求和家到底一条数据上?
[解决办法]
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'1 ', N'1 ', N'1 ', 30, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'2 ', N'2 ', N'1 ', 80, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'3 ', N'3 ', N'1 ', 60, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'4 ', N'4 ', N'1 ', 120, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'5 ', N'5 ', N'5 ', 110, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'6 ', N'6 ', N'6 ', 200, CAST(0x0000A032011CBBCC AS DateTime))
INSERT [dbo].[Table_1] ([id], [pid], [gid], [money], [sdatetime]) VALUES (N'7 ', N'7 ', N'6 ', 100, NULL)
SELECT TOP 1000 t1.*
,CASE
WHEN t2.newmoney IS NULL THEN t1.money
ELSE t2.newmoney
END AS newmoney
FROM .[dbo].[Table_1] t1
LEFT JOIN (SELECT gid
,Sum(money) AS newmoney
FROM [dbo].[Table_1]
GROUP BY gid)t2
ON t1.pid = t2.gid
UPDATE Table_1
SET Table_1.money = t3.newmoney
FROM ( SELECT t1.* ,
t2.newmoney
FROM .[dbo].[Table_1] t1
INNER JOIN ( SELECT gid ,
SUM(money) AS newmoney
FROM [dbo].[Table_1]
GROUP BY gid
) t2 ON t1.pid = t2.gid
) t3
WHERE Table_1.id = t3.id