How to effectively make database queries?

Question

Sorry long question!

We have two database tables, for example. Car and wheel. They are connected by the fact that the wheel belongs to the car, and the car has several wheels. The wheels, however, are subject to change without prejudice to the “version” of the vehicle. A car record can be updated (for example, a paint job) without affecting the version of the wheels (i.e., without cascading updates).

For example, a Car table looks like this:

CarId, CarVer, VersionTime, Colour
   1      1       9:00       Red
   1      2       9:30       Blue
   1      3       9:45       Yellow
   1      4      10:00       Black

The wheel table looks like this (this car has only two wheels!)

WheelId, WheelVer, VersionTime, CarId
   1         1           9:00     1
   1         2           9:40     1
   1         3          10:05     1
   2         1           9:00     1

So, there were 4 versions of this two-wheeled car. This first wheel (WheelId 1) has not changed. The second wheel was changed (for example, painted) at 10:05.

, ? , , , . ( : CarId, CarVersion, WheelId, WheelVersion, CarVerTime, WheelVerTime), .

: 1, , 9:50. :

WheelId, WheelVer, WheelVerTime, CarId, CarVer, CarVerTime, CarColour
   1         2         9:40        1       3       9:45      Yellow
   2         1         9:00        1       3       9:45      Yellow

, , :

select c.CarId, c.VersionTime, w.WheelId,w.WheelVer,w.VersionTime,w.CarId
from Cars c, 
(    select w.WheelId,w.WheelVer,w.VersionTime,w.CarId
    from Wheels w
    where w.VersionTime <= "12 Jun 2009 09:50" 
     group by w.WheelId,w.CarId
     having w.WheelVer = max(w.WheelVer)
) w
where c.CarId = w.CarId
and c.CarId = 1
and c.VersionTime <= "12 Jun 2009 09:50" 
group by c.CarId, w.WheelId,w.WheelVer,w.VersionTime,w.CarId
having c.CarVer = max(c.CarVer)

, , SQL :

create table Wheels
(
WheelId int not null,
WheelVer int not null,
VersionTime datetime not null,
CarId int not null,
 PRIMARY KEY  (WheelId,WheelVer)
)
go

insert into Wheels values (1,1,'12 Jun 2009 09:00', 1)
go
insert into Wheels values (1,2,'12 Jun 2009 09:40', 1)
go
insert into Wheels values (1,3,'12 Jun 2009 10:05', 1)
go
insert into Wheels values (2,1,'12 Jun 2009 09:00', 1)
go


create table Cars
(
CarId int not null,
CarVer int not null,
VersionTime datetime not null,
colour varchar(50) not null,
 PRIMARY KEY  (CarId,CarVer)
)
go

insert into Cars values (1,1,'12 Jun 2009 09:00', 'Red')
go
insert into Cars values (1,2,'12 Jun 2009 09:30',  'Blue')
go
insert into Cars values (1,3,'12 Jun 2009 09:45',  'Yellow')
go
insert into Cars values (1,4,'12 Jun 2009 10:00',  'Black')
go

Answer 1

. , , . , SQL - , ; , , .

, :

. (1999). SQL

, PDF ( ).

. ISO/ANSI Standard SQL-92, SQL, SQL Server ( ), .

Joe Celko " : , SQL", Snodgrass, , , Snodgrass .

, SQL, . , , ; "", . SQL-92 SQL Server, . INTERVAL, OVERLAPS .. , , " " , , CHECK SQL Server, / UDF.

Snodgrass SQL3, SQL, , , , , , : (

Answer 2

, . , , :

select 
    ThisCar.CarId
,   StartTime = ThisCar.VersionTime
,   EndTime = NextCar.VersionTime
from Cars ThisCar
left join Cars NextCar
    on NextCar.CarId = ThisCar.CarId
    and ThisCar.VersionTime < NextCar.VersionTime
left join Cars BetweenCar
    on BetweenCar.CarId = BetweenCar.CarId
    and ThisCar.VersionTime < BetweenCar.VersionTime
    and BetweenCar.VersionTime < NextCar.VersionTime
where BetweenCar.CarId is null

. , vwCars, , :

select * 
from vwCars
where StartTime <= '2009-06-12 09:15' 
and ('2009-06-12 09:15' < EndTime or EndTime is null)

, .

Answer 3

, , . OLTP , , , .

, , , , .

Answer 4

, , " (/...) " ( ) " (//...)" ( ).

, Allen (, , ,...) , , .

SQL - , .

, , - . : , "" , , "" 10:05 (wheelVer 3) 10:00 ( ). , "-" 09:50.

" ". , , , , " , - ".

( ), , . , , .

Answer 5

This query will return duplicates if you have two rows with the same version time for the same car identifier, but it is a matter of determining what you consider to be the “last” in this situation. I have not yet had the opportunity to verify this, but I think it will give you what you need. It is at least pretty close.

SELECT
     C.car_id,
     C.car_version,
     C.colour,
     C.version_time AS car_version_time,
     W.wheel_id,
     W.wheel_version,
     W.version_time AS wheel_version_time,
FROM
     Cars C
LEFT OUTER JOIN Cars C2 ON
     C2.car_id = C.car_id AND
     C2.version_time <= @as_of_time AND
     C2.version_time > C.version_time
LEFT OUTER JOIN Wheels W ON
     W.car_id = C.car_id AND
     W.version_time <= @as_of_time
LEFT OUTER JOIN Wheels W2 ON
     W2.car_id = C.car_id AND
     W2.wheel_id = W.wheel_id AND
     W2.version_time <= @as_of_time AND
     W2.version_time > W.version_time
WHERE
     C.version_time <= @as_of_time AND
     C2.car_id IS NULL AND
     W2.wheel_id IS NULL