求一个最优查询语句，有点难度（附上脚本）
本帖最后由 maysoft 于 2012-12-10 15:22:15 编辑 表A（业务主表）：
ID 单号 起点
1 CY01 成都
2 CY02 郑州
3 CY03 乌鲁木齐
……
表B（业务明细表）：
id 表AID 产品 毛重 净重 方量 终点 业务时间
1 1 40001325 23.45 12.15 65.30 北京 2011-10-01
2 1 40001328 22.15 17.25 42.10 廊坊 2011-10-01
3 1 40002326 25.25 15.34 33.02 香河 2010-10-01
4 2 40001007 23.00 16.31 45.00 上海 2012-08-08
5 2 40001158 17.45 13.25 32.20 南京 2012-12-01
6 3 40001567 11.45 9.05 31.21 长春 2012-11-30
……
表C（价格表）：
id 产品 起点 终点 类型 价格 最小值 最大值 生效日期
1 40001325 成都 北京 方量 120.00 1 1000 2011-01-05
2 40001325 成都 北京 方量 130.00 1 1000 2011-08-05
3 40001325 成都 北京 方量 125.00 1001 2000 2011-01-05
4 40001328 成都 廊坊 毛重 90.00 1 1000 2011-01-05
5 40001328 成都 廊坊 毛重 88.00 1001 2000 2011-01-05
6 40002326 成都 香河 净重 100.00 1 2000 2011-01-05
7 40001007 郑州 上海 方量 120.00 1 2000 2011-01-05
8 40001158 郑州 南京 方量 115.00 1 2000 2011-01-05
……
需要计算出如下的结果：
表AID 单号 表BID 金额
1 CY01 1 8489.00 （130*65.30）
1 CY01 2 1993.50 （22.15*90）
1 CY01 3 0.00 （没找到匹配的价格）
2 CY02 4 5400.00 （45*120）
2 CY02 5 3703.00 （ 32.20*115）
3 CY03 6 0.00 （没找到匹配的价格）
需要注意的：
1：结果中的括号内内容不用算。我注释给大家的。
2：表C价格表，同一产品、同一线路、同一类型及区间（最大最小值一样），如果生效日期不一样，则价格有可能不一样。
3：计算金额时，需要根据表C中的生效日期是否是最新的（在业务时间之前），然后按指定类型计算（方量、净重、毛重）三种
4：目前数据量有点大，需要一个最快速的办法（基于现有结构）
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[表C] Script Date: 12/10/2012 15:12:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[表C](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[产品] [varchar](50) NULL,
[起点] [varchar](50) NULL,
[终点] [varchar](50) NULL,
[类型] [varchar](50) NULL,
[价格] [decimal](18, 2) NULL,
[最小值] [decimal](18, 2) NULL,
[最大值] [decimal](18, 2) NULL,
[生效日期] [datetime] NULL,
CONSTRAINT [PK_表C] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[表C] ON
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (1, N'40001325', N'成都', N'北京', N'方量', CAST(120.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (2, N'40001325', N'成都', N'北京', N'方量', CAST(130.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0x00009F3600000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (3, N'40001325', N'成都', N'北京', N'方量', CAST(125.00 AS Decimal(18, 2)), CAST(1001.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (4, N'40001328', N'成都', N'廊坊', N'毛重', CAST(90.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (5, N'40001328', N'成都', N'廊坊', N'毛重', CAST(88.00 AS Decimal(18, 2)), CAST(1001.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (6, N'40002326', N'成都', N'香河', N'净重', CAST(100.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (7, N'40001007', N'郑州', N'上海', N'方量', CAST(120.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
INSERT [dbo].[表C] ([id], [产品], [起点], [终点], [类型], [价格], [最小值], [最大值], [生效日期]) VALUES (8, N'40001158', N'郑州', N'南京', N'方量', CAST(115.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0x00009E6200000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[表C] OFF
/****** Object: Table [dbo].[表B] Script Date: 12/10/2012 15:12:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[表B](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[表AID] [bigint] NULL,
[产品] [varchar](50) NULL,
[毛重] [decimal](18, 2) NULL,
[净重] [decimal](18, 2) NULL,
[方量] [decimal](18, 2) NULL,
[终点] [varchar](50) NULL,
[业务时间] [datetime] NULL,
CONSTRAINT [PK_表B] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[表B] ON
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (1, 1, N'40001325', CAST(23.45 AS Decimal(18, 2)), CAST(12.15 AS Decimal(18, 2)), CAST(65.30 AS Decimal(18, 2)), N'北京', CAST(0x00009F6F00000000 AS DateTime))
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (2, 1, N'40001328', CAST(22.15 AS Decimal(18, 2)), CAST(17.25 AS Decimal(18, 2)), CAST(42.10 AS Decimal(18, 2)), N'廊坊', CAST(0x00009F6F00000000 AS DateTime))
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (3, 1, N'40002326', CAST(25.25 AS Decimal(18, 2)), CAST(15.34 AS Decimal(18, 2)), CAST(33.02 AS Decimal(18, 2)), N'香河', CAST(0x00009E0200000000 AS DateTime))
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (4, 2, N'40001007', CAST(23.00 AS Decimal(18, 2)), CAST(16.31 AS Decimal(18, 2)), CAST(45.00 AS Decimal(18, 2)), N'上海', CAST(0x0000A0A700000000 AS DateTime))
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (5, 2, N'40001158', CAST(17.45 AS Decimal(18, 2)), CAST(13.25 AS Decimal(18, 2)), CAST(32.20 AS Decimal(18, 2)), N'南京', CAST(0x0000A11A00000000 AS DateTime))
INSERT [dbo].[表B] ([id], [表AID], [产品], [毛重], [净重], [方量], [终点], [业务时间]) VALUES (6, 3, N'40001567', CAST(11.45 AS Decimal(18, 2)), CAST(9.05 AS Decimal(18, 2)), CAST(31.21 AS Decimal(18, 2)), N'长春', CAST(0x0000A11900000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[表B] OFF
/****** Object: Table [dbo].[表A] Script Date: 12/10/2012 15:12:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[表A](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[单号] [varchar](50) NULL,
[起点] [varchar](50) NULL,
CONSTRAINT [PK_表A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[表A] ON
INSERT [dbo].[表A] ([ID], [单号], [起点]) VALUES (1, N'CY01', N'成都')
INSERT [dbo].[表A] ([ID], [单号], [起点]) VALUES (2, N'CY02', N'郑州')
INSERT [dbo].[表A] ([ID], [单号], [起点]) VALUES (3, N'CY03', N'乌鲁木齐')
SET IDENTITY_INSERT [dbo].[表A] OFF
--参考
SELECT B.表aid,单号,B.id AS 表BID,ISNULL(SUM(CASE c.类型 WHEN '方量' THEN B.方量
WHEN '毛重' THEN B.毛重
WHEN '净重' THEN B.净重 END
*c.价格 ),0) AS 金额
FROM 表A A
right JOIN 表B B ON A.id = B.表aid
LEFT JOIN (SELECT 产品,起点,终点,类型,MAX(价格) AS 价格,MIN(生效日期) AS 生效日期 FROM 表c c GROUP BY 产品,起点,终点,类型)
AS c ON B.产品 = c.产品 AND B.业务时间 >=C.生效日期
GROUP BY B.表aid,单号,B.id
/*
1CY0118489.0000
1CY0121993.5000
1CY0130.0000
2CY0245400.0000
2CY0253703.0000
3CY0360.0000*/
SELECT B.表AID,
A.单号,
B.id AS '表BID',
CASE WHEN C.类型 = '方量' THEN ISNULL(方量,0) * ISNULL(C.价格,0)
WHEN C.类型 = '毛重' THEN ISNULL(毛重,0) * ISNULL(C.价格,0)
WHEN C.类型 = '净重' THEN ISNULL(净重,0) * ISNULL(C.价格,0)
WHEN ISNULL(C.类型,'')='' THEN 0 END AS '价格'
FROM [表A] A
RIGHT JOIN [表B] B ON A.ID = B.表AID
OUTER APPLY(SELECT TOP 1 类型,价格 FROM [表C]
WHERE 产品 = B.产品 AND 生效日期 <= B.业务时间
AND (
(类型='方量' AND B.方量 BETWEEN 最小值 AND 最大值)
OR (类型='毛重' AND B.毛重 BETWEEN 最小值 AND 最大值)
OR (类型='净重' AND B.净重 BETWEEN 最小值 AND 最大值)
)
ORDER BY 生效日期 DESC) C
