SELECT OrderID, SUM(CASE WHEN T2.Process=M.Process THEN 1 ELSE 0 END) AS ProcessCount
FROM T2
CROSS APPLY (SELECT TOP 1 Process FROM T1 WHERE OrderID=T2.OrderID ORDER BY No DESC) M
GROUP BY T2.OrderID
FROM T2
CROSS APPLY (SELECT TOP 1 Process FROM T1 WHERE OrderID=T2.OrderID ORDER BY No DESC) M
GROUP BY T2.OrderID
表2: ID OrderID sn Process Line .....
1 A 12312 1 1
1 A 12312 5 1
1 A 12312 8 1
1 A 12313 8 2
1 A 12344 3 1
1 B 12344 7 2
1 B 12345 7 3
.......
这样要怎么搞了
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-28 09:26:52
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[OrderID] varchar(1),[No] int,[Process] int)
insert [a]
select 1,'A',1,1 union all
select 1,'A',2,5 union all
select 1,'A',3,8 union all
select 1,'B',1,3 union all
select 1,'B',2,7
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[OrderID] varchar(1),[sn] int,[Process] int)
insert [b]
select 1,'A',12312,1 union all
select 1,'A',12312,5 union all
select 1,'A',12312,8 union all
select 1,'A',12313,8 union all
select 1,'A',12344,3 union all
select 1,'B',12344,7 union all
select 1,'B',12345,7
--------------开始查询--------------------------
SELECT
DISTINCT b.OrderID,b.Process
FROM
a
INNER JOIN b ON a.OrderID=b.OrderID
WHERE
NOT EXISTS ( SELECT
1
FROM
b t
WHERE
OrderID=b.OrderID AND sn>b.sn )
----------------结果----------------------------
/* OrderID Process
------- -----------
A 3
B 7(2 行受影响)
*/
OrderID Line Process Count
A 1 8 1
A 2 8 1
B 2 7 1
B 3 7 1
FROM T2
CROSS APPLY (SELECT TOP 1 Process FROM T1 WHERE OrderID=T2.OrderID ORDER BY No DESC) M
GROUP BY T2.OrderID, T2.Line, M.Process
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-28 09:46:31
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] int,[OrderID] varchar(1),[No] int,[Process] int)
insert [a]
select 1,'A',1,1 union all
select 1,'A',2,5 union all
select 1,'A',3,8 union all
select 1,'B',1,3 union all
select 1,'B',2,7
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([ID] int,[OrderID] varchar(1),[sn] int,[Process] int,[Line] int)
insert [b]
select 1,'A',12312,1,1 union all
select 1,'A',12312,5,1 union all
select 1,'A',12312,8,1 union all
select 1,'A',12313,8,2 union all
select 1,'A',12344,3,1 union all
select 1,'B',12344,7,2 union all
select 1,'B',12345,7,3
--------------开始查询--------------------------
SELECT
a.OrderID,b.Line,a.Process,COUNT(1) AS [count]
FROM
a
INNER JOIN b ON a.OrderID=b.OrderID AND a.Process=b.Process
WHERE
NOT EXISTS ( SELECT
1
FROM
a t
WHERE
OrderID=a.OrderID AND Process>a.Process )
GROUP BY
a.OrderID,b.Line,a.Process
----------------结果----------------------------
/* OrderID Line Process count
------- ----------- ----------- -----------
A 1 8 1
A 2 8 1
B 2 7 1
B 3 7 1(4 行受影响)
*/
重新描述完整需求。