我只写到了查出名称相同的记录找出来:
SELECT a.*
FROM tbl_AppSupplier a INNER JOIN
(SELECT id, suppliercode
FROM tbl_AppSupplier
GROUP BY id, suppliercode
HAVING COUNT(*) > 1) b ON a.ID = b.id AND
a.SupplierCode = b.suppliercode请各位帮忙!
SELECT a.*
FROM tbl_AppSupplier a INNER JOIN
(SELECT id, suppliercode
FROM tbl_AppSupplier
GROUP BY id, suppliercode
HAVING COUNT(*) > 1) b ON a.ID = b.id AND
a.SupplierCode = b.suppliercode请各位帮忙!
2.select Suppliername into #t from tbl_appSupplier b group by Suppliername having count(*)>=2
3.insert into tbl_appSupplier
select Suppliername,id,'0' from tbl_appSupplier group by Suppliername,id having Suppliername in (select * from #t)
WHERE (SupplierType = 0) AND (SupplierCode IN
(SELECT a.suppliercode
FROM tbl_AppSupplier a INNER JOIN
(SELECT id, suppliercode
FROM tbl_AppSupplier
GROUP BY id, suppliercode
HAVING COUNT(*) > 1) b ON a.ID = b.id AND
a.SupplierCode = b.suppliercode))我用以上的语句已经把等于0的删除了,下面怎么样插入数据呢?
----------------
随机产生或者等于0即可
WHERE (SupplierType = 0) AND (SupplierCode IN
(SELECT a.suppliercode
FROM tbl_AppSupplier a INNER JOIN
(SELECT id, suppliercode
FROM tbl_AppSupplier
GROUP BY id, suppliercode
HAVING COUNT(*) > 1) b ON a.ID = b.id AND
a.SupplierCode = b.suppliercode))if @@rowcount=0else
....这样可以吗?
一般引用系统的全局变量,是自定义一个变量,然后将全局变量的值付给自定义变量,再使用。
或者你直接这样判断:
if @@rowcount=0else
....
delete a
from @t a
where not exists(select 1 from @t where Suppliername=a.Suppliername
and a.suppliertype<>0)--插入
insert into @t
select Suppliername,
ID,
suppliertype=0
from @t
Suppliername ID suppliertype
Supp 1 0 Supp 2 1 Supp 3 1把suppliertype=0的数据删除,然后根据suppliertype<>0的记录的id插入数据呢?
变为:
Suppliername ID suppliertype Supp 2 0 Supp 3 0 Supp 2 1 Supp 3 2
Suppliername ID suppliertype
Supp 1 0 Supp 1 1这样只要删除suppliertype=0的数据即可,不要再插入了.
select Suppliername into #t from tbl_appSupplier b group by Suppliername having count(*)>=2
--删除符合条件的记录
delete * from tbl_appSupplier a where suppliertype=0 and Suppliername in (select Suppliername from tbl_appSupplier b group by Suppliername having count(*)>=2)
--插入记录
insert into tbl_appSupplier
select Suppliername,id,'0' from tbl_appSupplier group by Suppliername,id having Suppliername in (select * from #t) 这样3句不可以吗?