SQL高手_读书人

求助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求和家到底一条数据上？
[解决办法]

引用:

什么规则？gid求和家到底一条数据上？

gid等于pid的话，就把money相加放到pid的这一行上
[解决办法]
看不懂啊
[解决办法]
楼主试试这个，其中dbo.Table_1
建表语句
CREATE TABLE [dbo].[Table_1](
[id] [nchar](10) NULL,
[pid] [nchar](10) NULL,
[gid] [nchar](10) NULL,
[money] [int] NULL,
[sdatetime] [datetime] NULL
) ON [PRIMARY]

GO

查询语句：
SELECT MQ.ID
,MQ.PID
,MQ.GID
,SQ.NEWMONOEY
FROM dbo.Table_1 MQ

LEFT JOIN(
SELECT A.PID,Sum(CASE WHEN B.MONEY IS NULL THEN A.MONEY ELSE B.MONEY END) AS NEWMONOEY FROM dbo.Table_1 A
LEFT JOIN dbo.Table_1 B
ON A.PID = B.GID
GROUP BY A.PID) SQ
ON MQ.PID = SQ.PID

[解决办法]


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 a.id,a.pid,a.gid,
(case when a.pid = a.gid then b.money else a.money end) money,a.time
from table a
left join (select gid,sum(isnull(money,0)) money from table group by gid) b
on a.gid = b.gid
[解决办法]
不对呀，就是第一条数据更新了gid的值吧，再查出来不就好呢，大家说呢
update table name
Set gid = 290 where id= 1;

select * from table name ...
[解决办法]
楼上先看看别人的额回复嘛
[解决办法]
如果不想更新库的话。

select id,pid,gid,case when id=1 then 290 else money end as money ,time
from table
[解决办法]
该回复于2012-04-23 09:10:39被版主删除
[解决办法]
该回复于2012-04-26 08:18:04被版主删除
[解决办法]


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

[解决办法]
这个不知道怎么搞啊
[解决办法]
select
id,pid,gid,case when pid=gid then sum(money)over(PARTITION by gid) else money end as money
from [Table_1]

请来分，谢谢

SQL高手

热点推荐