我可能只是设置我的查询完全不正确,但我需要返回的预期结果集是Nuestra B & Nosotros B
因为它们分别属于日期范围。IE Nuestra Bfeeduedate >= '20160301' AND fj.feeduedate <='20160330'
和 Nosotros Bfe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
它们就位。
但是,当我运行此查询尝试返回结果时,我的结果集中返回了 0。
我的查询有什么问题?以下是示例数据和 DDL
CREATE TABLE [dbo].[fei](
[violatorsName] [varchar](500) NOT NULL,
[violationNumber] [varchar](100) NOT NULL,
[violationDate] [date] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[FJI](
[violatorsName] [varchar](500) NOT NULL,
[VIN] [varchar](100) NOT NULL,
[vfees1] [float] NOT NULL,
[vfees2] [float] NOT NULL,
[vfees3] [float] NOT NULL,
[vfees4] [float] NOT NULL,
[vfees5] [float] NOT NULL,
[vfees6] [int] NULL,
[feeduedate] [date] NULL,
[totalvfees] [float] NOT NULL,
[totalvfeespaid] [float] NOT NULL,
[vfeesremaining] [float] NOT NULL,
[vfee7] [float] NOT NULL,
[vfee8] [float] NOT NULL,
[vfee9] [float] NOT NULL,
[vfee10] [float] NOT NULL,
[vfee11] [float] NOT NULL,
[vfee12] [float] NULL,
[vfee13] [float] NULL,
[vfee14] [float] NULL,
[vfee15] [float] NULL,
[vfee16] [float] NULL
) ON [PRIMARY]
INSERT [dbo].[fei] ([violatorsName], [violationNumber], [violationDate]) VALUES (N'Nostra A', N'3244', CAST(0xE63C0B00 AS Date))
INSERT [dbo].[fei] ([violatorsName], [violationNumber], [violationDate]) VALUES (N'Nuestra B', N'408', CAST(0xE53C0B00 AS Date))
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros A', N'41', 1917.71, 0, 898.3, 10.870000, 0, 1906, CAST(0x353B0B00 AS Date), 9948, 9773, 7867, 1429.5, 9296.5, 401.53999999999996, 9371, 2826.88, 20782, 28875.31, 222059.4, 0, 0)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'311', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170101', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'811', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170103', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'11111', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'77711', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
SELECT ISNULL(fe.violatorsName,fj.violatorsName)
, TotalSW = COUNT(fe.violationNumber)
, TotalSNT = COUNT(fj.VIN)
, totalvfees = SUM(totalvfees)
, calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
, AdminFee = SUM(COALESCE(vfee12,0))
, SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
AND fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)
<strong>EDIT</strong><br>
SELECT ISNULL(fe.violatorsName,fj.violatorsName)
, TotalSW = COUNT(fe.violationNumber)
, TotalSNT = COUNT(fj.VIN)
, totalvfees = SUM(totalvfees)
, calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
, AdminFee = SUM(COALESCE(vfee12,0))
, SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
OR (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)
当您有一个时,针对任何外部表的
OUTER JOIN
任何WHERE
子句都会将其转换为内部表。这意味着只有可以评估该谓词的行才能通过。实际上,an 中的一个子句使得它成为一个.WHERE
OUTER JOIN
INNER JOIN
我会尝试:
或者:
获得相同结果的另一种方法(但可能使用不同的执行计划)是分别从每个表中获取所需的行,然后获取
FULL JOIN
它们:在dbfiddle.uk测试。