怎么指定默认值
如何指定默认值?
已知表A:
id name type ...
001 sun human
002 wang human
003 zhang human
表A的每一行都是独一无二的
表B
bid valuea valueb tm
002 01 02 2013-01-03
003 02 02 2013-01-04
要求两个表做连接
而且A中的表每一行都要有,不存在的字段取指定的默认值,比如valuea默认01,valueb默认02
小弟不才,请问该怎么做
------解决思路----------------------
select isnull(b.valuea,'01'),isnull(b.valueb,'02'),* from {oj a left outer join b on a.id=b.bid}
大体是这个样子,你自己试一下
------解决思路----------------------
------解决思路----------------------
根据你的where过滤条件, [STINFO_YQ_XY]不一写每一行都显示
------解决思路----------------------
左连接右表不能有where条件,会过滤掉外部行
已知表A:
id name type ...
001 sun human
002 wang human
003 zhang human
表A的每一行都是独一无二的
表B
bid valuea valueb tm
002 01 02 2013-01-03
003 02 02 2013-01-04
要求两个表做连接
而且A中的表每一行都要有,不存在的字段取指定的默认值,比如valuea默认01,valueb默认02
小弟不才,请问该怎么做
------解决思路----------------------
select isnull(b.valuea,'01'),isnull(b.valueb,'02'),* from {oj a left outer join b on a.id=b.bid}
大体是这个样子,你自己试一下
------解决思路----------------------
select a.*,isnull(b.valuea,'01') as valuea,
isnull(b.valueb,'02') as valueb,b.tm
from 表A a join 表B b
on a.id=b.id
------解决思路----------------------
根据你的where过滤条件, [STINFO_YQ_XY]不一写每一行都显示
SELECT [ST_STINFO_B_YC].[STCDT] AS STCD,SUM([#table1].[DRP]) AS DRP,[ST_STINFO_B_YC].[STNM],
MAX([#table1].[TM]) TM,[ST_STINFO_B_YC].[STADDR],[ST_STINFO_B_YC].[DISTRICT],C.[XCD],C.[YCD]
from #table1
right JOIN [dbo].[STINFO_YQ_XY] AS C
ON [#table1].[STCD]=C.[STXY]
and [#table1].[TM]>'2013/10/01 6:00:00' AND [#table1].[TM]<='2013/10/05 10:59:46'
FULL JOIN [dbo].[ST_STINFO_B_YC]
ON [ST_STINFO_B_YC].[STCDT]=[#table1].[STCD]
--WHERE [#table1].[TM]>'2013/10/01 6:00:00' AND [#table1].[TM]<='2013/10/05 10:59:46' AND[#table1].[STCD] IS NOT NULL
group by [#table1].[STCD],[ST_STINFO_B_YC].[STCDT],[ST_STINFO_B_YC].[STNM],[ST_STINFO_B_YC].[STADDR],[ST_STINFO_B_YC].[DISTRICT],C.[XCD],C.[YCD]
drop table #table1
------解决思路----------------------
左连接右表不能有where条件,会过滤掉外部行