sql server2022如何进行代码调试 sql server 代码

转载

数据科学家 2024-04-02 10:37:04

文章标签 sql server insert delete table character 文章分类 SQL Server 数据库

问题1：现有两个表TB1,TB2,有如下数据

TB1
FD SP
A 111
B 222
C 222
D 333
E 333
. ...

TB2
SP
111
222
333
...
我想把TB2表的数据插入到TB1表,TB1.FD每个值都要有TB2.SP的值,但不要重复插入
结果如下
TB1
FD SP
A 111
A 222
A 333
B 111
B 222
B 333
...

代码1：
if object_id('tb1') is not null drop table tb1 
go 
create table tb1([FD] varchar(10),[SP] int)
insert tb1 select 'A',111
union all select 'B',222
union all select 'C',222
union all select 'D',333
union all select 'E',333
go
if object_id('tb2') is not null drop table tb2 
go 
create table tb2([SP] int)
insert tb2 select 111
union all select 222
union all select 333
go

insert tb1
select *
from (Select * from (select distinct fd from tb1) a,tb2) as t
where not exists(select 1 from tb1 where fd=t.fd and sp=t.sp)

select * from tb1 order by fd 
代码2：
create table #Tb1(FD nvarchar(2), sp nvarchar(10))
insert #tb1 values('A'    ,'111') 
insert #tb1 values('B'    ,'222') 
insert #tb1 values('C'    ,'222') 
insert #tb1 values('D'    ,'333') 
insert #tb1 values('E'    ,'333') 


create table #Tb2(Sp nvarchar(10))
insert #tb2 values('111') 
insert #tb2 values('222') 
insert #tb2 values('333') 

insert #tb1
select a.FD,b.sp from #tb1 a join #tb2 b on a.sp <> b.sp
order by a.fd

select * from #tb1 order by fd,sp 
代码3：
create table #tb1([FD] varchar(10),[SP] int)
insert #tb1 select 'A',111
union all select 'B',222
union all select 'C',222
union all select 'D',333
union all select 'E',333
go

create table #tb2([SP] int)
insert #tb2 select 111
union all select 222
union all select 333
go

insert into #tb1 select * from
(
  select b1.FD,b2.sp as sp2 from #tb1 b1,#tb2 b2 where b1.[SP] not in (b2.[SP])
)T

select * fromorder by FD,[SP]

问题2：

一个元器件表，向表中添加元器件，若表中已有，则不添加，若没有，则添加并自动编号。元器件属性有：名称，类型，封装方式，这三个属性有一个不一样就代表是新的一个元器件。自动编号是指：找出一个分类中的最大编号，如“电阻类”，最大编号R0010，则新加的自动编号为R0011。根据问题描述，编写一个存储过程。

代码一：
create proc pr_test
@name varchar(20),
@type varchar(10),
@package varchar(10)
as
begin
    if not exists(select 1 from [device] where [name] = @name and [type] = @type and package = @package)
    begin
        declare @maxid varchar(100)select @maxid = max(id) from [device] where [type] = @type
        set @maxid = left(@maxid,1)+ right('0000'+ltrim(cast(stuff(@maxid,1,1,'') as int) + 1),4)  
        insert [device] select @maxid,@name,@type,@package
    end
end其中：left(character_expression,integer_expression)：返回从字符串左边开始指定个数的字符；
         RIGHT(character_expression,integer_expression)：返回从字符串右边开始指定个数的字符；
         ltrim(character_expression)：删除起始空格后返回字符串表达式；
         cast(<expression> as <data_type> [length])：用以转换数据类型；
         stuff(<character_expression1>,<start_position>,<length>,<charavter_expression2>)：用另一子串代替字符串指定位置、长度的子串，(如果起始位置或长度值为负，或者起始位置大于表达式1的长度，择返回null，如果长度大于表达式1中起始位置以右的长度，则表达式1只保留首字符)；
 
代码2：
--用触发器也可以。
--假定元器件表为yqjb
create trigger trig_insert_yqjb on yqjb
instead of  insert as
declare @no varchar(10)
if not exists(select 1 from inserted a,yqjb b where a.名称=b.名称 and a.类型=b.类型 and a.封装方式=b.封装方式)
begin
       set  @no=(select max(编号) from yqjb where 类型 in (select 类型 from inserted))
       set @no=left(@no,1)+right('0000'+ltrim(cast(right(@no,4) as int)+1),4)
       insert yqjb(编号,名称,类型,封装方式) select @no,名称,类型,封装方式 from inserted
end代码3：
create proc INSERT_DATA 
@name varchar(20), 
@type varchar(10), 
@package varchar(10) 
as 
begin 
    if not exists(select 1 from 表 where 名称 = @name and 类型 = @type and 封装方式 = @package) 
    begin 
        declare @maxID varchar(100) 
        declare @nextID varchar(100) 
set @maxID='' 
        set @nextID='' 
        select @maxID = max([自动ID号]) from 表 where 类型 = @type 
        set @nextID = left(@maxID,1) 
        set @maxID=convert(varchar(10),(convert(int,right(@maxID,len(@maxID)-1))+1)) 
        if len(@maxID)=3 set @maxID="0"+@maxID 
        if len(@maxID)=2 set @maxID="00"+@maxID 
        if len(@maxID)=1 set @maxID="000"+@maxID 
        set @nextID=@nextID+@maxID 
        insert into 表 select @nextID,@name,@type,@package 
    end 
end

说明：上面代码只复制了表的结构，不复制表内容。这种代码在需要建很多结构相同的表的时候很有用。

问题3：

假如表a已经存在，现在要建一个新表，其表结构和其中的字段名称、属性都与表a相同，只有表名不同。则可以采用以下代码完成表结构的复制。

select * into b from a where 1<>1

或者：select top 0 * into b from a

问题4：某表有一个时间字段Sdt，要按照时间字段查找表中最后一条记录。代码如下：

SELECT TOP 1 * FROM TB ORDER BY Sdt DESC

问题5：要查找表中指定的第n至m条记录，参考代码如下：

N-M条记录
1.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname descset rowcount  0


2.
select top n * from 
(select top m * from tablename order by columnname) a
order by columnname desc


3.
如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename

取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m

如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true

4.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m问题5：处理重复的记录（查询和删除）
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go


--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/



--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)

方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)



select * from #T

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID保留最大的一条记录:

方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
delete a  from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)

方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)


select * from #T
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/





--3、删除重复记录没有大小关系时，处理重复值


--> --> (Roy)生成測試數據

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#

truncate table #T--清空表

insert #T select * from #    --把临时表#插入到表#T中

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

--重新执行测试数据后用方法2
方法2:

alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列

--查看结果
select * from #T

/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)

*/

--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin 
    set rowcount @con;
    delete #T where Num=@Num and Name=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor

--查看结果
select * from #T
/*
Num         Name
----------- ----
1           A
2           B

(2 行受影响)
*/

问题5：有个表如下

id projectid username
1 项目1 张三
2 项目1 张四
3 项目2 张三
4 项目3 张三
5 项目3 张四
6 项目4 张三
7 项目5 张四

我想得到最后结果

id username 参与项目
1 张三 4个项目
2 张四 4个项目

请问这个SQL该怎么写？

--建立表变量

DECLARE @TB TABLE([id] INT, [projectid] NVARCHAR(3), [username] NVARCHAR(2))
INSERT @TB
SELECT 1, N'项目1', N'张三' UNION ALL
SELECT 2, N'项目1', N'张四' UNION ALL
SELECT 3, N'项目2', N'张三' UNION ALL
SELECT 4, N'项目3', N'张三' UNION ALL
SELECT 5, N'项目3', N'张四' UNION ALL
SELECT 6, N'项目4', N'张三' UNION ALL
SELECT 7, N'项目5', N'张四'

--代码1
select username,cast(count(*) as varchar(5))+'个项目' as 参与项目
from @TB 
group by username 
--代码2
select id=identity(int,1,1), username,参与项目=cast(count(projectid) as varchar(5)) +'个项目' into #temp from @TB group by username
select * from #temp
drop table #temp 
--代码3
SELECT Min(id) AS id,
    username,
    RTRIM(COUNT(*)) + '个项目' AS 参与项目
FROM @tb
GROUP BY username

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。