插入数据判断

buzhunmeiyouni 2017-10-31 02:07:10

判断a表里的课程的值，是否在表里的fname里有重复的，没有就插入

if not exists(select 1 from t_item_3003 t3,ta a where t3.FName=a.课程 )
begin
insert into t_item_3003 values((select MAX(fitemdi) from t_item_3003 )+ ROW_NUMBER() over(order by fitemid) as fitemid,
(select right('00000000'+fnumber,3) from t_Item_3003),
(select 课程 from ta),5000,83776)
end

这样写为什么不行啊

...全文

397 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

buzhunmeiyouni 2017-11-01

打赏
举报

引用 10 楼 RINK_1 的回复:

按以下试试，


insert into t_item_3003
select fitemid+ROW_NUMBER() over (order by 编号),
       right('000'+CAST(fnumber+ROW_NUMBER() over (order by 编号) as varchar),3),
       课程,
       5000,
       83776
from 
(select distinct 课程,编号
from ta
where 课程 not in
(select Fname from t_item_3003)) as A
join (select MAX(fitemid) as fitemid,
             MAX(cast(fnumber as int)) as fnumber
      from t_item_3003) as B on 1=1

如果把5000换成表一里金额字段里的值需要怎么改

buzhunmeiyouni 2017-11-01

打赏
举报

引用 10 楼 RINK_1 的回复:

按以下试试，


insert into t_item_3003
select fitemid+ROW_NUMBER() over (order by 编号),
       right('000'+CAST(fnumber+ROW_NUMBER() over (order by 编号) as varchar),3),
       课程,
       5000,
       83776
from 
(select distinct 课程,编号
from ta
where 课程 not in
(select Fname from t_item_3003)) as A
join (select MAX(fitemid) as fitemid,
             MAX(cast(fnumber as int)) as fnumber
      from t_item_3003) as B on 1=1

非常可以

RINK_1 2017-11-01

打赏
举报

引用 12 楼 buzhunmeiyouni 的回复:

[quote=引用 10 楼 RINK_1 的回复:] 按以下试试，


insert into t_item_3003
select fitemid+ROW_NUMBER() over (order by 编号),
       right('000'+CAST(fnumber+ROW_NUMBER() over (order by 编号) as varchar),3),
       课程,
       5000,
       83776
from 
(select distinct 课程,编号
from ta
where 课程 not in
(select Fname from t_item_3003)) as A
join (select MAX(fitemid) as fitemid,
             MAX(cast(fnumber as int)) as fnumber
      from t_item_3003) as B on 1=1

如果把5000换成表一里金额字段里的值需要怎么改[/quote]


insert into t_item_3003
select fitemid+ROW_NUMBER() over (order by 编号),
       right('000'+CAST(fnumber+ROW_NUMBER() over (order by 编号) as varchar),3),
       课程,
       金额,
       83776
from 
(select 课程,max(编号) as 编号,SUM(金额) as 金额
from ta
where 课程 not in
(select Fname from t_item_3003)
group by 课程) as A
join (select MAX(fitemid) as fitemid,
             MAX(cast(fnumber as int)) as fnumber
      from t_item_3003) as B on 1=1

RINK_1 2017-10-31

打赏
举报

按以下试试，


insert into t_item_3003
select fitemid+ROW_NUMBER() over (order by 编号),
       right('000'+CAST(fnumber+ROW_NUMBER() over (order by 编号) as varchar),3),
       课程,
       5000,
       83776
from 
(select distinct 课程,编号
from ta
where 课程 not in
(select Fname from t_item_3003)) as A
join (select MAX(fitemid) as fitemid,
             MAX(cast(fnumber as int)) as fnumber
      from t_item_3003) as B on 1=1

buzhunmeiyouni 2017-10-31

打赏
举报

引用 8 楼 appetizing_fish1 的回复:

[quote=引用 7 楼 buzhunmeiyouni 的回复:] [quote=引用 6 楼 appetizing_fish1 的回复:] 这样写不可以吗
INSERT INTO t_item_3003 
Select * From ta Where [代码] not in (Select FName From t_item_3003)

不行的[/quote] 不知你的意思, 你直接将你的目的描述出来, 最好提供测试数据. [/quote] 就是把第一个表的课程那一个字段的数据插入到第二个表里，重复的不插入，第二个表的前两列都是要最大值加一

顺势而为1 2017-10-31

打赏
举报

引用 7 楼 buzhunmeiyouni 的回复:

[quote=引用 6 楼 appetizing_fish1 的回复:] 这样写不可以吗
INSERT INTO t_item_3003 
Select * From ta Where [代码] not in (Select FName From t_item_3003)

不行的[/quote] 不知你的意思, 你直接将你的目的描述出来, 最好提供测试数据.

buzhunmeiyouni 2017-10-31

打赏
举报

引用 6 楼 appetizing_fish1 的回复:

这样写不可以吗



INSERT INTO t_item_3003 
Select * From ta Where [代码] not in (Select FName From t_item_3003)

不行的

顺势而为1 2017-10-31

打赏
举报

这样写不可以吗



INSERT INTO t_item_3003 
Select * From ta Where [代码] not in (Select FName From t_item_3003)

buzhunmeiyouni 2017-10-31

打赏
举报

这个表是excel导进数据库的

CREATE TABLE [dbo].[t_Item_3003](
[FItemID] [int] NOT NULL,
[FNumber] [varchar](80) NULL,
[FName] [varchar](255) NULL,
[F_101] [decimal](28, 10) NULL,
[F_103] [int] NULL,
PRIMARY KEY CLUSTERED
(
[FItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

二月十六 2017-10-31

打赏
举报

1、把t_item_3003表的fitemid改成自增长字段，不在用这种方式每次读取+1，让其自己增长每次加1即可。 2、子查询如果没有特别要求，都加一个top 1就不会报错，但是不知道这样是不是你想要的。 3、如果上边那种写法不对，建议楼主列出表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

buzhunmeiyouni 2017-10-31