83 lines
3.0 KiB
Transact-SQL
83 lines
3.0 KiB
Transact-SQL
--<2D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD>AuthorityDB sql
|
||
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'MACLogs') and xtype='U')
|
||
drop table MACLogs
|
||
go
|
||
create table MACLogs(
|
||
Id int identity(1,1),
|
||
HostsId int not null,
|
||
MAC nvarchar(50) null,
|
||
userid int not null,
|
||
location nvarchar(100) null,
|
||
Ip nvarchar(50) null,
|
||
ActionId INT null,
|
||
[type] int default 0, -- 0 <20><><EFBFBD><EFBFBD> 1 <20><><EFBFBD> 2 <20><><EFBFBD>İ<EFBFBD>
|
||
CreateTime datetime default getdate()-- 0 <20><><EFBFBD><EFBFBD> 1 <20><><EFBFBD>
|
||
)
|
||
go
|
||
--<2D><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ݿ<EFBFBD>AuthorityDB sql
|
||
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'ErrorInfo') and xtype='U')
|
||
drop table ErrorInfo
|
||
go
|
||
create table ErrorInfo(
|
||
Id int identity(1,1),
|
||
HostsId int not null,
|
||
MAC nvarchar(50) null,
|
||
userid int not null,
|
||
[type] int default 0, -- 0 <20><><EFBFBD>䱻<EFBFBD><E4B1BB> 1 MAC<41><43><EFBFBD><EFBFBD>
|
||
[status] int default 0,--<2D>Ƿ<EFBFBD><C7B7>Ѿ<EFBFBD><D1BE><EFBFBD><EFBFBD><EFBFBD> 0 δ<><CEB4><EFBFBD><EFBFBD> 1 <20>Ѿ<EFBFBD><D1BE><EFBFBD><EFBFBD><EFBFBD>
|
||
CreateTime datetime default getdate()-- ʱ<><CAB1>
|
||
)
|
||
go
|
||
|
||
select * from Hosts where id = 14939
|
||
|
||
SELECT * FROM MACLogs
|
||
if EXISTS(SELECT 1 FROM sys.views WHERE name='View_MAClog')
|
||
begin
|
||
drop view View_MAClog
|
||
end
|
||
go
|
||
create view View_MAClog
|
||
as
|
||
select m.*,u.[Uid] ,ht.Name,Hotelsid = ht.Id
|
||
from MACLogs m
|
||
left join Hotels ht on ht.id = m.HotelID
|
||
left join UserInfo u on u.id = m.userid
|
||
go
|
||
select *,CreateTime from MACLogs
|
||
SELECT * FROM View_MAClog
|
||
SELECT * FROM ErrorInfo --WHERE
|
||
SELECT * FROM UserInfo WHERE ID = 212
|
||
--<2D><> sql
|
||
select * from tb_Hosts where hotelid = 3
|
||
|
||
update tb_Hosts set MAC=MAC where MAC= '34-D0-B8-11-10-4D';update tb_Hosts set MAC='34-D0-B8-11-10-4D' where Id = 14810;
|
||
DECLARE @MAC int;
|
||
DECLARE @ID int;
|
||
--<2D><><EFBFBD><EFBFBD> @MAC <20>µ<EFBFBD>MAC @ID Ϊ<><CEAA>Ҫ<EFBFBD><D2AA><EFBFBD><EFBFBD>mac<61><63>id
|
||
begin tran
|
||
DECLARE @HostsId int;
|
||
DECLARE @oldmac nvarchar(50);
|
||
DECLARE @HostsIdoldmac nvarchar(50);
|
||
SET @HostsId = -1;
|
||
SET @oldmac = '-1';
|
||
SET @HostsIdoldmac = '-1';
|
||
SELECT TOP 1 @HostsId = id, @oldmac= MAC FROM tb_Hosts where MAC = @MAC
|
||
SELECT TOP 1 @HostsIdoldmac = MAC FROM tb_Hosts where Id = @ID and mac is not null and mac!=''
|
||
PRINT convert(varchar,@HostsId) +'&'+ @oldmac+'&'+@HostsIdoldmac
|
||
update tb_Hosts set MAC='' where id = @HostsId
|
||
update tb_Hosts set MAC=@MAC where Id = @ID
|
||
if @@ERROR>0
|
||
begin
|
||
rollback tran
|
||
end
|
||
ELSE
|
||
BEGIN
|
||
COMMIT TRAN
|
||
END
|
||
|
||
;
|
||
|
||
go
|
||
|
||
select * from Hosts where Id = 14939 |