AGConsoleHelp.Пример SQL запроса для MSSQL

Материал из TK
Перейти к: навигация, поиск

CREATE TABLE Coordinate ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int  NULL,

DataDateTime datetime  NOT NULL,

Latitude float  NULL,

Longtitude float NULL,

lnHDOP decimal(1,0)  NULL,

HDOP decimal(2,0)  NULL,

ValidTime decimal(1,0) NOT NULL,

Source decimal(1,0)  NULL,

Power decimal(1,0) NOT NULL,

ReservPower decimal(1,0)  NULL,

Antenna1 decimal(1,0)  NULL,

Antenna2 decimal(1,0)  NULL,

SatCount decimal(2,0)  NULL,

Speed float  NULL,

Azimut int  NULL,

Altitude int  NULL,

In1 decimal(1,0)  NULL,

In2 decimal(1,0)  NULL,

In3 decimal(1,0)  NULL,

In4 decimal(1,0)  NULL,

In5 decimal(1,0)  NULL,

In6 decimal(1,0)  NULL,

In7 decimal(1,0)  NULL,

In8 decimal(1,0)  NULL,

Flag1 decimal(1,0)  NULL,

Flag2 decimal(1,0)  NULL,

Flag3 decimal(1,0)  NULL,

Flag4 decimal(1,0)  NULL,

Flag5 decimal(1,0)  NULL,

Flag6 decimal(1,0)  NULL,

Flag7 decimal(1,0)  NULL,

Flag8 decimal(1,0)  NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Flags ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

DataDateTime datetime  NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Power decimal(1,0) NOT NULL,

ReservPower decimal(1,0)  NULL,

Antenna1 decimal(1,0)  NULL,

Antenna2 decimal(1,0)  NULL,

In1 decimal(1,0)  NULL,

In2 decimal(1,0)  NULL,

In3 decimal(1,0)  NULL,

In4 decimal(1,0)  NULL,

In5 decimal(1,0)  NULL,

In6 decimal(1,0)  NULL,

In7 decimal(1,0)  NULL,

In8 decimal(1,0)  NULL,

Flag1 decimal(1,0)  NULL,

Flag2 decimal(1,0)  NULL,

Flag3 decimal(1,0)  NULL,

Flag4 decimal(1,0)  NULL,

Flag5 decimal(1,0)  NULL,

Flag6 decimal(1,0)  NULL,

Flag7 decimal(1,0)  NULL,

Flag8 decimal(1,0)  NULL,

PRIMARY KEY  (ID)

);

CREATE TABLE Analog ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

UPower int NOT NULL,

UReservPower int  NULL,

AIN1 int NOT NULL,

AIN2 int NOT NULL,

FlagsId int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN1 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Speed int NOT NULL,

CruiseControl decimal(1,0) NOT NULL,

BrakePedal decimal(1,0) NOT NULL,

CouplingPedal decimal(1,0) NOT NULL,

LayBrake decimal(1,0) NOT NULL,

GasPedal int NOT NULL,

FuelConsumption int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN2 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

FuelLevel1 int NOT NULL,

FuelLevel2 int NOT NULL,

FuelLevel3 int NOT NULL,

FuelLevel4 int NOT NULL,

FuelLevel5 int NOT NULL,

FuelLevel6 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN3 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

EngineTurns int NOT NULL,

TripToService int NOT NULL,

MotoHours int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN4 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

CoolingTemp int NOT NULL,

OilTemp int NOT NULL,

FuelTemp int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN5 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

TotalTrip int NOT NULL,

DayTrip int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN6 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

RecId int NOT NULL,

Wheel1 int NOT NULL,

Wheel2 int NOT NULL,

Wheel3 int NOT NULL,

Wheel4 int NOT NULL,

Wheel5 int NOT NULL,

Wheel6 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE CAN7 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

RecId int NOT NULL,

User1 int NOT NULL,

User2 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Count12 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Count1 int NOT NULL,

Count2 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Count34 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Count3 int NOT NULL,

Count4 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Count56 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Count5 int NOT NULL,

Count6 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Count78 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Count7 int NOT NULL,

Count8 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Event ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

EventId int NOT NULL,

EventData binary(5) NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE InExp ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

InHeight int NOT NULL,

InLow int NOT NULL,

InStatus int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE LLS14 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

LLS1 int NOT NULL,

ValidLLS1 decimal(1,0) NOT NULL,

LLS2 int NOT NULL,

ValidLLS2 decimal(1,0) NOT NULL,

LLS3 int NOT NULL,

ValidLLS3 decimal(1,0) NOT NULL,

LLS4 int NOT NULL,

ValidLLS4 decimal(1,0) NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE LLS58 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

LLS5 int NOT NULL,

ValidLLS5 decimal(1,0) NOT NULL,

LLS6 int NOT NULL,

ValidLLS6 decimal(1,0) NOT NULL,

LLS7 int NOT NULL,

ValidLLS7 decimal(1,0) NOT NULL,

LLS8 int NOT NULL,

ValidLLS8 decimal(1,0) NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Temp14 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Temp1 int NOT NULL,

Temp2 int NOT NULL,

Temp3 int NOT NULL,

Temp4 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE Temp58 ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

Temp5 int NOT NULL,

Temp6 int NOT NULL,

Temp7 int NOT NULL,

Temp8 int NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE iButton ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

iButtonId binary(6) NOT NULL

PRIMARY KEY  (ID)

);

CREATE TABLE TirePressure ( 

ID int IDENTITY(1,1) NOT NULL,

DeviceID int NOT NULL,

FlagsId int NOT NULL,

DataDateTime datetime NOT NULL,

ValidTime decimal(1,0) NOT NULL,

WheelPosition int NOT NULL, 

AxletreePosition int NOT NULL,

TireTemperature int NOT NULL,

Pressure float NOT NULL,

LowPressure decimal(1,0)  NOT NULL,

HighPressure decimal(1,0)  NOT NULL,

HighTemperature decimal(1,0)  NOT NULL,

LowBattery decimal(1,0)  NOT NULL,

Reserv decimal(1,0)  NOT NULL,

HighLeakage decimal(1,0)  NOT NULL,

LowLeakage decimal(1,0)  NOT NULL,

TireDamage decimal(1,0)  NOT NULL,

PRIMARY KEY  (ID)

);

Источник — «http://oldwiki.tk-chel.ru/index.php?title=AGConsoleHelp.Пример_SQL_запроса_для_MSSQL&oldid=1849»