table a请购单 请购行 其它信息
no1 1 ....
no1 2
table b请购单 请购行 日期 备注
no1 1 20090909 test1
no1 1 20090910 test2
no1 1 20090911 test3
no1 2 20090909 test
...如何查询 A表,并在A表后添加B表字段(B表中相同请购单和请购行,添加最大日期的那条)如
请购单 请购行 其它信息 日期 备注
no1 1 .... 20090911 test3
这样的结果
no1 1 ....
no1 2
table b请购单 请购行 日期 备注
no1 1 20090909 test1
no1 1 20090910 test2
no1 1 20090911 test3
no1 2 20090909 test
...如何查询 A表,并在A表后添加B表字段(B表中相同请购单和请购行,添加最大日期的那条)如
请购单 请购行 其它信息 日期 备注
no1 1 .... 20090911 test3
这样的结果
from tb b
where exists(select 1 from tb where 请购单 =b.请购单 and 请购行 = b.请购行 and 日期> b.日期)
WHERE tb1 AS A
LEFT JOIN tb2 AS B
ON A.请购单 = B.请购单 AND A.请购行 = B.请购行
WHERE NOT EXISTS(SELECT * FROM tb2
WHERE 请购单 = B.请购单 AND 请购行 = B.请购行
AND 日期 > B.日期);
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-14 10:30:35
-------------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (请购单 VARCHAR(3),请购行 INT,其它信息 VARCHAR(4))
INSERT INTO @tb1
SELECT 'no1',1,'....' UNION ALL
SELECT 'no1',2,'xxx'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (请购单 VARCHAR(3),请购行 INT,日期 DATETIME,备注 VARCHAR(5))
INSERT INTO @tb2
SELECT 'no1',1,'20090909','test1' UNION ALL
SELECT 'no1',1,'20090910','test2' UNION ALL
SELECT 'no1',1,'20090911','test3' UNION ALL
SELECT 'no1',2,'20090909','test'--SQL查询如下:SELECT A.*,B.日期,B.备注
FROM @tb1 AS A
LEFT JOIN @tb2 AS B
ON A.请购单 = B.请购单 AND A.请购行 = B.请购行
WHERE NOT EXISTS(SELECT * FROM @tb2
WHERE 请购单 = B.请购单 AND 请购行 = B.请购行
AND 日期 > B.日期);/*
请购单 请购行 其它信息 日期 备注
---- ----------- ---- ----------------------- -----
no1 1 .... 2009-09-11 00:00:00.000 test3
no1 2 xxx 2009-09-09 00:00:00.000 test(2 行受影响)*/
WHERE tb1 --> FROM tb1
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-14 10:34:32
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([请购单] varchar(3),[请购行] int,[其它信息] varchar(4))
insert [a]
select 'no1',1,'....' union all
select 'no1',2,null
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([请购单] varchar(3),[请购行] int,[日期] datetime,[备注] varchar(5))
insert [b]
select 'no1',1,'20090909','test1' union all
select 'no1',1,'20090910','test2' union all
select 'no1',1,'20090911','test3' union all
select 'no1',2,'20090909','test'
--------------开始查询--------------------------
select
a.*,b.日期,b.备注
from
a
left join
b
on
a.请购单 = b.请购单 and a.请购行 = b.请购行
where
not exists(select 1 from b where 请购单 =b.请购单 and 请购行 = b.请购行 and 日期> b.日期)----------------结果----------------------------
/* 请购单 请购行 其它信息 日期 备注
---- ----------- ---- ----------------------- -----
no1 1 .... 2009-09-09 00:00:00.000 test1
no1 1 .... 2009-09-10 00:00:00.000 test2
no1 1 .... 2009-09-11 00:00:00.000 test3
no1 2 NULL 2009-09-09 00:00:00.000 test(4 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-14 10:34:32
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([请购单] varchar(3),[请购行] int,[其它信息] varchar(4))
insert [a]
select 'no1',1,'....' union all
select 'no1',2,null
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([请购单] varchar(3),[请购行] int,[日期] datetime,[备注] varchar(5))
insert [b]
select 'no1',1,'20090909','test1' union all
select 'no1',1,'20090910','test2' union all
select 'no1',1,'20090911','test3' union all
select 'no1',2,'20090909','test'
--------------开始查询--------------------------
select
a.*,t.日期,t.备注
from
a
left join
b t
on
a.请购单 = t.请购单 and a.请购行 = t.请购行
where
not exists(select 1 from b where 请购单 =t.请购单 and 请购行 = t.请购行 and 日期> t.日期)----------------结果----------------------------
/* 请购单 请购行 其它信息 日期 备注
---- ----------- ---- ----------------------- -----
no1 1 .... 2009-09-11 00:00:00.000 test3
no1 2 NULL 2009-09-09 00:00:00.000 test(2 行受影响)*/
INSERT INTO @tb1
SELECT 'no1',1,'....' UNION ALL
SELECT 'no1',2,'xxx'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (请购单 VARCHAR(3),请购行 INT,日期 DATETIME,备注 VARCHAR(5))
INSERT INTO @tb2
SELECT 'no1',1,'20090909','test1' UNION ALL
SELECT 'no1',1,'20090910','test2' UNION ALL
SELECT 'no1',1,'20090911','test3' UNION ALL
SELECT 'no1',2,'20090909','test'select a.*,b.日期,b.备注 from @tb1 as a inner join @tb2 as b on a.请购单=b.请购单 and a.请购行=b.请购行 where
not exists(select * from @tb2 as tb where tb.请购单=b.请购单 and tb.请购行=b.请购行 and tb.日期>b.日期)
DECLARE @tb1 TABLE (请购单 VARCHAR(3),请购行 INT,其它信息 VARCHAR(4))
INSERT INTO @tb1
SELECT 'no1',1,'....' UNION ALL
SELECT 'no1',2,'xxx'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (请购单 VARCHAR(3),请购行 INT,日期 DATETIME,备注 VARCHAR(5))
INSERT INTO @tb2
SELECT 'no1',1,'20090909','test1' UNION ALL
SELECT 'no1',1,'20090910','test2' UNION ALL
SELECT 'no1',1,'20090911','test3' UNION ALL
SELECT 'no1',2,'20090909','test'select a.*,b.日期,b.备注 from @tb1 as a inner join @tb2 as b on a.请购单=b.请购单 and a.请购行=b.请购行 where
not exists(select * from @tb2 as tb where tb.请购单=b.请购单 and tb.请购行=b.请购行 and tb.日期>b.日期)