使用语句得出一段时间内所有出库情况
select id,name,num,date from a
id name num date
1 aa 2 2009-1-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 3 2009-4-2另一条语句得出一段时间内满足条件的出库情况
id name num date
1 aa 1 2009-2-1
2 bb 2 2009-4-8如何写sql语句得出下面我要的结果
id name num date
1 aa 1 2009-2-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 1 2009-4-8
select id,name,num,date from a
id name num date
1 aa 2 2009-1-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 3 2009-4-2另一条语句得出一段时间内满足条件的出库情况
id name num date
1 aa 1 2009-2-1
2 bb 2 2009-4-8如何写sql语句得出下面我要的结果
id name num date
1 aa 1 2009-2-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 1 2009-4-8
是否是bb 2 ?
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试。
(
select id,name,num,date from a
union all
select id,name,num,date from b
)
select * from f t where date=(select max(date) from f where id=t.id and name=t.name)
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-12 16:36:03
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int,[name] varchar(2),[num] int,[date] datetime)
insert [a]
select 1,'aa',2,'2009-1-1' union all
select 1,'aa',3,'2009-3-1' union all
select 1,'aa',2,'2009-3-4' union all
select 2,'bb',3,'2009-4-2'
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[name] varchar(2),[num] int,[date] datetime)
insert [b]
select 1,'aa',1,'2009-2-1' union all
select 2,'bb',2,'2009-4-8'
--------------开始查询--------------------------
;with f as
(
select id,name,num,date from a
union all
select id,name,num,date from b
)
select * from f t where date=(select max(date) from f where id=t.id and name=t.name and num=t.num) order by 4
----------------结果----------------------------
/* id name num date
----------- ---- ----------- -----------------------
1 aa 1 2009-02-01 00:00:00.000
1 aa 3 2009-03-01 00:00:00.000
1 aa 2 2009-03-04 00:00:00.000
2 bb 3 2009-04-02 00:00:00.000
2 bb 2 2009-04-08 00:00:00.000(5 行受影响)*/
aa、bb指的是名称,bb 1 是指出了1个bb配件语句A:select id,name,num,date from a 这句得出所有的出库配件
id name num date
1 aa 2 2009-1-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 3 2009-4-2 语句B:select id,name,num,date from b 这句得出满足条件的出库配件
id name num date
1 aa 1 2009-2-1
2 bb 2 2009-4-8 如何写sql语句得出下面我要的结果,即从A中减去B里面的数量,但是只减去一个,后2个同名的就不减
id name num date
1 aa 1 2009-2-1
1 aa 3 2009-3-1
1 aa 2 2009-3-4
2 bb 1 2009-4-8
go
create table [ta]([id] int,[name] varchar(2),[num] int,[date] datetime)
insert [ta]
select 1,'aa',2,'2009-1-1' union all
select 1,'aa',3,'2009-3-1' union all
select 1,'aa',2,'2009-3-4' union all
select 2,'bb',3,'2009-4-2'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(2),[num] int,[date] datetime)
insert [tb]
select 1,'aa',1,'2009-2-1' union all
select 2,'bb',2,'2009-4-8'
-->查询:
select a.id,a.name,
a.num-sum(isnull(b.num,0)) as num,
isnull(b.[date],a.[date]) as [date]
from ta a
left join tb b on a.name=b.name and a.[date]<=b.[date]
group by a.num,a.id,a.name,isnull(b.[date],a.[date])/**
id name num date
----------- ---- ----------- ------------------------------------------------------
1 aa 1 2009-02-01 00:00:00.000
1 aa 2 2009-03-04 00:00:00.000
1 aa 3 2009-03-01 00:00:00.000
2 bb 1 2009-04-08 00:00:00.000(所影响的行数为 4 行)
**/
2> case when (select sum(num) from b where name=t.name)<ISNULL ((select sum
(num) from a where name=t.name and date<t.date),0)
3> then num
4> else num-(select sum(num) from b where name=t.name)
5> end,date
6> from a t
7> go
id |name| |date
-----------|----|-----------|-----------------------
1|aa | 1|2009-01-01 00:00:00.000
1|aa | 3|2009-03-01 00:00:00.000
1|aa | 2|2009-03-04 00:00:00.000
2|bb | 1|2009-04-02 00:00:00.000(4 rows affected)
1>
create table A(id int,name varchar(2),num int,[date] varchar(12))
insert A
select 1,'aa',2,'2009-1-1' union all
select 1,'aa',3,'2009-3-1' union all
select 1,'aa',2,'2009-3-4' union all
select 2,'bb',3,'2009-4-2'create table B(id int,name varchar(2),num int,[date] varchar(12))
insert B
select 1,'aa',1,'2009-2-1' union all
select 2,'bb',2,'2009-4-8'select t.id, t.name, num=(case when t.rid=1 then t.num-B.num else t.num end), t.date
from (select *, rid=row_number() over (partition by id order by date) from A) t,B
where t.id=B.id
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[name] varchar(2),[num] int,[date] datetime)
insert [ta]
select 1,'aa',2,'2009-1-1' union all
select 1,'aa',3,'2009-3-1' union all
select 1,'aa',2,'2009-3-4' union all
select 2,'bb',3,'2009-4-2'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(2),[num] int)
insert [tb]
select 1,'aa',1,union all
select 2,'bb',2
语句B:select id,name,num from tb 这句得出满足条件的出库配件数量汇总,是没有时间的。
id name num
1 aa 1
2 bb 2
case when (select sum(num) from b where name=t.name)<ISNULL ((select sum(num) from a where name=t.name and date<t.date),0)
then num
else num-(select sum(num) from b where name=t.name)
end,date
from a t