A B两张表
A表id 主键 自增
name 用户名
B表
bid 主键 自增
id 用户id
wincount 赢的局数
lostcount 输的局数我想得出以下的效果用户名 游戏总局数张三 110局
李四 95局
王五 55局
....
....
....这样的效果如何实现哇?
A表id 主键 自增
name 用户名
B表
bid 主键 自增
id 用户id
wincount 赢的局数
lostcount 输的局数我想得出以下的效果用户名 游戏总局数张三 110局
李四 95局
王五 55局
....
....
....这样的效果如何实现哇?
group by a.id,a.name
from a
left join
(select id,sum(wincount+lostcount) as cnt from b group by id)
on a.id=b.id
from a
left join
(select id,sum(wincount+lostcount) as cnt from b group by id) as b
on a.id=b.id
from b t2 inner join a t1 on t1.id = t2.id --这个关联条件对吗?
group by t1.id,t1.name--ps:如果在b表中,对于用户是唯一的话,就不用group by了
--declare @tb table(id int ,tmid int ,time datetime)
--insert into @tb
--select 1 , 11 , '2001' union all
--select 2 , 12, '2002' union all
--select 3, 13, '2003' union all
--select 4, 14,'2004' union all
--select 5, 15, '2005' union all
--select 5, 16,'2006' union all
--select 5 , 17, '2007'
--
--declare @tb2 table (id int, tmid int ,state int)
--insert into @tb2
--select 1 , 11 , 1 union all
--select 2, 12, 1 union all
--select 3, 13 , 2 union all
--select 4, 14, 2 union all
--select 4, 16, 2 union all
--select 4, 17, 2
--
--select * from @tb a where not exists (select * from @tb2 b where a.tmid = b.tmid)
--
--select * from @tb a ,@tb2 b where a.tmid = b.tmid and b.state = 1declare @a table (id int identity(1,1) ,name varchar(10))
insert into @a(name)
select '张三' union all
select '李四' union all
select '王五'declare @b table (bid int identity(1,1),
id int,
wincount int ,
lostcount int)insert into @b (id,wincount,lostcount)
select 1,20,50 union all
select 2,10,30 union all
select 3,40,10 select a.name,sum((b.winc
选择列表中的列 'LFDSDB.dbo.AccountsInfo.UserID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。正确的。
select a.UserID,a.[Accounts],sum(b.WinCount+b.LostCount+b.DrawCount+b.FleeCount) as gamecount from [LFDSDB].[dbo].[AccountsInfo] as a left join QPTreasureDB.dbo.GameScoreInfo as b on a.UserID=b.UserID group by a.UserID,a.[Accounts]
这句话不是很明白 因为该列没有包含在聚合函数或 GROUP BY 子句中。
SQL> select * from a
2 ; ID NAME
---------- ---------
1 张三
2 李四SQL> select * from b
2 ; ID WINCOUNT LOSTCOUNT
---------- ---------- ----------
1 12 23
2 32 12
1 select name 用户名,sum(wincount+lostcount) 游戏总局数
2 FROM a,b
3 where a.id=b.id
4* group by name
SQL> /用户名 游戏总局数
--------- ----------
李四 44
张三 35SQL>
select a.UserID,a.[Accounts],
(b.WinCount+b.LostCount+b.DrawCount+b.FleeCount) as gamecount
from [LFDSDB].[dbo].[AccountsInfo] as a
left join QPTreasureDB.dbo.GameScoreInfo as b on a.UserID=b.UserID
from b t2 inner join a t1 on t1.id = t2.id
group by t1.id,t1.name