with a (class) as (select 1 union all select 2 union all select 3), b(class_id, sx, b_u) as (select 1, 1, 1 union all select 1, 2 ,1 union all select 2,4,2 union all select 2,7 ,2), c([user] ,ud) as (select '小明', 1 union all select '小白', 2) select class,ud,[USER],SUM(sx) from a left join B on a.class=b.class_id left join c on b.b_u=c.ud group by class,ud,[user]
select a.class,c.ud,c.user,SUM(b.sx) from 表a a left jion 表b b on a.class=b.class_id inner join 表c c on b.b_u=c.ud group by a.class,c.ud,c.user
IF OBJECT_ID(N'A') IS NOT NULL DROP TABLE A GO CREATE TABLE A(class INT ) INSERT INTO dbo.A SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3IF OBJECT_ID(N'B') IS NOT NULL DROP TABLE B GO CREATE TABLE B(class_id INT,sx INT,b_u INT) INSERT INTO B SELECT 1 ,1 ,1 UNION ALL SELECT 1,2,1 UNION ALL SELECT 2,4,2 UNION ALL SELECT 2,7,2
IF OBJECT_ID(N'C') IS NOT NULL DROP TABLE C GO CREATE TABLE C([user] varchar(10),ud INT) INSERT INTO C SELECT '小明',1 UNION ALL SELECT '小白',2
GO --------------------------------------------------------------------------查询------------------------------------------------------------------------------------SELECT a.class,isnull(CONVERT(VARCHAR,ud),'') ud,isnull(CONVERT(VARCHAR,[user]),'') [user], isnull(CONVERT(VARCHAR,SUM(sx)),'') [sum(sx)] FROM A a LEFT JOIN (SELECT * FROM B,C WHERE c.ud=b.b_u) b ON a.class=b.class_id GROUP BY a.class,ud,[user]
--------------------------------------------------------------------------结果------------------------------------------------------------------------------------ /* class ud user sum(sx) ----------- ------------------------------ ------------------------------ ------------------------------ 1 1 小明 3 2 2 小白 11 3 */
with a (class) as
(select 1 union all select 2 union all select 3),
b(class_id, sx, b_u) as
(select 1, 1, 1 union all select 1, 2 ,1 union all select 2,4,2 union all select 2,7 ,2),
c([user] ,ud) as
(select '小明', 1 union all select '小白', 2)
select class,ud,[USER],SUM(sx) from a left join B on a.class=b.class_id
left join c on b.b_u=c.ud group by class,ud,[user]
select a.class,c.ud,c.user,SUM(b.sx)
from 表a a
left jion 表b b
on a.class=b.class_id
inner join 表c c
on b.b_u=c.ud
group by a.class,c.ud,c.user
IF OBJECT_ID(N'A') IS NOT NULL
DROP TABLE A
GO
CREATE TABLE A(class INT )
INSERT INTO dbo.A SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3IF OBJECT_ID(N'B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE B(class_id INT,sx INT,b_u INT)
INSERT INTO B SELECT 1 ,1 ,1
UNION ALL SELECT 1,2,1
UNION ALL SELECT 2,4,2
UNION ALL SELECT 2,7,2
IF OBJECT_ID(N'C') IS NOT NULL
DROP TABLE C
GO
CREATE TABLE C([user] varchar(10),ud INT)
INSERT INTO C SELECT '小明',1
UNION ALL SELECT '小白',2
GO
--------------------------------------------------------------------------查询------------------------------------------------------------------------------------SELECT a.class,isnull(CONVERT(VARCHAR,ud),'') ud,isnull(CONVERT(VARCHAR,[user]),'') [user], isnull(CONVERT(VARCHAR,SUM(sx)),'') [sum(sx)] FROM A a LEFT JOIN
(SELECT * FROM B,C WHERE c.ud=b.b_u) b ON a.class=b.class_id GROUP BY a.class,ud,[user]
--------------------------------------------------------------------------结果------------------------------------------------------------------------------------
/*
class ud user sum(sx)
----------- ------------------------------ ------------------------------ ------------------------------
1 1 小明 3
2 2 小白 11
3
*/