select * from tb a where exists(select 1 from tb where name=a.name)
不知是不是这样create table #t(id varchar(10),name varchar(10)) insert into #t select '1','aaa' insert into #t select '2','agc' insert into #t select '3','aaa' delete from #t select a.id+','+b.id from #t a, #t b where a.name = b.name and a.id < b.id
1,3(1 row(s) affected)
CREATE TABLE TB ( id INT IDENTITY(1, 1) , [name] VARCHAR(10) ) INSERT dbo.TB SELECT 'AAA' UNION ALL SELECT 'AGC' UNION ALL SELECT 'BBBB' SELECT id FROM dbo.TB WHERE LEN(REPLACE([name], LEFT([name], 1), '')) = 0DROP TABLE dbo.TB /*id ----------- 1 3(2 個資料列受到影響)*/
--创建一个函数 CREATE FUNCTION [dbo].[ReturnIfAlike] ( @Value VARCHAR(100) ) RETURNS INT AS BEGIN DECLARE @IfAlike INT, @A INT, @LEN INT, @B VARCHAR(1)
SELECT @A=1, @LEN=LEN(@Value), @B=LEFT(@Value,1) WHILE @A<=@LEN BEGIN IF SUBSTRING(@Value,@A,1)=@B BEGIN SET @A=@A+1 SET @IfAlike=0 END ELSE BEGIN SET @A=@LEN+1 SET @IfAlike=-1 END END RETURN @IfAlikeENDcreate table t1 ( id int, val varchar(10) ) insert into t1 select 1, 'AAA' union all select 2, 'AGC' union all select 3, 'BBBB' select * from t1SELECT * FROM t1 WHERE dbo.returnifalike(val)=0
你那个表里,1,3 的NAME 不相同!
insert into #t select '1','aaa'
insert into #t select '2','agc'
insert into #t select '3','aaa'
delete from #t select a.id+','+b.id from #t a, #t b where a.name = b.name and a.id < b.id
1,3(1 row(s) affected)
(
id INT IDENTITY(1, 1) ,
[name] VARCHAR(10)
)
INSERT dbo.TB
SELECT 'AAA'
UNION ALL
SELECT 'AGC'
UNION ALL
SELECT 'BBBB'
SELECT id
FROM dbo.TB
WHERE LEN(REPLACE([name], LEFT([name], 1), '')) = 0DROP TABLE dbo.TB
/*id
-----------
1
3(2 個資料列受到影響)*/
aaaa(都是a),bbb(都是b)
我的思路是 取字段的第一个字母,然后用REPLACE ( name, left(name,1), '') 第一个字母,如果结果
是空,则字母都相同
--创建一个函数
CREATE FUNCTION [dbo].[ReturnIfAlike]
(
@Value VARCHAR(100)
)
RETURNS INT
AS
BEGIN
DECLARE @IfAlike INT,
@A INT,
@LEN INT,
@B VARCHAR(1)
SELECT @A=1,
@LEN=LEN(@Value),
@B=LEFT(@Value,1) WHILE @A<=@LEN
BEGIN
IF SUBSTRING(@Value,@A,1)=@B
BEGIN
SET @A=@A+1
SET @IfAlike=0
END
ELSE
BEGIN
SET @A=@LEN+1
SET @IfAlike=-1
END
END RETURN @IfAlikeENDcreate table t1
(
id int,
val varchar(10)
)
insert into t1
select 1, 'AAA' union all
select 2, 'AGC' union all
select 3, 'BBBB'
select * from t1SELECT * FROM t1 WHERE dbo.returnifalike(val)=0