select max(cast(col1 as varchar(50))) as col1,max(col2) as col2,SupplierCo
from
(
select newid() as col1,suppliercode,'0' as col2,SupplierCo from tbl_AppSupplier T
where SupplierCo in
(
select distinct SupplierCo from tbl_AppSupplier a
where not exists (select 1 from tbl_AppSupplier
where SupplierCo=a.SupplierCo and suppliertype=0)
)
) a group by SupplierCo 我做了个例子,应该可以的!
/*-----去掉[create]和[union]的括号----*/
[create] table t
(col varchar(50))
insert t
select 'EUNOS BRANCH PTE LTD' [union] all
select 'MG AUTOMOTIVE PTE LTD' [union] all
select 'SUPER CITY PTE LTD' [union] all
select 'EUNOS BRANCH PTE LTD' [union] all
select 'SUPER CITY PTE LTD' [union] all
select 'liu_1' select max(cast(t1 as varchar(100))),min(t2),col
from
(
select newid() as t1,'0' as t2,col from t
) a group by coldrop table t---------------------------------------------------------------------------------------------------- ---- --------------------------------------------------
BCE3B17F-BCFF-4D10-A3D9-6C108B92F4EE 0 EUNOS BRANCH PTE LTD
28CDAB78-1A1E-4D3D-AAAE-C197620AFF37 0 liu_1
07B03505-05CD-4B41-991C-7E97A079DB5A 0 MG AUTOMOTIVE PTE LTD
785AEE01-613A-4CAE-A042-41BD979787C7 0 SUPER CITY PTE LTD(所影响的行数为 4 行)
from
(
select newid() as col1,suppliercode,'0' as col2,SupplierCo from tbl_AppSupplier T
where SupplierCo in
(
select distinct SupplierCo from tbl_AppSupplier a
where not exists (select 1 from tbl_AppSupplier
where SupplierCo=a.SupplierCo and suppliertype=0)
)
) a group by SupplierCo 我做了个例子,应该可以的!
/*-----去掉[create]和[union]的括号----*/
[create] table t
(col varchar(50))
insert t
select 'EUNOS BRANCH PTE LTD' [union] all
select 'MG AUTOMOTIVE PTE LTD' [union] all
select 'SUPER CITY PTE LTD' [union] all
select 'EUNOS BRANCH PTE LTD' [union] all
select 'SUPER CITY PTE LTD' [union] all
select 'liu_1' select max(cast(t1 as varchar(100))),min(t2),col
from
(
select newid() as t1,'0' as t2,col from t
) a group by coldrop table t---------------------------------------------------------------------------------------------------- ---- --------------------------------------------------
BCE3B17F-BCFF-4D10-A3D9-6C108B92F4EE 0 EUNOS BRANCH PTE LTD
28CDAB78-1A1E-4D3D-AAAE-C197620AFF37 0 liu_1
07B03505-05CD-4B41-991C-7E97A079DB5A 0 MG AUTOMOTIVE PTE LTD
785AEE01-613A-4CAE-A042-41BD979787C7 0 SUPER CITY PTE LTD(所影响的行数为 4 行)
delete from #temp1
where newfield not in(
select min(newfield) from #temp1 group by SupplierCo
)alter table #temp1 drop column newfield
select * from #temp1
order by SupplierCoinsert into tbl_AppSupplier select * from #temp1drop table #temp1我把查询的结果防到临时表#temp里面,然后把重复的数据删除,但是提示
服务器: 消息 207,级别 16,状态 3,行 13
列名 'newfield' 无效。
服务器: 消息 207,级别 16,状态 1,行 13
列名 'newfield' 无效。但是我单步执行使好的,请问这是怎么回事呢?
select distinct SupplierCo,'0' ,newid() from tbl_AppSupplier a
where not exists (select 1 from tbl_AppSupplier
where SupplierCo=a.SupplierCo and suppliertype=0)
select newid(), SupplierCo,'0' from tbl_AppSupplier a
group by SupplierCo having not exists (select 1 from tbl_AppSupplier
where SupplierCo=a.SupplierCo and suppliertype=0)
列名 'newfield' 无效。
服务器: 消息 207,级别 16,状态 1,行 13
列名 'newfield' 无效。是不是临时表不能修改表结构呢?
,*
from
(
select suppliercode
,'0' as 'type'
,SupplierCo
from tbl_AppSupplier T
where SupplierCo in
(
select distinct SupplierCo
from tbl_AppSupplier a
where not exists (select 1
from tbl_AppSupplier
where SupplierCo=a.SupplierCo
and suppliertype=0)
)
)t
delete from #temp1
where newfield not in(
select min(newfield) from #temp1 group by SupplierCo
)
=====>改为:
alter table #temp1 add newfield int identity(1,1)
go
delete from #temp1
where newfield not in(
select min(newfield) from #temp1 group by SupplierCo
)
--原因,修改表后必须立即提交才会生效(即后面加GO),而单步执行,就相当于加GO
服务器: 消息 545,级别 16,状态 1,行 1
当 IDENTITY_INSERT 设置为 ON 时,必须指定表 'tbl_AppSupplier' 中标识列的显式值这又是什么问题呢,请高手帮忙!
set IDENTITY_INSERT tbl_AppSupplier ON
也没有用,我怎么样把#temp1中的数据插入表tbl_AppSupplier中呢?
insert into tbl_AppSupplier (字段1,字段2,...) select 字段1,字段2,... from #temp1
--原因:当有自增量时,且 IDENTITY_INSERT 设置为 ON 时,必须列出字段名,不能省略。
Create Procedure sp_Change_data asselect newid() as id1,suppliercode,'0' as type,LocationCode,SupplierCo,Address1,Address2,Address3,PostalCode1, PostalCode2,PostalCode3,GSTRegNo,ContactPerson,Title,OfficeNo,Email,MobileNo,FaxNo,PaymentTerm,APAccountcode,CurrencyCode,GSTCategoryCode,Creator,CreateDate,LastModifier,LastModifyDate into #temp1
from tbl_AppSupplier T
where SupplierCo in
(
select distinct SupplierCo from tbl_AppSupplier a
where not exists (select 1 from tbl_AppSupplier
where SupplierCo=a.SupplierCo and suppliertype=0)
)alter table #temp1 add newfield int identity(1,1)
godelete from #temp1
where newfield not in(
select min(newfield) from #temp1 group by SupplierCo
)alter table #temp1 drop column newfield
goselect * from #temp1
order by SupplierCo
insert into tbl_AppSupplier select * from #temp1drop table #temp1里面用到临时表,我创建存储过程时,提示对象名#temp1无效,这个有办法解决吗?