当前位置: 澳门新濠3559 > 数据库 > 正文

删除本地及海关单证待分派表、报关单表中的数

时间:2019-10-07 08:58来源:数据库
select count(*) from W_DECLARATIONS_TEST group by entry_id havingcount(id)1 insert into 表2  values (3,'在路上' ,cast('2005-1-1' asdatetime),'马化腾') create function dbo.fc_str(@ProductID varchar(10)) returns varchar(100) as begin  

select count(*) from W_DECLARATIONS_TEST group by entry_id having count(id)<>1

insert into 表2  values (3,'在路上' ,cast('2005-1-1' as datetime),'马化腾')

create function dbo.fc_str(@ProductID varchar(10))
returns varchar(100)
as
begin
 declare @sql varchar(1000)
 set @sql=''
 select @sql=@sql ',' cast(PositionID as varchar(20)) from tb where ProductID=@ProductID
 return stuff(@sql,1,1,'')
end
go

 发帖表

呈报帖子公布的连带音信

/* 步骤2

NAME    varchar(10) ,

--分组总括,总计各个段中多少的个数
--日常战绩计算能够用到这些
declare @t table(id int,weight int)
insert into @t select  1, 20
insert into @t select  2, 15
insert into @t select  3,  5
insert into @t select  4, 60
insert into @t select  5, 12
insert into @t select  6, 33
insert into @t select  7, 45
insert into @t select  8, 59
insert into @t select  9, 89
insert into @t select 10,110

搜索表

/* 步骤3

    begin

 

t_comment

CREATE TABLE 't_comment'(

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

'posting_id'  bigint(20) NOT NULL  COMMEN '帖子id',

`comment_people_id`  bigint(20) DEFAULT NULL COMMENT '评论人id',

`comment_time` datetime DEFAULT NULL COMMENT '批评时间',

`comment_content` varchar(255) DEFAULT NULL COMMENT '批评内容',

`REVISION` int(11) DEFAULT NULL,

`CREATE_USER_ID` varchar(20) DEFAULT NULL,

`LAST_UPDATE_USER_ID` varchar(20) DEFAULT NULL,

`CREATE_DATE` datetime DEFAULT NULL,

`LAST_UPDATE_DATE` datetime DEFAULT NULL,

`TENANT_ID` varchar(255) DEFAULT NULL,

`APP_NAME` varchar(255) DEFAULT NULL,

`APP_SCOPE` varchar(255) DEFAULT NULL,T

)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-


*/

               2   秦云    2005-1-1  马化腾

--------按项目去最新的前N条记下,把同一类的位于一齐,总括同一类的项的个数等-------------
create table t
(
 ClassName varchar(50),
 ClassCode varchar(10),
 ClassID int identity(1,1)
)
insert into t
select 'cccc1','002' union all
select 'aaaa','001' union all
select 'bbbb','001' union all
select 'aaaa1','002' union all
select 'cccc','001' union all
select 'dddd','001' union all
select 'bbbb1','002' union all
select 'dddd1','002'
select * from t
select ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc

t_search

陈述顾客的查找历史

CREATE TABLE  't_search'(

`id` bigint(20) NOT NULL AUTO_INCREMENT,

'user_id'  bigint(20)NOT NULL  COMMEN '用户ID',

 `content` varchar(45) DEFAULT NULL COMMENT '寻找内容',

`search_time` datetime  DEFAULT NULL COMMENT '寻找时间',

`REVISION` int(11) DEFAULT NULL,

`CREATE_USER_ID` varchar(20) DEFAULT NULL,

`LAST_UPDATE_USER_ID` varchar(20) DEFAULT NULL,

`CREATE_DATE` datetime DEFAULT NULL,

`LAST_UPDATE_DATE` datetime DEFAULT NULL,

`TENANT_ID` varchar(255) DEFAULT NULL,

`APP_NAME` varchar(255) DEFAULT NULL,

`APP_SCOPE` varchar(255) DEFAULT NULL,T

)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-


select datediff(mi,'2013-10-11 16:13:08','2013-10-11 17:18:39');

    deallocate myCur

--删除情形
drop function f_go
drop table t

 评论表

/* 步骤1

姓名    上机次数    管理员

select classCode,className from t order by classCode,classID desc
drop table t

回复表

begin
exec GenBGDH @p2,@p1,@p3,@p4,@p5,@p6,@p7,@p8
end


EXEC [master]..sp_dboption [Database Name], 'autoshrink', 'TRUE'

t_reply

CREATE TABLE 't_reply'(

`ID` bigint(20) NOT NULL AUTO_INCREMENT,

`from_people_id`  bigint(20) DEFAULT NULL COMMENT '那条回复是还原给何人的',

`reply_people_id`  bigint(20) DEFAULT NULL COMMENT '回复人id',

'comment_id'  bigint(20) NOT NULL  COMMENT '评论id',

`reply_id` bigint(20)DEFAULT NULL COMMENT '回复目标id',

'reply_type'   varchar(20) DEFAULT NULL COMMENT '回复种类(0-对商议的上涨,1-对恢复生机的过来)',

`comment_time` datetime DEFAULT NULL COMMENT '回复时间',

`comment_content` varchar(255) DEFAULT NULL COMMENT '回复内容',

`REVISION` int(11) DEFAULT NULL,

`CREATE_USER_ID` varchar(20) DEFAULT NULL,

`LAST_UPDATE_USER_ID` varchar(20) DEFAULT NULL,

`CREATE_DATE` datetime DEFAULT NULL,

`LAST_UPDATE_DATE` datetime DEFAULT NULL,

`TENANT_ID` varchar(255) DEFAULT NULL,

`APP_NAME` varchar(255) DEFAULT NULL,

`APP_SCOPE` varchar(255) DEFAULT NULL,T

)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

当reply_type='0'  令reply_id=comment_id   表名那条回复是本着对象评价的;当reply_type='1'  令reply_id=父回复的id

insert into W_DOCUMENTS_TEST(id,I_E_FLAG,TRADE_MODE,ENTRY_ID)
select id,I_E_FLAG,TRAF_MODE,ENTRY_ID from W_DECLARATIONS_TEST;

秦云        3       李大伟,马化腾,李大伟

select a,case b when '11' then '细细' when '22' then '呵呵' else '哈哈' end as 转换,c from table1

t_publish

CREATE TABLE  't_search'(

`id` bigint(20) NOT NULL AUTO_INCREMENT,

'user_id'  bigint(20)NOT NULL  COMMEN '用户ID' ,

`content` varchar(255) DEFAULT NULL COMMENT '发帖内容',

`publish_time` datetime  DEFAULT NULL COMMENT '发帖时间',

'section'   varchar(255)  DEFAULT NULL COMMENT '发帖版块',

'theme'   varchar(255)  DEFAULT NULL COMMENT '发帖大旨',

'click_num'  int(20) DEFAULT NULL COMMENT '点击该帖数量',

'keyword'  varchar(255)  DEFAULT NULL COMMENT '关键字',

'publish_state'  DEFAULT NULL COMMENT '发帖状态(0-草稿,1-发布)',

`REVISION` int(11) DEFAULT NULL,

`CREATE_USER_ID` varchar(20) DEFAULT NULL,

`LAST_UPDATE_USER_ID` varchar(20) DEFAULT NULL,

`CREATE_DATE` datetime DEFAULT NULL,

`LAST_UPDATE_DATE` datetime DEFAULT NULL,

`TENANT_ID` varchar(255) DEFAULT NULL,

`APP_NAME` varchar(255) DEFAULT NULL,

`APP_SCOPE` varchar(255) DEFAULT NULL,T

)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-


调用带输入参数的蕴藏进度
GenBGDH
条件2 I_E_FLAG,
条件1 TRAF_MODE,
稳固前4位的单证号 TMP_ENTRY_ID
航次 VOYAGE_NO,
船名 TRAF_NAME,
提单号 BILL_NO,
是还是不是拼箱、联单(1:是|0:是) @flag,
条数(数字范围:0~32767) @count

问题:  


UPDATE W_DOCUMENTS_TEST
SET [CREATE_DATE] =getdate()
,[DECL_PORT] =convert(int,ceiling(rand()*1000 1000))
,[RANDOM_FLAG] =convert(int,ceiling(rand()*10))
,[OP_DATE] =dateadd(hh,-0.5,getdate())
,[ENTRY_TYPE] =1
,[D_DATE] =getdate()
,[CERT_FLAG] =1
,[TAX_FLAG] =1;

               秦云             3             李大伟,马化腾,李大伟

 

*/

        set @nameStr = @nameStr ',' @tempStr

--method one
delete from A where userid not in(select min(userid) as userid from A group by username ,userpwd)

select count(*) from W_DOCUMENTS;

               3    在路上  2005-1-1  马化腾

--------------------------------自动缩短数据库begin-----------------------------

set @p1='g';
set @p2='o';
set @p3=534500000000000000;
set @p4='HC1';
set @p5='CM1';
set @p6='BN01';
set @p7=0;
set @p8=100;

    close myCur

/*---------------------------------------------*/

*/

若是条件如下:

------------删除数据库中的重复记录(且仅保留一条有效记录)示例-----------------
create table A
(
 userID int identity(1,1),
 userName varchar(20),
 userPwd varchar(20),
 userEmail varchar(50)
)
insert into A(userName,userpwd) select 'qin','qin' union all select 'qin','qin1' union all select 'qin','qin1'
select * from A

挑选报关单表中的单证号、条件1、条件2列中数据,插入到本地单证待分派表

QQ     varchar(10),

收获当前岁月(时/分/秒):select convert(varchar(10),getdate(),8)
获得当二〇一六年月日:select convert(varchar(10),getdate(),120)
取稳妥前时间:select convert(varchar(7),getdate(),120)
获取当前时光:select convert(varchar(10),year(getdate())) '-' convert(varchar(10),month(getDate()))

*/

    declare myCur cursor for ( select 管理员 from 表2 where 表2.NAME = @name )

declare @p int
set @p=10
select
    rtrim(p*@p) '-' rtrim((p 1)*@p">p*@p) '-' rtrim((p 1)*@p) as p,
    num
from
    (select (weight/@p">weight/@p) as p,count(*) as num from @t where weight between 10 and 100 group by (weight/@p">weight/@p)) a

查询已成功分派了有个别条单证

    declare @nameStr nvarchar(800)

--------按种类去最新的前N条记下,把同一类的放在一块儿,总计同一类的项的个数等-------------

相配生成单证法规
declare @p1 varchar(10),@p2 varchar(10),@p3 bigint,@p4 varchar(100);
declare @p5 varchar(100),@p6 varchar(100),@p7 tinyint,@p8 smallint;

insert into 表2  values(1,'秦云'   ,cast('2004-1-1' as datetime),'李大伟')

--调用
--select * from dbo.f_go('A')
select col,km from dbo.f_go('a')

查询报关单表中是还是不是有双重生成的单证号数据

FROM  表1 LEFT OUTER JOIN

select ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductID

询问海关单证待分派表中是不是有重新生成的单证号数据

    declare @tempStr nvarchar(800)

select count(*),classCode from (select top 100 percent ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc)a group by classcode

select count(*) from W_DOCUMENTS_TEST group by entry_id having count(id)<>1

    set @flag = 0

------------删除数据库中的重复记录(且仅保留一条有效记录)示例-----------------

*/

create table 表1( --drop table 表1

drop table tb

create procedure GenBGDH @I_E_FLAG varchar(10),@TRAF_MODE varchar(10),@TMP_ENTRY_ID bigint,
@VOYAGE_NO varchar(100),@TRAF_NAME varchar(100),@BILL_NO varchar(100),@flag tinyint,@count smallint
with encryption
as
declare @ENTRY_ID bigint;
while(@count>0)
begin
SET @ENTRY_ID =convert(bigint,ceiling(rand()*100000000000000 @TMP_ENTRY_ID));
if @flag=1
begin
insert into W_DECLARATIONS_TEST(I_E_FLAG,TRAF_MODE,ENTRY_ID,VOYAGE_NO,TRAF_NAME,BILL_NO)
values(@I_E_FLAG,@TRAF_MODE,@ENTRY_ID,@VOYAGE_NO,@TRAF_NAME,@BILL_NO);
end
else if @flag=0
begin
insert into W_DECLARATIONS_TEST(I_E_FLAG,TRAF_MODE,ENTRY_ID)
values(@I_E_FLAG,@TRAF_MODE,@ENTRY_ID);
end
else print'不进行插入数据操作'
set @count=@count-1;
end

    end

--取各种类的前n条记下(各种类都取top n条)
--假诺有数据库中有八个类,未来要取各个类的前n条记下,可用以下语句
Create Table TEST
(ID Int Identity(1,1),
 h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
GO
--方法一:
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)
--方法二:
Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)
--方法三:
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3
GO
Drop Table TEST
GO

相配生成单证法规的数码
条件1 条件2
g o
b w
a f
v x
g d
j f
船名 航次 提单号 箱号
cm hc bn cn
TRAF_NAME VOYAGE_NO BILL_NO CONTAINER_NO
CM1 HC1 BN01 CN1
CM1 HC1 BN02 CN2

SELECT  表1.NAME AS 姓名, COUNT(表2.ID) AS 上机次数

/*sql 语艺术学习*/

/*
其他
2个时刻之间距离的秒数

insert into 表2  values(4,'秦云'   ,cast('2005-1-1' as datetime),'李大伟')

 

*/

    表2:      ID, NAME,  上机时间,管理员,

---------------------------round的用法beigin------------------------------
declare @s float
set @s = 0.1566134
print round(@s,3)
---------------------------round的用法end---------------------------------

删除本地及海关单证待分派表、报关单表中的数据
delete from W_DOCUMENTS;
delete from W_DOCUMENTS_TEST;
delete from W_DECLARATIONS_TEST;

    fetch next from myCur into @tempStr

--创立函数
--函数重返三个表,依照实际情况的比不上一层一层的插入,能够足够利用生成的表
create function f_go(@col varchar(10))
 returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
 declare @i int
 set @i=1
 insert @t select st '-' ed,*,@i from t where st=@col
 while exists (select * from t a,@t b where
  b.ed=a.st and b.level=@i and b.ed<>@col )
 begin
  set @i=@i 1
  insert @t
  select b.col '-' a.ed,a.st,a.ed,b.km a.km,@i from t a,@t b
   where b.level=@i-1 and b.ed=a.st and b.ed<>@col
 end
 return
end
go

/* 步骤4

create function GetNameStr(@name nvarchar(10))

-------------------------------迭归的使用(找起源和极端之间的路线-----------------------------

总括本地及海关单证待分派表、报关单表中的数据总条数
select count(*) from W_DOCUMENTS;
select count(*) from W_DOCUMENTS_TEST;
select count(*) from W_DECLARATIONS_TEST;

    open myCur

/*函数的读书---------------------------------------*/

/*
步骤5
履新当地单证待分派表中其余列数据
将OP_DATE字段的命宫改成与近些日子系统时间距离为1个钟头之内的岁月

)

-------------同上,按体系举行总括,把同一类的项的任何剧情张开相加并发在一个字段中------------------
create table tb(ProductID varchar(10),PositionID varchar(10))
insert into tb
select '10001','A1'
union all select '10001','B2'
union all select '10002','C3'
union all select '10002','D4'
union all select '10002','E5'
go

    else

--method two
delete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid < b.userid)

    表1:      ID, NAME,      QQ,     PHONE,

select cast(b as integer) as bb from table1 where b = '11'

insert into 表1 values(3   ,'LEO'     ,'10000'        ,'13900000')

----------------------------在in语句中只用自定义排序end--------------------------------

      表2 ON 表1.NAME = 表2.NAME

drop function dbo.fc_str

               4    秦云   2005-1-1  李大伟

-------------------------------去除首尾无效的字符begin--------------------------
declare @s varchar(20)
set @s=',,,1->1,'
while(left(@s,1)=',')
set @s=stuff(@s,1,1,'')
while(right(@s,1)=',')
set @s=stuff(reverse(@s),1,1,'')
select @s
-------------------------------去除首尾无效的字符end--------------------------

insert into 表2  values(5,'在路上' ,cast('2005-1-1' as datetime),'李大伟')

-------------按连串举办总括,把同一类的项的任何剧情张开相加并发在三个字段中------------------

    declare @flag int

 

NAME   varchar(10),

 

    return @nameStr

获得当前天子:print current_timestamp

group by 表2.NAME

select a,b,case when c = '111' then '细细' when c = '222' then '呵呵' else '哈哈' end as 转换1 from table1

    fetch next from myCur into @tempStr

-------------------------------迭归的接纳(找起源和顶峰之间的门路-----------------------------
create table t
 (st varchar(20),ed varchar(20),km int)
go
insert t values ('A','B',1000)
insert t values ('A','C',1100)
insert t values ('A','D',900)
insert t values ('A','E',400)
insert t values ('B','D',300)
insert t values ('D','F',600)
insert t values ('E','A',400)
insert t values ('F','G',1000)
insert t values ('C','B',600)
go
--展现插入值
select * from t
go

    while @@fetch_status = 0

 

管理员    varchar(10)

 

GROUP BY 表1.名称

select * from A
drop table A

select 表2.NAME as 姓名, count(ID) as 上机次数, dbo.GetNameStr(表2.NAME) as 管理员

 

要是不算管理员那一列的话,小编是那样写的。

--method three
delete from a where userid not in(select min(userid) from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid > b.userID)

ID     int,

--------------------------------自动减弱数据库end-----------------------------

ID        int,

----------------------------在in语句中只用自定义排序begin--------------------------------
declare @t table(id int,weight int)
insert into @t select  1, 20
insert into @t select  2, 15
insert into @t select  3,  5
insert into @t select  4, 60
insert into @t select  5, 12
insert into @t select  6, 33
insert into @t select  7, 45
insert into @t select  8, 59
insert into @t select  9, 89
insert into @t select 10,110
--暗中同意in语句中sql会依据id举行排序
select * from @t where id in(2,4,3)
--用此格局能够依照我们传入的id顺序举办展现数据
select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')

begin

-----------------将sql查询输出到txt文本文件中-------------------------------------------
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out d:/1.txt -c -q -U"sa" -P"password"'

    end

returns nvarchar(800)

表中数据:      1       秦云        10102800 13四千00

    end

where 表2.NAME in ( select 表1.NAME from 表1 )

表中数据:     1   秦云    二〇〇三-1-1  李大伟

                2       在路上      10378    13600000

测验结果:

上机时间  datetime,

               LEO              0      

)

        set @nameStr = @tempStr

insert into 表2  values(2,'秦云'   ,cast('2005-1-1' as datetime),'马化腾')

    set @flag = @flag 1

                3       LEO         10000    13900000

在路上      2       马化腾,李大伟

               在路上           2            马化腾,李大伟

insert into 表1 values(1   ,'秦云'    ,'10102800'     ,'13500000')

    begin

insert into 表1 values(2   ,'在路上'  ,'10378'        ,'13600000')

from 表2

create table 表2( --drop table 表2

end

PHONE  varchar(20)

as

前后相继部分

             上机职员名单    上机次数   管理员(上那四遍机的各种管理员都列出来)

兑现指标:从表第11中学取全部职员列表,从表第22中学取上机次数和管理员.

        if @flag = 0

解答:
测量检验用例

               5   在路上 2005-1-1  李大伟

    begin

编辑:数据库 本文来源:删除本地及海关单证待分派表、报关单表中的数

关键词: 澳门新濠3559