A表:
列名 DW JH HTH
my 1.2 2
my 3.0 6
my 2.6 12B表:
列名 DW JH XM
my 2.2 x
my 1.1 y
my 1.02 x
my 2.3 y要想得到
列名 DW JH HTH JH_x JH_y
my 1.2 2 3.22 3.40
my 3.0 6 0.00 0.00
my 2.6 12 0.00 0.00
列名 DW JH HTH
my 1.2 2
my 3.0 6
my 2.6 12B表:
列名 DW JH XM
my 2.2 x
my 1.1 y
my 1.02 x
my 2.3 y要想得到
列名 DW JH HTH JH_x JH_y
my 1.2 2 3.22 3.40
my 3.0 6 0.00 0.00
my 2.6 12 0.00 0.00
from a as x left Join (Select dw,sum(Jh) as Sjh,sum(xm) as Sxm from b group by dw) as t
on x.dw=b.dw and not exists(Select * from a where dw=x.dw and HTH<x.HTH)
insert into a(dw,jh,mth)
select 'my',1.2,2
union all select 'my',3.0,6
union all select 'my',2.6,12
create table b(dw varchar(10),jh numeric(20,6),xm char(1))
insert into b(dw,jh,xm)
select 'my',2.2,'x'
union all select 'my',1.1,'y'
union all select 'my',1.02,'x'
union all select 'my',2.3,'y'select a.dw,a.jh,a.mth,
case when mth=2 then t1.jh_x else 0 end as jh_x,
case when mth=2 then t1.jh_y else 0 end as jh_y
from a
left join (select dw,
sum(case when xm='x' then jh end) as jh_x,
sum(case when xm='y' then jh end) as jh_y
from b group by dw)t1 on t1.dw=a.dw不知道楼主的数据为什么第2行和第3行的jh_x,jh_y的值为0?
declare @b table(dw varchar(8),jh decimal(8,2),XM varchar(8))Insert into @a
Select 'my',1.2 ,2
union Select 'my',3.0, 6
union Select 'my',2.6, 12Insert into @b
Select 'my',2.2,'x'
union Select 'my',1.1,'y'
union Select 'my',1.02,'x'
union Select 'my',2.3,'y' Select x.*,JH_x=IsNULL(b.JH_x,0.00),JH_y=IsNULL(b.JH_y,0.00)
from @a as x left Join (Select dw,sum(case when xm='x' then Jh else 0 end) as JH_x,
sum(case when xm='y' then Jh else 0 end) as JH_y
from @b group by dw) b on x.dw=b.dw and not exists(Select * from @a where dw=x.dw and HTH<x.HTH)
列名 'mth' 无效。
必须声明变量 '@a'。
必须声明变量 '@b'。
insert into a(dw,jh,hth)
select 'my',1.2,2
union all select 'my',3.0,6
union all select 'my',2.6,12
create table b(dw varchar(10),jh numeric(20,6),xm char(1))
insert into b(dw,jh,xm)
select 'my',2.2,'x'
union all select 'my',1.1,'y'
union all select 'my',1.02,'x'
union all select 'my',2.3,'y'select a.dw,a.jh,a.hth,
case when hth=2 then t1.jh_x else 0 end as jh_x,
case when hth=2 then t1.jh_y else 0 end as jh_y
from a
left join (select dw,
sum(case when xm='x' then jh end) as jh_x,
sum(case when xm='y' then jh end) as jh_y
from b group by dw)t1 on t1.dw=a.dw
-- 列名 DW JH HTH
-- MY 1.2 2
-- MY 3.0 6
-- MY 2.6 12
--
-- B表:
-- 列名 DW JH XM
-- MY 2.2 X
-- MY 1.1 Y
-- MY 1.02 X
-- MY 2.3 Y
--
-- 要想得到
-- 列名 DW JH HTH JH_X JH_Y
-- MY 1.2 2 3.22 3.40
-- MY 3.0 6 0.00 0.00
-- MY 2.6 12 0.00 0.00DROP TABLE TABLEA
DROP TABLE TABLEBCREATE TABLE TABLEA(DW CHAR(2),JH DECIMAL(20,2),HTH DECIMAL(20,2))
CREATE TABLE TABLEB(DW CHAR(2),JH DECIMAL(20,2),XM CHAR(1))INSERT INTO TABLEA
SELECT 'MY' , 1.2 , 2
UNION SELECT 'MY' , 3.0 , 6
UNION SELECT 'MY' , 2.6 , 12INSERT INTO TABLEB
SELECT 'MY' , 2.2 , 'X'
UNION SELECT 'MY' , 1.1 , 'Y'
UNION SELECT 'MY' , 1.02 , 'X'
UNION SELECT 'MY' , 2.3 , 'Y'
SELECT DW,JH,HTH,JH_X=(SELECT SUM(JH) FROM TABLEB WHERE XM='X'),JH_Y=(SELECT SUM(JH) FROM TABLEB WHERE XM='Y') FROM TABLEA