带有DISTINCT选项的SQL INNER JOIN [英] SQL INNER JOIN with DISTINCT option

问题描述

我在MS Access数据库中有2个表：



带列的车辆表 -

制作

Registration_No

司机

保险

折旧





带列的VEHICLE_ALLOCATION表 -

日期

Registration_No

项目



VEHICLES表是主车辆列表。

VEHICLE_ALLOCATION表是一个表格，其中包含给定车辆在给定项目中使用的日期。



我需要生成一份报告，显示一个月内某个项目使用车辆的天数。

我需要从VEHICLE_ALLOCATION中选择DISTINCT Registration_No（以获取所有使用过的车辆的清单，以及INNER JOIN车辆的所有详细信息，车辆在VEHICLE_ALLOCATION.DATE位于给定月份的第1天和最后一天之间。

比方说，50 ve在VEHICLES表中列出的hicles，但是在一个月的时间内只有10个可以分配给项目。

我一直在努力构建一个单独的SQL语句，它将返回所有的列表VEHICLE详细信息基于从VEHICLE_ALLOCATION获得的DISTINCT Registration_No。



我无法发布任何示例代码，因为我尝试了很多变化（没有成功）而且我不会我不知道要发布哪一个。



这样的声明是否可构建？帮助任何人？



提前谢谢。

解决方案

我试过这样的，我用过temp对于sql server的表，我不确定它在ms访问中是否相同

  CREATE   TABLE  #VEHICLES（
 Make  VARCHAR （ 100 ），
 Registration_No  VARCHAR （ 100 ）， 
驱动程序 VARCHAR （ 100 ），
折旧 VARCHAR （ 100 ）
）
 创建 < span class =code-keyword> TABLE  #VEHICLE_ALLOCATION（
 日期  DATE ，
 Registration_No  VARCHAR （ 100 ），
项目 VARCHAR （ 100 ）
）
 
  SELECT  * 
  FROM  #VEHICLES 
  SELECT  * 
  FROM  #VEHICLE_ALLOCATION 
 
  DROP   TABLE  #VEHICLES 
  DROP  表 #VEHICLE_ALLOCATION 
 
  SELECT  MONTH（GETDATE（））
  SELECT  DAY（GETDATE（））
 
 
  / *  主查询* /  
  SELECT  * 
  FROM  #VEHICLES  AS  Vehicales 
  INNER   JOIN  
（
  SELECT  Registration_No，Project，COUNT（*） AS  TotalDays 
  FROM  #VEHICLE_ALLOCATION 
  / *  添加条件* /  
  WHERE  MONTH（日期）=  1  
  AND （DAY（日期）< =  30  和 DAY（日期）> =  1 ）
  GROUP  < span class =code-keyword> BY  Registration_No，Project 
 
） AS 分配
  ON  Vehicales.Registration_No = Allocations.Registration_No 

从主查询中删除＃符号。

如果你想添加分配日期也是另一个故事。

你需要正确定义连接规则。请阅读本文以了解不同类型的连接之间的区别： SQL连接的可视化表示 [ ^ ]。

I have 2 tables within an MS Access database:

A VEHICLES table with columns –
Make
Registration_No
Driver
Insurance
Depreciation


A VEHICLE_ALLOCATION table with columns –
Date
Registration_No
Project

The VEHICLES table is a master vehicle listing.
The VEHICLE_ALLOCATION table is a table containing the date that a given vehicle as used on a given project.

I need to produce a report showing how many days a vehicle was used on a given project over a period of a month.
I need to select DISTINCT Registration_No from VEHICLE_ALLOCATION (to get a list of all the vehicles that were used) and with an INNER JOIN all the details about the vehicle from VEHICLES WHERE the VEHICLE_ALLOCATION.DATE is between the 1st and last days of a given month.
There may be, say, 50 vehicles listed in the VEHICLES table but only 10 could have been allocated to projects over a period of a month.
I have been trying for hours to construct a single SQL statement that will return a list of all the VEHICLE details based on a DISTINCT Registration_No obtained from VEHICLE_ALLOCATION.

I can't post any sample code because I have tried so many variations (with zero success) and I wouldn't know which one to post.

Is such a statement constructable? Help anyone?

Thanks in advance.

解决方案

Well i have tried like this, i used temp tables for sql server, i am not sure would it gonna be same in ms access

CREATE TABLE #VEHICLES(
	Make VARCHAR(100),
	Registration_No VARCHAR(100),
	Driver VARCHAR(100),
	Depreciation VARCHAR(100)
)
CREATE TABLE #VEHICLE_ALLOCATION(
	Date DATE,
	Registration_No VARCHAR(100),
	Project VARCHAR(100)
)

SELECT *
	FROM #VEHICLES
SELECT *
	FROM #VEHICLE_ALLOCATION

DROP TABLE #VEHICLES
DROP TABLE #VEHICLE_ALLOCATION

SELECT MONTH(GETDATE())
SELECT DAY(GETDATE())


/*Main query*/
SELECT *
	FROM #VEHICLES AS Vehicales
	INNER JOIN 
	(
		SELECT Registration_No, Project, COUNT(*) AS TotalDays
			FROM #VEHICLE_ALLOCATION
			/*Add the conditons*/
			WHERE MONTH(Date) = 1
			AND (DAY(Date) <= 30 AND DAY(Date) >= 1)
			GROUP BY Registration_No, Project
			
	) AS Allocations
	ON Vehicales.Registration_No = Allocations.Registration_No

remove the # signs from the Main query.
if you want to add allocation date also that a different story.

You need to properly define join rules. Please, read this article to understand the difference between different types of joins: Visual Representation of SQL Joins[^].

这篇关于带有DISTINCT选项的SQL INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！