任务表和样品表关联通过GUID和RGUID,state为10表示交接完成,每个任务所有样品交接完成后,取日期最新的一条作为交接日期
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[任务表]') AND type in (N'U'))
DROP TABLE [dbo].[任务表]
GO
CREATE TABLE [dbo].[任务表](
[guid] [varchar](36) NULL,
[reportno] [varchar](20) NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[样品表]') AND type in (N'U'))
DROP TABLE [dbo].[样品表]
GO
CREATE TABLE [dbo].[样品表](
[guid] [varchar](36) NULL,
[rguid] [varchar](36) NULL,
[STATE] [int] NULL,
[creattime] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.任务表
SELECT '7D3493D2-C043-4101-8673-04C341540588' AS guid,'20141001' AS reportno
UNION
SELECT 'EE78735D-7181-4E65-8D54-3934AD0064BE' AS guid,'20141002' AS reportno
UNION
SELECT 'D1E90684-B6F3-456C-9713-B290302DDB96' AS guid,'20141003' AS reportno
GO
select * from 任务表
-------------------------------------------------------------------------------------------------------------------------
guid reportno
7D3493D2-C043-4101-8673-04C341540588 20141001
D1E90684-B6F3-456C-9713-B290302DDB96 20141003
EE78735D-7181-4E65-8D54-3934AD0064BE 20141002
-------------------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.样品表
SELECT NEWID() AS guid,'7D3493D2-C043-4101-8673-04C341540588' AS rguid,10 AS STATE,'2013-1-4 10:33:38' AS [creattime]
UNION
SELECT NEWID() AS guid,'7D3493D2-C043-4101-8673-04C341540588' AS rguid,10 AS STATE,'2013-1-5 14:02:20' AS [creattime]
UNION
SELECT NEWID() AS guid,'EE78735D-7181-4E65-8D54-3934AD0064BE' AS rguid,10 AS STATE,'2013-1-4 14:46:51' AS [creattime]
UNION
SELECT NEWID() AS guid,'EE78735D-7181-4E65-8D54-3934AD0064BE' AS rguid,5 AS STATE,'2013-1-5 14:56:34' AS [creattime]
UNION
SELECT NEWID() AS guid,'D1E90684-B6F3-456C-9713-B290302DDB96' AS rguid,10 AS STATE,'2013-1-6 15:00:33' AS [creattime]
UNION
SELECT NEWID() AS guid,'D1E90684-B6F3-456C-9713-B290302DDB96' AS rguid,10 AS STATE,'2013-1-7 09:31:30' AS [creattime]
GO
SELECT * FROM 样品表
GO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
guid rguid STATE creattime
D84AF172-9AA4-4586-AE5D-0F5A318EFA70 7D3493D2-C043-4101-8673-04C341540588 10 2013-01-05 14:02:20.000
56EAE370-412B-4710-BAF2-5B59B45F789D EE78735D-7181-4E65-8D54-3934AD0064BE 5 2013-01-05 14:56:34.000
C09E2AA5-A453-4BAB-B7E1-7F111F4166FE 7D3493D2-C043-4101-8673-04C341540588 10 2013-01-04 10:33:38.000
CE23F6DD-27E0-4B9F-860F-9CC79E880CFF EE78735D-7181-4E65-8D54-3934AD0064BE 10 2013-01-04 14:46:51.000
46BFBA01-A139-40DA-A379-D706BCFFBB2C D1E90684-B6F3-456C-9713-B290302DDB96 10 2013-01-06 15:00:33.000
5F859AB6-6246-44AE-94AE-F258F971F226 D1E90684-B6F3-456C-9713-B290302DDB96 10 2013-01-07 09:31:30.000
----------------------------------------------------------------------------------------------------------------------------------------------------------
想得到的查询结果
------------------------------------------------------------------------------------------------------------------------------------------------------------
guid reportno settime
7D3493D2-C043-4101-8673-04C341540588 20141001 2013-01-05 14:02:20.000
D1E90684-B6F3-456C-9713-B290302DDB96 20141003 2013-01-07 09:31:30.000
------------------------------------------------------------------------------------------------------------------------------------------------------------
这个查询如何写
------解决思路----------------------
select r.guid,r.reportno,y.settime
from (select rguid,MAX(createtime) as settime from 样品表 group by rguid having MIN(state)=10) y
left JOIN 任务表 r ON y.rguid = r.guid