mssql三表联合查询统计应该怎么写？

一转程序员 2018-12-16 09:12:09

比如一个仓库有三个表，物品列表，入库表，出库表物品表列为id,name,code,size 入库表列为id,pid,num 出库表列为id,code,num 现在想查询出所有物品的入库总数和出库总数，查询语句应该怎么写呢？

...全文

117 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

一转程序员 2018-12-16

打赏
举报

引用 2 楼二月十六的回复:

--测试数据

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([id] int,[name] nvarchar(23),[code] int,[size] int)

Insert #T1

select 1,N'物品1',1001,20 union all

select 2,N'物品2',1002,40

GO

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([id] int,[pid] int,[num] int)

Insert #T2

select 1,1,20 union all

select 2,1,30 union all

select 3,2,10

GO

if not object_id(N'Tempdb..#T3') is null

	drop table #T3

Go

Create table #T3([id] int,[code] int,[num] int)

Insert #T3

select 1,1001,10 union all

select 2,1002,20 union all

select 3,1002,30

Go

--测试数据结束

SELECT #T1.name,

       t2.num AS 入库数量,

       t3.num AS 出库数量

FROM #T1

    LEFT JOIN

    (SELECT pid, SUM(num) AS num FROM #T2 GROUP BY pid) t2

        ON t2.pid = #T1.id

    LEFT JOIN

    (SELECT code, SUM(num) AS num FROM #T3 GROUP BY code) t3

        ON t3.code = #T1.code;

完全正确，再次感谢

一转程序员 2018-12-16

打赏
举报

引用 2 楼二月十六的回复:

--测试数据

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([id] int,[name] nvarchar(23),[code] int,[size] int)

Insert #T1

select 1,N'物品1',1001,20 union all

select 2,N'物品2',1002,40

GO

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([id] int,[pid] int,[num] int)

Insert #T2

select 1,1,20 union all

select 2,1,30 union all

select 3,2,10

GO

if not object_id(N'Tempdb..#T3') is null

	drop table #T3

Go

Create table #T3([id] int,[code] int,[num] int)

Insert #T3

select 1,1001,10 union all

select 2,1002,20 union all

select 3,1002,30

Go

--测试数据结束

SELECT #T1.name,

       t2.num AS 入库数量,

       t3.num AS 出库数量

FROM #T1

    LEFT JOIN

    (SELECT pid, SUM(num) AS num FROM #T2 GROUP BY pid) t2

        ON t2.pid = #T1.id

    LEFT JOIN

    (SELECT code, SUM(num) AS num FROM #T3 GROUP BY code) t3

        ON t3.code = #T1.code;

就是这个意思，太感谢了！我试试去

二月十六版主 2018-12-16

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([id] int,[name] nvarchar(23),[code] int,[size] int)

Insert #T1

select 1,N'物品1',1001,20 union all

select 2,N'物品2',1002,40

GO

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([id] int,[pid] int,[num] int)

Insert #T2

select 1,1,20 union all

select 2,1,30 union all

select 3,2,10

GO

if not object_id(N'Tempdb..#T3') is null

	drop table #T3

Go

Create table #T3([id] int,[code] int,[num] int)

Insert #T3

select 1,1001,10 union all

select 2,1002,20 union all

select 3,1002,30

Go

--测试数据结束

SELECT #T1.name,

       t2.num AS 入库数量,

       t3.num AS 出库数量

FROM #T1

    LEFT JOIN

    (SELECT pid, SUM(num) AS num FROM #T2 GROUP BY pid) t2

        ON t2.pid = #T1.id

    LEFT JOIN

    (SELECT code, SUM(num) AS num FROM #T3 GROUP BY code) t3

        ON t3.code = #T1.code;

二月十六版主 2018-12-16

打赏
举报

关联是怎么关联的，通过pid和code？

一转程序员 2018-12-16

打赏
举报

引用 5 楼二月十六的回复:

[quote=引用 4 楼 hodrag 的回复:] [quote=引用 2 楼二月十六的回复:]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(23),[code] int,[size] int)
Insert #T1
select 1,N'物品1',1001,20 union all
select 2,N'物品2',1002,40
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[pid] int,[num] int)
Insert #T2
select 1,1,20 union all
select 2,1,30 union all
select 3,2,10
GO
if not object_id(N'Tempdb..#T3') is null
	drop table #T3
Go
Create table #T3([id] int,[code] int,[num] int)
Insert #T3
select 1,1001,10 union all
select 2,1002,20 union all
select 3,1002,30
Go
--测试数据结束
SELECT #T1.name,
       t2.num AS 入库数量,
       t3.num AS 出库数量
FROM #T1
    LEFT JOIN
    (SELECT pid, SUM(num) AS num FROM #T2 GROUP BY pid) t2
        ON t2.pid = #T1.id
    LEFT JOIN
    (SELECT code, SUM(num) AS num FROM #T3 GROUP BY code) t3
        ON t3.code = #T1.code;

完全正确，再次感谢

[/quote] 结贴啊兄弟[/quote] 手机上没找到结贴，我得找台电脑

二月十六版主 2018-12-16

打赏
举报

引用 4 楼 hodrag 的回复:

[quote=引用 2 楼二月十六的回复:]

--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(23),[code] int,[size] int)
Insert #T1
select 1,N'物品1',1001,20 union all
select 2,N'物品2',1002,40
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[pid] int,[num] int)
Insert #T2
select 1,1,20 union all
select 2,1,30 union all
select 3,2,10
GO
if not object_id(N'Tempdb..#T3') is null
	drop table #T3
Go
Create table #T3([id] int,[code] int,[num] int)
Insert #T3
select 1,1001,10 union all
select 2,1002,20 union all
select 3,1002,30
Go
--测试数据结束
SELECT #T1.name,
       t2.num AS 入库数量,
       t3.num AS 出库数量
FROM #T1
    LEFT JOIN
    (SELECT pid, SUM(num) AS num FROM #T2 GROUP BY pid) t2
        ON t2.pid = #T1.id
    LEFT JOIN
    (SELECT code, SUM(num) AS num FROM #T3 GROUP BY code) t3
        ON t3.code = #T1.code;

完全正确，再次感谢

[/quote] 结贴啊兄弟