SQL 链接字符串

链接字符串： A.B.C or A.C

Sql Server:

create DataBase TestDemo;
use TestDemo

Create 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 Test1
where 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 Table1

select Table1.a +
(case when Table1.b is null then ' ' else '.' + Table1.b end) 
+ '.' +Table1.c from Table1

结果：

(No column name)
First.Middle.Third
First1.Middle.Third
First2 .Third
First3 .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]

GO

INSERT 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)

Go

select 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 name

name    数学    英语    语文
李四    70    0    80
王五    75    80    57
张三    0    100    60

select 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 name

name    数学    英语    语文
李四    70    0    80
王五    75    80    57
张三    0    100    60

 

引申Sql case when then else end的用法：

 http://www.cnblogs.com/kevin2013/archive/2010/07/02/1769682.html

 

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;