select * from table1 t1 inner join table2 t2 On t1.BrandID=t2.BrandID inner join Table3 t3 On t1.BrandID=t3.BrandID
但如果我想按三个表中的两个字段分类汇总后在组合呢?例如: select BrandID,UserID,Sum(Money) from Table1 group by BrandID,UserIDselect BrandID,UserID,Sum(SaleMoney) from Table2 group by BrandID,UserIDselect BrandID,UserID,Sum(BackMoney) from Table3 group by BrandID,UserID想合成一张表,有BrandID,UserID,Sum(1),sum(2),sum(3) 三个汇总字段?
我想问: funboy88(司令) inner join 和 Left join 有什么不同?
select select BrandID,UserID,Sum(Money),(select Sum(SaleMoney) from Table2 where Table2.BrandID=BrandID and Table2.UserID=UserID),(select Sum(BackMoney) from Table3 where Table3.BrandID=BrandID and Table3.UserID=UserID) from Table1 group by BrandID,UserID
1, left join---->left outer join左表全部行有 如要取全部相同部分,改用INNER JOIN 2, select t1.BrandID,t1.UserID,t1.[sum(1)],t2.[sum(2)],t3.[sum(3)] from (select BrandID,UserID,Sum(Money) as 'sum(1)' from Table1 group by BrandID,UserID) t1 inner join (select BrandID,UserID,Sum(SaleMoney) as 'sum(2)' from Table2 group by BrandID,UserID) t2 on t1.BrandID=t2.BrandID and t1.UserID=t2.UserID inner join (select BrandID,UserID,Sum(BackMoney) as 'sum(3)' from Table3 group by BrandID,UserID) t3 on t1.BrandID=t3.BrandID and t1.UserID=t3.UserID
2,其实,don't need use joinselect BrandID,UserID,Sum(Money) as 'sum(1)',(select Sum(SaleMoney) from Table2 where BrandID=t1.BrandID and UserID=t1.UserID) as 'sum(2)',(select Sum(BackMoney) from Table3 where BrandID=t1.BrandID and UserID=t1.UserID) as 'sum(3)' from Table1 t1 group by BrandID,UserID
from
table1 t1
inner join
table2 t2
On t1.BrandID=t2.BrandID
inner join
Table3 t3
On t1.BrandID=t3.BrandID
select BrandID,UserID,Sum(Money)
from Table1
group by BrandID,UserIDselect BrandID,UserID,Sum(SaleMoney)
from Table2
group by BrandID,UserIDselect BrandID,UserID,Sum(BackMoney)
from Table3
group by BrandID,UserID想合成一张表,有BrandID,UserID,Sum(1),sum(2),sum(3)
三个汇总字段?
from Table1
group by BrandID,UserID
如要取全部相同部分,改用INNER JOIN
2,
select t1.BrandID,t1.UserID,t1.[sum(1)],t2.[sum(2)],t3.[sum(3)] from
(select BrandID,UserID,Sum(Money) as 'sum(1)'
from Table1
group by BrandID,UserID) t1 inner join
(select BrandID,UserID,Sum(SaleMoney) as 'sum(2)'
from Table2
group by BrandID,UserID) t2 on t1.BrandID=t2.BrandID and t1.UserID=t2.UserID
inner join
(select BrandID,UserID,Sum(BackMoney) as 'sum(3)'
from Table3
group by BrandID,UserID) t3 on t1.BrandID=t3.BrandID and t1.UserID=t3.UserID
from Table1 t1
group by BrandID,UserID