declare t1 cursor local for select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
--麻烦大侠填下!!! end
close t1
DEALLOCATE t1 原本数据: GGID YPPM YPSPM YPJX
561 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
521 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
要得到的效果: GGID YPPM YPSPM YPJX
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
522 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
不是修改啊!!!是向对象表YP_YPCJD里面添加数据!!!
但数据相同时取最大的GGID!
declare @GGID int
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
--麻烦大侠填下!!! end
close t1
DEALLOCATE t1 原本数据: GGID YPPM YPSPM YPJX
561 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
521 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
要得到的效果: GGID YPPM YPSPM YPJX
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
522 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
不是修改啊!!!是向对象表YP_YPCJD里面添加数据!!!
但数据相同时取最大的GGID!
相同就取最大的GGID!
大侠们麻烦下啊!!!!!!
但是是根据YP_YPGGD表里的GGID来添加数据的啊1
找到相同的YPPM YPSPM YPJX
就取最大的GGID然后在添加啊1
没有就直接添加进表!
select * from tb t
where not exists(select 1 from tb t where ggid>t.ggid and YPPM =t.YPPM and YPSPM=t. YPSPM and YPJX =t.YPJX)
declare t1 cursor local for
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
select MAXGGID=max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX)
insert into YP_YPCJD
values(MAXGGID,@GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM )
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
close t1
DEALLOCATE t1
declare @YP_YPCJD table( GGID int,YPPM varchar(20),YPSPM varchar(20),YPJX int)declare @tb table( GGID int,YPPM varchar(20),YPSPM varchar(20),YPJX int)
insert @tb select
561 , '瞿麦' , '瞿麦', 10 union all select
857 , '瞿麦' , '瞿麦' , 10 union all select
521 , '普萘洛尔' , '普萘洛尔', 12 union all select
522 , '普萘洛尔', '普萘洛尔' , 12
insert @YP_YPCJD
select ggid=(select max(ggid) from @tb where YPPM=t.YPPM),YPPM,YPSPM ,ypjx from @tb tselect * from @YP_YPCJDGGID YPPM YPSPM YPJX
----------- -------------------- -------------------- -----------
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
522 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12(4 行受影响)这样也可以插入啊,能少用游标,就少用
declare t1 cursor local for
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
select @MAXGGID=max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX) -----剛才此處少了@
insert into YP_YPCJD
values(@MAXGGID,@GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM )
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
close t1
DEALLOCATE t1
消息 102,级别 15,状态 1,第 16 行
')' 附近有语法错误。
消息 128,级别 15,状态 1,第 18 行
在此上下文中不允许使用名称 "GGID"。有效表达式包括常量、常量表达式和变量(在某些上下文中)。不允许使用列名。
大哥错了!
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
GGID INT,
YPPM VARCHAR(50),
YPSPM VARCHAR(50),
YPJX VARCHAR(50)
)
INSERT INTO TB
SELECT 561, '瞿麦', '瞿麦', '10' UNION ALL
SELECT 857, '瞿麦', '瞿麦', '10' UNION ALL
SELECT 521, '普萘洛尔', '普萘洛尔', '12' UNION ALL
SELECT 522, '普萘洛尔', '普萘洛尔', '12'
SELECT GGID
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPPM
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPSPM
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPJX
FROM TB T
/*
561 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
521 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
*/
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
if @GGID=(select max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX)
insert YP_YPCJD select @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
close t1
DEALLOCATE t1
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
set @GGID=(select max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX)
insert YP_YPCJD(GGID,YPPM,YPSPM,YPJX) select @GGID,@YPPM,@YPSPM,@YPJX
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
CLOSE t1
DEALLOCATE t1 修改一下。
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
GGID INT,
YPPM VARCHAR(50),
YPSPM VARCHAR(50),
YPJX VARCHAR(50)
)
INSERT INTO TB
SELECT 561, '瞿麦', '瞿麦', '10' UNION ALL
SELECT 857, '瞿麦', '瞿麦', '10' UNION ALL
SELECT 521, '普萘洛尔', '普萘洛尔', '12' UNION ALL
SELECT 522, '普萘洛尔', '普萘洛尔', '12'
SELECT
(SELECT MAX(GGID) FROM TB T2 WHERE T2.YPPM=T.YPPM ) GGID
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPPM YPPM
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPSPM YPSPM
,SPACE((SELECT COUNT(1) FROM TB T2 WHERE T2.YPPM=T.YPPM AND T2.GGID<T.GGID))+YPJX YPJX
FROM TB T
/*
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
522 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
*/
这样我重复的数据怎么会被过滤掉!!!
我想的是2条数据对应同一个GGID号!
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
IF @GGID=(select max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX)
BEGIN
insert YP_YPCJD(GGID,YPPM,YPSPM,YPJX) select @GGID,@YPPM,@YPSPM,@YPJX
END
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
CLOSE t1
DEALLOCATE t1 这样吗,你后面要的跟你前面讲的是两码事,看看你那个要得到的结果。
561 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
521 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12
要得到的效果: GGID YPPM YPSPM YPJX
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10
522 普萘洛尔 普萘洛尔 12
522 普萘洛尔 普萘洛尔 12 是2条数据对应一个GGID我哪里说错了吗?
大哥!!!
857 瞿麦 瞿麦 10
857 瞿麦 瞿麦 10 这条不重复吗,那你说的重复要怎么理解?
我是说一样的2条数据对应一个GGID 因为2条数据的厂家可能不同!
你的SQL没错只是在添加的时候把数据相同的给过滤掉了!
效果是这样的有2条一样的数据对应的是同一GGID号!
当我选择GGID号时我就能找到2条记录了!
大哥你在帮帮忙!
select GGID,YPPM,YPSPM,YPJX,HLDW,BZDW,YPGG,PYM from YP_YPGGD open t1
declare @GGID int ,@GGID1
declare @MAXGGID int ,@GGID1
declare @YPPM varchar(200)
declare @ypspm varchar(200)
declare @YPJX varchar(200)
declare @HLDW int
declare @BZDW varchar(200)
declare @YPGG varchar(200)
declare @PYM char(50)
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
while @@FETCH_STATUS=0
begin
BEGIN
SET @GGID=(select max(GGID) from YP_YPGGD where YPPM=@YPMM and @YPSPM=YPSPM and @YPJX=YPJX) insert YP_YPCJD(GGID,YPPM,YPSPM,YPJX) select @GGID,@YPPM,@YPSPM,@YPJX
END
fetch next from t1 into @GGID,@YPPM,@YPSPM,@YPJX,@HLDW,@BZDW,@YPGG,@PYM
end
CLOSE t1
DEALLOCATE t1 贴一下这段代码出来的结果