链接字符串： A.B.C or A.C
Sql Server:
create DataBase TestDemo;use TestDemoCreate Table Test1( name varchar(1024), id int )insert into Test1 values('a12',1)insert into Test1 values('^12',2)insert into Test1 values('b12',3)insert into Test1 values('m12',4)insert into Test1 values('!m12',4)select * from Test1where name like '[ab]%'create TABLE Table1 ( a varchar(10), b varchar(10), c varchar(10), CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) ON [PRIMARY]Insert into Table1 values('First1','Middle', 'Third')Insert into Table1(a,c) values('First2','Third')Insert into Table1(a,c) values('First3','Third')select * from Table1select Table1.a +(case when Table1.b is null then ' ' else '.' + Table1.b end) + '.' +Table1.c from Table1 结果：
(No column name)First.Middle.ThirdFirst1.Middle.ThirdFirst2 .ThirdFirst3 .Third
create TABLE [Test] ([id] [int] IDENTITY (1, 1) NOT NULL ,[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[Source] [numeric](18, 0) NULL ) ON [PRIMARY]GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Goselect name,sum(case subject when N'数学' then source else 0 end) as '数学',sum(case subject when N'英语' then source else 0 end) as '英语',sum(case subject when N'语文' then source else 0 end) as '语文'from test group by namename 数学 英语 语文李四 70 0 80王五 75 80 57张三 0 100 60select name,sum(case when subject = N'数学' then source else 0 end) as '数学',sum(case when subject = N'英语' then source else 0 end) as '英语',sum(case when subject = N'语文' then source else 0 end) as '语文'from test group by namename 数学 英语 语文李四 70 0 80王五 75 80 57张三 0 100 60
引申Sql case when then else end的用法：
MySQL:
select concat(TestJoin.a,(case when ISNULL(TestJoin.b) then "" else concat(".",TestJoin.b) end),(case when ISNULL(TestJoin.c) then "" else concat(".",TestJoin.c) end)) from TestJoin;