1、一个表t_xsnr其中的两列(spcode,nxsdj),我要查询符合列‘spcode’前两位为‘02’和‘03’的行,nxsdj列求和spcode
023333
033333
043333
053333
026666
036666
046666nxsdj
63
12
13
21
25
29
35我用的是下面这条语句,但是查不出来,请高手指点这个查询该如何写:select sum(nxsdj) from t_xsnr where left(spcode,2)='02' and left(spcode,2)='03'2、还有一个问题,就是关于关联语句语法的事:
我在删除一个表中的内容,但是要关联其它的表,其中语法中的括号用法,如‘()’外是加‘{}’,还是加同样的‘()’:
例如:delect FROM T_XSNR WHERE spcode in (select * from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02')
或:
delect FROM T_XSNR WHERE spcode in {select * from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02'}请问哪种写法是对的,如果上面都不对,请问高手如何写语法才正确?
023333
033333
043333
053333
026666
036666
046666nxsdj
63
12
13
21
25
29
35我用的是下面这条语句,但是查不出来,请高手指点这个查询该如何写:select sum(nxsdj) from t_xsnr where left(spcode,2)='02' and left(spcode,2)='03'2、还有一个问题,就是关于关联语句语法的事:
我在删除一个表中的内容,但是要关联其它的表,其中语法中的括号用法,如‘()’外是加‘{}’,还是加同样的‘()’:
例如:delect FROM T_XSNR WHERE spcode in (select * from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02')
或:
delect FROM T_XSNR WHERE spcode in {select * from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02'}请问哪种写法是对的,如果上面都不对,请问高手如何写语法才正确?
--求總和
select sum(nxsdj) from t_xsnr where left(spcode,2)='02' OR left(spcode,2)='03'
--分別求和
select sum(nxsdj) from t_xsnr where left(spcode,2)='02' OR left(spcode,2)='03'
group by left(spcode,2)
--2.
delect FROM T_XSNR WHERE spcode in (select b.spxlcode from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02')
一个字段的前两位怎么可能同时是02和03呢
加同样的‘()’delect FROM T_XSNR WHERE spcode in (select * from p_spinfo as a,t_xsnr as b where a.spinfo=b.spinfo and LEFT(b.spxlcode, 2) = '02')
这里不能用*
用和这个spcode 匹配的字段
from t_xsnr as b
where exists
(select 1 from p_spinfo as a
where a.spinfo=b.spinfo
and left(b.spxlcode, 2) = '02') 删除数据之前先执行下面这个,对比数据确认语句正确性
select *
from t_xsnr as b
where exists
(select 1 from p_spinfo as a
where a.spinfo=b.spinfo
and left(b.spxlcode, 2) = '02')