T1:(FID +FEntryID 唯一)FID FEntryID FDetailID
11 1 16
11 2 20T2:(FDetailID 唯一)
FDetailID FCount F2 F8 F2033
16 0 0 207 0
20 1 0 0 517T3:(FInterId +FClassID 唯一)
FInterId FClassID FNumber
207 8 ABC
207 11 EEE
517 2033 UUU要求结果
FID FEntryID FDetailID FClassID FNumber
11 1 16 8 ABC
11 2 20 2033 UUU结果中FClassID就取T2的从第2列开始列名的数字(除去其中的F,但不一定第四列就是F8,但数字肯定是有小到大),FNumber取在T3中FInterId, FClassID(如FInterId=207,FClassID =8的值是ABC)符合条件的FNumber值。
11 1 16
11 2 20T2:(FDetailID 唯一)
FDetailID FCount F2 F8 F2033
16 0 0 207 0
20 1 0 0 517T3:(FInterId +FClassID 唯一)
FInterId FClassID FNumber
207 8 ABC
207 11 EEE
517 2033 UUU要求结果
FID FEntryID FDetailID FClassID FNumber
11 1 16 8 ABC
11 2 20 2033 UUU结果中FClassID就取T2的从第2列开始列名的数字(除去其中的F,但不一定第四列就是F8,但数字肯定是有小到大),FNumber取在T3中FInterId, FClassID(如FInterId=207,FClassID =8的值是ABC)符合条件的FNumber值。
IF OBJECT_ID('[T1]') IS NOT NULL
DROP TABLE [T1]
GO
CREATE TABLE [T1] ([FID] [int],[FEntryID] [int],[FDetailID] [int])
INSERT INTO [T1]
SELECT '11','1','16' UNION ALL
SELECT '11','2','20'--> 生成测试数据表: [T2]
IF OBJECT_ID('[T2]') IS NOT NULL
DROP TABLE [T2]
GO
CREATE TABLE [T2] ([FDetailID] [int],[FCount] [int],[F2] [int],[F8] [int],[F2033] [int])
INSERT INTO [T2]
SELECT '16','0','0','207','0' UNION ALL
SELECT '20','1','0','0','517'--> 生成测试数据表: [T3]
IF OBJECT_ID('[T3]') IS NOT NULL
DROP TABLE [T3]
GO
CREATE TABLE [T3] ([FInterId] [int],[FClassID] [int],[FNumber] [nvarchar](10))
INSERT INTO [T3]
SELECT '207','8','ABC' UNION ALL
SELECT '207','11','EEE' UNION ALL
SELECT '517','2033','UUU'-->SQL查询如下:
SELECT A.*,C.*
FROM T1 A
JOIN T2 B ON A.FDETAILID=B.FDETAILID
CROSS APPLY(SELECT TOP 1 [FCLASSID],FNUMBER FROM T3 WHERE ISNULL(NULLIF(B.[F8],0),B.F2033)=FINTERID ORDER BY FCLASSID) C
/*
FID FEntryID FDetailID FCLASSID FNUMBER
----------- ----------- ----------- ----------- ----------
11 1 16 8 ABC
11 2 20 2033 UUU(2 行受影响)
*/