双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

SQL 多表查询问题求大神指点

2013-11-21 07:33:19 224 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

森广墨衍会员 · AI 创作全网分发关注 + 加入社区

银河麒麟V10开机卡Logo？别慌，手把手教你进单用户模式修复（附详细命令）本文详细解析银河麒麟V10操作系统启动时卡Logo的故障原因，并提供进入单用户模式的完整解决方案。通过GRUB引导菜单修改启动参数，执行文件系统检查、图形服务修复等关键命令，帮助用户快速恢复系统正常运行，适用于运维人员和普通用户应对紧急启动问题。阅读详情

例如有一个入库表，出库表，销售表，
入库表
ID 品名入库数量入库时间
1 矿泉水 100 2013-01-02
2 方便面 60 2013-01-03
3 方便面 50 2013-01-03
4 矿泉水 80 2013-01-04
5 方便面 50 2013-01-05
销售表
ID 品名销售数量销售时间
1 矿泉水 5 2013-01-03
2 矿泉水 10 2013-01-04
出库表

ID 品名出库数量出库时间
1 方便面 50 2013-01-03
2 矿泉水 80 2013-01-04

然后我要得到的信息是2013年1月五号之前的商品在库数量

品名在库数量
矿泉水 85
方便面 60

...全文

Python读取三菱PLC数据 3、支持自定义的数据读写操作。直接调用Read方法，可以读取到原生的byte数组，然后进行组合数据。1、每次的数据交互您都可以判断是否成功，如果您不判断，如果网络断开或是异常，会影响程序的执行。Qna3E协议的ascii类 MelsecMcAsciiNet。A兼容1E协议的二进制类 MelsecA1ENet。Qna3E协议的二进制类 MelsecMcNet。2、读写操作支持单个数和数组。阅读详情

森广

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

LongRui888 2013-11-21

打赏
举报



if object_id('[入库表]') is not null drop table [入库表]
go 

create table [入库表]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [入库表]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'


if object_id('[销售表]') is not null drop table [销售表]
go 
create table [销售表]([ID] int,[品名] varchar(6),[销售数量] int,[销售时间] datetime)
insert [销售表]
select 1,'矿泉水',5,'2013-01-03' union all
select 2,'矿泉水',10,'2013-01-04'


if object_id('[出库表]') is not null drop table [出库表]
go 
create table [出库表]([ID] int,[品名] varchar(6),[出库数量] int,[出库时间] datetime)
insert [出库表]
select 1,'方便面',50,'2013-01-03' union all
select 2,'矿泉水',80,'2013-01-04'
go


DECLARE @d DATETIME
SET @d='2013-01-05'--统计日期


select a.品名,ISNULL(a.v,0) - ISNULL(b.v,0) - ISNULL(c.v,0) 在库数量
from 
(
	select [品名],SUM([入库数量]) as v
	from [入库表] 
	where 入库时间 < @d
	group by [品名]
)a
left join 
(
select [品名],SUM([销售数量]) as v
from [销售表] 
where 销售时间 < @d
group by [品名]
)b
  on a.品名 = b.品名
left join 
(
	select [品名],SUM([出库数量]) as v
	from [出库表] 
	where 出库时间 < @d
	group by [品名]
)c
  on a.品名 = c.品名
/*
品名	    在库数量
方便面	60
矿泉水	85
*/

秃驴子 2013-11-21

打赏
举报

select a.品名,a.数量-b.数量 from(
select 品名,SUM(入库数量)'数量'
from [入库表]
where convert(varchar(10),入库时间,120) < '2013-01-05'
group by 品名)a,
(
select 品名, SUM(数量)'数量' from
(
select 品名,SUM(销售数量)'数量'
from [销售表]
where convert(varchar(10),销售时间,120) < '2013-01-05'
group by 品名
union all
select 品名,SUM(出库数量)'数量'
from [出库表]
where convert(varchar(10),出库时间,120) <'2013-01-05'
group by 品名
)a
group by a.品名
)b
where a.品名=b.品名

唐诗三百首 2013-11-21

打赏
举报

引用 5 楼 TravyLee 的回复:

----------------------------------------------------------------
-- Author  :TravyLee(走自己的路，让狗去叫吧！)
-- Date    :2013-11-21 09:38:30
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：#rkb
if object_id('tempdb.dbo.#rkb') is not null drop table #rkb
go 
create table #rkb([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert #rkb
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
--> 测试数据：#xsb
if object_id('tempdb.dbo.#xsb') is not null drop table #xsb
go 
create table #xsb([ID] int,[品名] varchar(6),[销售数量] int,[销售时间] datetime)
insert #xsb
select 1,'矿泉水',5,'2013-01-03' union all
select 2,'矿泉水',10,'2013-01-04'
--> 测试数据：#xsb
if object_id('tempdb.dbo.#ckb') is not null drop table #ckb
go 
create table #ckb([ID] int,[品名] varchar(6),[出库数量] int,[出库时间] datetime)
insert #ckb
select 1,'方便面',50,'2013-01-03' union all
select 2,'矿泉水',80,'2013-01-04'
go

;with t
as
(
select 
	[品名],SUM([入库数量]) as [入库数量] from #rkb 
		where [入库时间]<='2013-01-04'
	group by [品名]
),
m as
(
select 
	[品名],SUM([销售数量]) as [销售数量] from #xsb
		where [销售时间]<='2013-01-04'
	group by [品名]
),n
as
(
select 
	[品名],SUM([出库数量]) as [出库数量] from #ckb
		where [出库时间]<='2013-01-04'
	group by [品名] 
)
select
	t.[品名],t.入库数量-isnull(m.销售数量,0)-isnull(n.出库数量,0) from t
	left join m on t.品名=m.品名
	left join n on t.品名=n.品名

学习了.

开着拖拉机泡妞 2013-11-21

打赏
举报

----------------------------------------------------------------
-- Author  :TravyLee(走自己的路，让狗去叫吧！)
-- Date    :2013-11-21 09:38:30
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：#rkb
if object_id('tempdb.dbo.#rkb') is not null drop table #rkb
go 
create table #rkb([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert #rkb
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
--> 测试数据：#xsb
if object_id('tempdb.dbo.#xsb') is not null drop table #xsb
go 
create table #xsb([ID] int,[品名] varchar(6),[销售数量] int,[销售时间] datetime)
insert #xsb
select 1,'矿泉水',5,'2013-01-03' union all
select 2,'矿泉水',10,'2013-01-04'
--> 测试数据：#xsb
if object_id('tempdb.dbo.#ckb') is not null drop table #ckb
go 
create table #ckb([ID] int,[品名] varchar(6),[出库数量] int,[出库时间] datetime)
insert #ckb
select 1,'方便面',50,'2013-01-03' union all
select 2,'矿泉水',80,'2013-01-04'
go

;with t
as
(
select 
	[品名],SUM([入库数量]) as [入库数量] from #rkb 
		where [入库时间]<='2013-01-04'
	group by [品名]
),
m as
(
select 
	[品名],SUM([销售数量]) as [销售数量] from #xsb
		where [销售时间]<='2013-01-04'
	group by [品名]
),n
as
(
select 
	[品名],SUM([出库数量]) as [出库数量] from #ckb
		where [出库时间]<='2013-01-04'
	group by [品名] 
)
select
	t.[品名],t.入库数量-isnull(m.销售数量,0)-isnull(n.出库数量,0) from t
	left join m on t.品名=m.品名
	left join n on t.品名=n.品名

开着拖拉机泡妞 2013-11-21

打赏
举报

这个思路很明显嘛，可以现根据时间汇总，然后再算库存，也可以按照楼上的方法

Yole 2013-11-21

打赏
举报

典型的进销存查库存~不过我建议你还是应该有个库存的表，要是查某一天（x）的库存，用现在库存减去x到今天的采购数量，加上x到今天的销售数量。即反着推算。否则，随着时间的推移，你的进销数据会越来越大，还是用总采购-总销售的算法查库存，查询会越来越慢的。

發糞塗牆 2013-11-21

打赏
举报

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-21 07:47:38
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[入库表]
if object_id('[入库表]') is not null drop table [入库表]
go 
create table [入库表]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert [入库表]
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
--> 测试数据：[销售表]
if object_id('[销售表]') is not null drop table [销售表]
go 
create table [销售表]([ID] int,[品名] varchar(6),[销售数量] int,[销售时间] datetime)
insert [销售表]
select 1,'矿泉水',5,'2013-01-03' union all
select 2,'矿泉水',10,'2013-01-04'
--> 测试数据：[出库表]
if object_id('[出库表]') is not null drop table [出库表]
go 
create table [出库表]([ID] int,[品名] varchar(6),[出库数量] int,[出库时间] datetime)
insert [出库表]
select 1,'方便面',50,'2013-01-03' union all
select 2,'矿泉水',80,'2013-01-04'
--------------开始查询--------------------------
DECLARE @d DATETIME
SET @d='2013-01-05'--统计日期
SELECT [品名],SUM([入库数量])在库数量
FROM(
select [品名],SUM([入库数量])[入库数量] from [入库表]
WHERE [入库时间]<@d
GROUP BY [品名]
UNION ALL 
select [品名],-1*SUM([销售数量])[入库数量] from [销售表]
WHERE [销售时间] <@d
GROUP BY [品名]
UNION ALL 
select [品名],-1*SUM([出库数量])[入库数量] from [出库表]
WHERE [出库时间] <@d
GROUP BY [品名]
)a
GROUP BY [品名]
----------------结果----------------------------
/* 
品名     在库数量
------ -----------
方便面    60
矿泉水    85
*/

森广 2013-11-21

打赏
举报

别让我石沉大海。感激不尽喽。

看不见人的小屋 2013-11-21

打赏
举报

if object_id('ins') is not null drop table ins
go 
create table ins([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)
insert ins
select 1,'矿泉水',100,'2013-01-02' union all
select 2,'方便面',60,'2013-01-03' union all
select 3,'方便面',50,'2013-01-03' union all
select 4,'矿泉水',80,'2013-01-04' union all
select 5,'方便面',50,'2013-01-05'
go
if object_id('sales') is not null drop table sales
go 
create table sales([ID] int,[品名] varchar(6),[销售数量] int,[销售时间] datetime)
insert sales
select 1,'矿泉水',5,'2013-01-03' union all
select 2,'矿泉水',10,'2013-01-04'
go
if object_id('chu') is not null drop table chu
go 
create table chu([ID] int,[品名] varchar(6),[出库数量] int,[出库时间] datetime)
insert chu
select 1,'方便面',50,'2013-01-03' union all
select 2,'矿泉水',80,'2013-01-04'
go
SELECT a.[品名],(ISNULL(a.[入库数量],0)-ISNULL(b.[销售数量],0)-ISNULL(c.[出库数量],0)) FROM (
SELECT SUM([入库数量]) AS [入库数量],[品名] FROM ins
WHERE CONVERT(CHAR(10),[入库时间],120) < '2013-01-05'
GROUP BY [品名]) AS a
LEFT JOIN (
SELECT SUM([销售数量]) AS [销售数量],[品名] FROM sales
WHERE CONVERT(CHAR(10),[销售时间],120) < '2013-01-05'
GROUP BY [品名]) AS b
ON a.[品名] = b.[品名]
LEFT JOIN (
SELECT SUM([出库数量]) AS [出库数量],[品名] FROM chu
WHERE CONVERT(CHAR(10),[出库时间],120) < '2013-01-05'
GROUP BY [品名]) c
ON a.[品名]= c.[品名]
ORDER BY a.[品名] DESC

✦双11狂欢大促✦

SQL 多表查询问题求大神指点

相关推荐

双11狂欢大促