表a有列oddnum(bigint) useprice(decimal),表b有列oddnum(varchar) price(decimal).
a表中的oddnum 为 1,2,3,4。b表中的oddnum为MZ1,MZ2,MZ3,MZ4.后面数字跟前面一样的,就是多了个MZ 。
我的问题就是如何通过b的oddnum来查询a的sum(useprice).
刚学这个不太懂,还请高手赐教!谢谢。
a表中的oddnum 为 1,2,3,4。b表中的oddnum为MZ1,MZ2,MZ3,MZ4.后面数字跟前面一样的,就是多了个MZ 。
我的问题就是如何通过b的oddnum来查询a的sum(useprice).
刚学这个不太懂,还请高手赐教!谢谢。
from A
inner join B on A.oddnum = B.int(right(oddnum))
group by B.oddnum
from A
inner join B on A.oddnum = B.int(right(oddnum,1))
group by B.oddnum
少些一个1 ,我晕!
--> 测试数据:[A1]
if object_id('[A]') is not null drop table [A]
create table [A]([oddnum] bigint,[useprice] decimal)
insert [A]
select 1,20 union all
select 2,30 union all
select 3,40 union all
select 4,50
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([oddnum] varchar(3),[price] decimal)
insert [B]
select 'MZ1',100 union all
select 'MZ2',200 union all
select 'MZ3',300select * from [A]
select * from [B]
select B.oddnum,sum(A.useprice)
from A
inner join B on A.oddnum = CONVERT(INT,(right(B.oddnum,1)))
group by B.oddnum
/*
oddnum
------ ---------------------------------------
MZ1 20
MZ2 30
MZ3 40(3 行受影响)*/
select * from a as a join b as b on 'mz'+a.oddnum=b.oddnum
select [总额]=sum(A.useprice)
from A
inner join B on 'MZ'+ltrim(A.oddnum) = B.oddnum