34,591
社区成员
发帖
与我相关
我的任务
分享
SELECT ALevel=
CASE
when Level1=Level2 and Level1=Level3 THEN Level1
when Level1=Level2 and Level1!=Level3 THEN Level1+Level3
WHEN Level2=Level3 THEN Level1+Level2
ELSE Level1+Level2+Level3
END
FROM T_Zone_Inf
USE tempdb
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TestTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable](
[field1] [varchar](10) NOT NULL,
[field2] [varchar](10) NOT NULL,
[field3] [varchar](10) NOT NULL
)
GO
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('安徽省','安庆市','安庆市')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('安徽省','蚌埠市','蚌埠市')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('安徽省','亳州市','亳州市')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('安徽省','巢湖市','巢湖市')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('北京市','北京市','海淀区')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('安徽省','滁州市','定远县')
INSERT INTO [TestTable]([field1],[field2],[field3]) VALUES ('陕西省','商洛市','镇安县')
SELECT * FROM [TestTable]
SELECT field1 + ISNULL(CASE WHEN field1 <> field2 THEN field2 END,'') + ISNULL(CASE WHEN field2 <> field3 THEN field3 END,'') FROM [TestTable]
select replace(replace(Level1,Level2,'')+Level2,Level3,'')+Level3 from @tab
declare @tab table
(
Level1 varchar(10),
Level2 varchar(10),
Level3 varchar(10)
)
insert @tab select '安徽省','安庆市','安庆市'
union select '安徽省','蚌埠市','蚌埠市'
union select '安徽省','亳州市','亳州市'
union select '安徽省','巢湖市','巢湖市'
union select '北京市','北京市','海淀区'
union select '安徽省','滁州市','定远县'
union select '陕西省','商洛市','镇安县'
union select '陕西省','商洛市','陕西省'
--select * from @tab
select replace(replace(replace(Level1+Level2+Level3,Level1,'')+Level1,Level2,'')+Level2,Level3,'')+Level3 from @tab
select Level1+replace(level2,level1,'')+replace(replace(level3,level2,''),level1,'') from @tab