sql语句请高手帮忙！
表AA
id user_id class_id shu
1 100 2 50
2 200 4 5
3 100 3 10
4 100 2 20
5 100 2 20
6 100 3 20
结果：
id user_id class_id shu
1 100 2 90
2 100 3 30
[解决办法]
2 200 4 5这条记录不计入结果地原因是？？？
[解决办法]
Select
[user_id],
class_id,
SUM(shu) As shu
From
AA
Where [user_id] = 100
Group By [user_id], class_id
[解决办法]
如果要生成前面的ID列的話，就要借助臨時表了。
[解决办法]
declare @t table(id int,[user_id] int,class_id int,shu int)
insert @t select 1, 100, 2, 50
union all select 2, 200, 4, 5
union all select 3, 100, 3, 10
union all select 4, 100, 2, 20
union all select 5, 100, 2, 20
union all select 6, 100, 3, 20
--方法1
select identity(int,1,1) as id,[user_id],class_id,sum(shu) as shu into #t from @t where [user_id]=100 group by [user_id],class_id
select * from #t
drop table #t
--方法2
select (select count(distinct class_id)+1 from @t where class_id <a.class_id and [user_id]=100) as id,
[user_id],class_id,sum(shu) as shu
from @t a where [user_id]=100 group by [user_id],class_id
[解决办法]
结果忘了贴
（所影响的行数为 6 行）
（所影响的行数为 2 行）
id user_id class_id shu
----------- ----------- ----------- -----------
1 100 2 90
2 100 3 30
（所影响的行数为 2 行）
id user_id class_id shu
----------- ----------- ----------- -----------
1 100 2 90
2 100 3 30
（所影响的行数为 2 行）
[解决办法]
与BB关联的依据是什么？？？？如果是AA.id=BB.ID的话，问什么class_id=2对应的是id=1的BB记录，为什么不是4的？
[解决办法]
Select
A.*, B.*
From
(Select
[user_id],
class_id,
SUM(shu) As shu
From
AA
Where [user_id] = 100
Group By [user_id], class_id
) A
Inner Join
BB B
On A.class_id = B.id
[解决办法]
select ....,b.name,b.tag,....
from AA a inner join BB b on a.class_id=b.id where [user_id]=100 group by [user_id],class_id,b.name,b.tag...
多select几个字段，group by 里也同样要加几个，其他不变
[解决办法]
create table ##AA
(
id varchar(50) Not Null,
user_id varchar(50) Not Null,
class_id varchar(50) Not Null,
shu int
)
insert into ##AA(id,user_id,class_id,shu)
select '1 ', '100 ', '2 ',50
union select '2 ', '200 ', '4 ',5
union select '3 ', '100 ', '3 ',10
union select '4 ', '100 ', '2 ',20
union select '5 ', '100 ', '2 ',20
union select '6 ', '100 ', '3 ',20
select IDENTITY(int,1,1) id,user_id,class_id,sum(shu) shu into #a from ##AA group by class_id,user_id having user_id = 100
select * from #a
drop table ##AA
drop table #a
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id int,
userid int,
class_id int,
shu int
)
insert into tb(id,userid,class_id,shu) values(1, 100, 2, 50)
insert into tb(id,userid,class_id,shu) values(2, 200, 4, 5)
insert into tb(id,userid,class_id,shu) values(3, 100, 3, 10)
insert into tb(id,userid,class_id,shu) values(4, 100, 2, 20)
insert into tb(id,userid,class_id,shu) values(5, 100, 2, 20)
insert into tb(id,userid,class_id,shu) values(6, 100, 3, 20)
select id =identity(int,1,1) , userid,class_id , sum(shu) shu into test from tb where userid = 100 group by userid , class_id
select * from test
drop table tb,test
/*result
id userid class_id shu
----------- ----------- ----------- -----------
1 100 2 90
2 100 3 30
（所影响的行数为 2 行）
*/
