sqlserver语句建表数据库等等

USE master
go

--drop database b2019dc
--go

create database b2019dc  ----创建数据库b2019dc
 ON  PRIMARY 
(
FILENAME = N'd:\data\b2019dc.mdf',name =N'b2019dc.mdf'   --放在D盘data目录下
)
go

use b2019dc   --切换到这个数据库
go

--drop table t_m1   ----删除这个表
--go

create table t_m4(
	id bigint identity(1,1) not null,
	InstrumentID nvarchar(max) not null default '',
	TradingDay date not null default getdate(),	
	UpdateTime time(0) not null default getdate(),
	LastPrice decimal(18, 2) not null default 0,
	PreClosePrice decimal(18, 2) not null default 0,
	OpenPrice decimal(18, 2) not null default 0,
	HighestPrice decimal(18, 2) not null default 0,
	LowestPrice decimal(18, 2) not null default 0,
	Volume int not null default 0,
	LowerLimitPrice decimal(18, 2) not null default 0,
	primary key(id)
)
go

create table t_m13(
	id bigint identity(1,1) not null,
	InstrumentID nvarchar(max) not null default '',
	TradingDay date not null default getdate(),	
	UpdateTime time(0) not null default getdate(),
	LastPrice decimal(18, 2) not null default 0,
	PreClosePrice decimal(18, 2) not null default 0,
	OpenPrice decimal(18, 2) not null default 0,
	HighestPrice decimal(18, 2) not null default 0,
	LowestPrice decimal(18, 2) not null default 0,
	Volume int not null default 0,
	LowerLimitPrice decimal(18, 2) not null default 0,
	primary key(id)
)
go

select count(*) from t_m5

select * from t_m5
delete from t_m5


select * from (select *,volume - lag(volume,1,0) over (partition by InstrumentID,tradingday order by InstrumentID asc, tradingday asc , volume asc ) as tmpvol from t_m1 ) as tmp_t1 where  tmpvol <> 0 order by instrumentid asc, tradingday asc, Volume asc


--下面作废
--select TradingDay,InstrumentID,LastPrice,PreClosePrice,OpenPrice,HighestPrice,LowestPrice,Volume,LowerLimitPrice,UpdateTime,vol from
--(
--    select top 1000000000 *, (
--                            volume - LAG(volume, 1,0) over (order by instrumentid asc, tradingday asc, Volume asc)
--                        )
--    as vol from t_m7
--) as tmpvol
--where vol > 0 order by instrumentid asc, tradingday asc, Volume asc