select case when len(username)<=1 then useremail else 'xxx' end from ...
用这个函数也可以datalength select case when datalength(username)<=1 then useremail else 'xxx' end from
datalength是字符数 len 是字节数
判斷字段username的長度<=1時取useremail:select username case len(username) <=1 then useremail else username end from table_name
謝謝,select可以了, 兩表聯合的,如果判斷句在on裡面怎麼寫?我這樣寫出錯:on (case when len(B.username)<=1 then B.useremail = C.userid else B.username = C.userid end)出錯顯示: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
create table B ( username varchar(10), useremail varchar(50) )create table c ( userid varchar(10) )insert B select '','Test' insert B select 'SC','' insert c select 'Test' insert c select 'SC'select c.* from ( select useremail from B where len(B.username)<=1 union select username from B ) B inner join c on B.useremail=c.userid
orselect c.* from B inner join C on (len(B.username)<=1 and B.useremail = C.userid) or (B.username = C.userid)
select C*,username case when len(B.username) <=1 then B.username else B.username end from B inner join C on B.username=C.userid
from ...
select case when datalength(username)<=1 then useremail else 'xxx' end
from
len 是字节数
判斷字段username的長度<=1時取useremail:select username case len(username) <=1 then useremail else username end from table_name
兩表聯合的,如果判斷句在on裡面怎麼寫?我這樣寫出錯:on (case when len(B.username)<=1 then B.useremail = C.userid else B.username = C.userid end)出錯顯示:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
(
username varchar(10),
useremail varchar(50)
)create table c
(
userid varchar(10)
)insert B select '','Test'
insert B select 'SC',''
insert c select 'Test'
insert c select 'SC'select c.*
from
(
select useremail from B where len(B.username)<=1
union
select username from B
) B inner join c on B.useremail=c.userid
from B inner join C on (len(B.username)<=1 and B.useremail = C.userid) or (B.username = C.userid)
from B inner join C on B.username=C.userid