sql 查询求和更新问题。

[lasmode]

假设有表table。内有3个字段name,item,count 三字段都是int类型
3个字段均有可能有重复值。
比如
5566 6677 100
5566 6677 200
5566 2233 44
4455 6677 50
类推……
请问怎样才能实现自动把name字段里同一值的记录里的特定值的count字段求和。
比如上例，我要把item字段里值为6677的记录的count求和并更新。其结果就是
5566 6677 344
4455 6677 50

很难表述。。不是item字段里值为6677的count值都求和，name值5566和4455的要分别计算。崩溃。。希望能有人高人帮忙。

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([name] int,[item] int,[count] int)
insert [TB]
select 5566,6677,100 union all
select 5566,6677,200 union all
select 5566,2233,44 union all
select 4455,6677,50

select name,[item]=max([item]),[count]=sum([count])
from tb group by name 
order by sum([count]) desc
/*
name        item        count
----------- ----------- -----------
5566        6677        344
4455        6677        50

(2 行受影响)

*/
drop table TB

[feixianxxx]

if object_id('[TB]') is not null 
drop table [TB]
go
create table [TB]([name] int,[item] int,[count] int)
insert [TB]
select 5566,6677,100 union all
select 5566,6677,200 union all
select 5566,2233,44 union all
select 4455,6677,50

select name,[item]=max([item]),[count]=sum([count])
from tb 
group by name 
(4 行受影响)
name        item        count
----------- ----------- -----------
4455        6677        50
5566        6677        344

[feixianxxx]

if object_id('[TB]') is not null 
drop table [TB]
go
create table [TB]([name] int,[item] int,[count] int)
insert [TB]
select 5566,6677,100 union all
select 5566,6677,200 union all
select 5566,2233,44 union all
select 4455,6677,50

select name,[item],[count]=sum([count])
from tb 
where item='6677'
group by name ,[item]
order by name desc 
/*
name        item        count
----------- ----------- -----------
5566        6677        300
4455        6677        50
*/

也许是这样 呵呵

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-27 19:30:36
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] int,[item] int,[count] int)
insert [tb]
select 5566,6677,100 union all
select 5566,6677,200 union all
select 5566,2233,44 union all
select 4455,6677,50
--------------开始查询--------------------------
select 
  name,[item],
  sum([count]) as [count]
from 
  tb 
where 
  item='6677'
group by 
  [name] ,[item]
order by 
  [name] desc 

----------------结果----------------------------
/*name        item        count
----------- ----------- -----------
5566        6677        300
4455        6677        50

(2 行受影响)

*/

[lasmode]

不对，我建表测试，老出错。对了， 我是mysql。。还有。name表里有几千条记录。拜托各位了。

[feixianxxx]

[Quote=引用 5 楼 lasmode 的回复:]
不对，我建表测试，老出错。对了， 我是mysql。。还有。name表里有几千条记录。拜托各位了。
[/Quote]
不早说 以上都是MSSQL语法。。

不过应该差不多 提示出错什么地方

[lasmode]

2行
select
name,[item],
这个位置。

[lasmode]

[fanzhouqi]

帮顶+学习