金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 非常有用的sql脚本,应用的经典案例

非常有用的sql脚本,应用的经典案例

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:08

/* 步骤1

问题:  

/*sql 语法学习*/

搜索表

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

金沙棋牌app手机下载,假设环境如下:

/*函数的学习---------------------------------------*/

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 count(*) from W_DOCUMENTS;
select count(*) from W_DOCUMENTS_TEST;
select count(*) from W_DECLARATIONS_TEST;

    表1:      ID, NAME,      QQ,     PHONE,

获取当前时间(时/分/秒):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()))

 发帖表

描述帖子发布的相关信息

*/

表中数据:      1       秦云        10102800 13500000

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

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;

-


/* 步骤2

                2       在路上      10378    13600000

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

 评论表

调用带输入参数的存储过程
GenBGDH
条件2 I_E_FLAG,
条件1 TRAF_MODE,
固定前4位的单证号 TMP_ENTRY_ID
航次 VOYAGE_NO,
船名 TRAF_NAME,
提单号 BILL_NO,
是否拼箱、联单(1:是|0:是) @flag,
条数(数字范围:0~32767) @count

                3       LEO         10000    13900000

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

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;

-


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

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

获取当前时间:print current_timestamp

回复表

*/

表中数据:     1   秦云    2004-1-1  李大伟

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

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

/* 步骤3

               2   秦云    2005-1-1  马化腾

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

匹配生成单证规则的数量
条件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

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


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

               4    秦云   2005-1-1  李大伟

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

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

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

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

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

实现目的:从表1中取所有人员列表,从表2中取上机次数和管理员.

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

*/

             上机人员名单    上机次数   管理员(上这几次机的每个管理员都列出来)

--------------------------------自动收缩数据库end-----------------------------

/* 步骤4

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

-------------------------------去除首尾无效的字符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--------------------------

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

               在路上           2            马化腾,李大伟

------------删除数据库中的重复记录(且仅保留一条有效记录)示例-----------------
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

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;

               LEO              0      

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

*/

如果不算管理员那一列的话,我是这样写的。

--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)

/*
步骤5
更新本地单证待分派表中其他列数据
将OP_DATE字段的时间改成与当前系统时间相差为1个小时以内的时间

SELECT  表1.NAME AS 姓名, COUNT(表2.ID) 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)

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;

FROM  表1 LEFT OUTER JOIN

select * from A
drop table A

*/

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

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

/*
其他
2个时间之间相差的秒数

GROUP BY 表1.名称

 

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

解答:
测试用例

-------------------------------迭归的应用(找起点和终点之间的路径-----------------------------
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

查询已成功分派了多少条单证

create table 表1( --drop table 表1

--创建函数
--函数返回一个表,根据实际情况的不同一层一层的插入,可以充分利用生成的表
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

select count(*) from W_DOCUMENTS;

ID     int,

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

查询报关单表中是否有重复生成的单证号数据

NAME   varchar(10),

--删除环境
drop function f_go
drop table t

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

QQ     varchar(10),

-------------------------------迭归的应用(找起点和终点之间的路径-----------------------------

查询海关单证待分派表中是否有重复生成的单证号数据

PHONE  varchar(20)

 

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

)

 

*/

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

--------按类别去最新的前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

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

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

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

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

create table 表2( --drop table 表2

--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------

ID        int,

 

NAME    varchar(10) ,

-------------同上,按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------
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

上机时间  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

管理员    varchar(10)

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

)

drop table tb

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

drop function dbo.fc_str

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

-------------按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------

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

 

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

 

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

--取各个类的前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

程序部分

--分组统计,统计每个段中数据的个数
--一般成绩统计可以用到这个
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

create function GetNameStr(@name nvarchar(10))

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

returns nvarchar(800)

 

as

----------------------------在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

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

    declare @nameStr nvarchar(800)

 

    declare @tempStr nvarchar(800)

    declare @flag int

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

    open myCur

    fetch next from myCur into @tempStr

    set @flag = 0

    while @@fetch_status = 0

    begin

        if @flag = 0

    begin

        set @nameStr = @tempStr

    end

    else

    begin

        set @nameStr = @nameStr + ',' + @tempStr

    end

    set @flag = @flag + 1

    fetch next from myCur into @tempStr

    end

    close myCur

    deallocate myCur

    return @nameStr

end

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

from 表2

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

group by 表2.NAME

测试结果:

姓名    上机次数    管理员


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

在路上      2       马化腾,李大伟

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:非常有用的sql脚本,应用的经典案例

关键词:

上一篇:没有了

下一篇:没有了