select IDENTITY(ind,1,1) as ID ,x.A ,Y.B ,t.[max(A)-min(A)] INTO Z from X,Y,( select YID,max(A)-min(A) as [max(A)-min(A)] from X group by YID ) as t where X.YID=Y.ID and X.YID=t.YIDselect * from Z
select id=identity(int,1,1),X.a,Y.b,a.YID,max(X.A)-min(X.A) as [max(X.A)-min(X.A)] into Z from X left join Y on X.YID=Y.ID GROUP BY X.a,Y.b,a.YID
create table #X ( [ID] int, A int, YID int ) create table #Y ( [ID] int, B int ) insert into #X select 1,2,3 union all select 2,5,7 union all select 22,54,7 union all select 25,54,75 union all select 28,54,76
insert into #Y select 1,4 union all select 2,5 union all select 22,54 union all select 25,54 union all select 27,54select C.[ID],C.A,B.YID,C.B,b.ResultColumn from (select YID,max(A.A)-min(A.A)as ResultColumn from (select #X.[ID],#X.A,#X.YID,#Y.B from #X join #Y on #X.[ID]=#Y.[ID]) as A group by A.YID)as B LEFT JOIN (select #X.[ID],#X.A,#X.YID,#Y.B from #X join #Y on #X.[ID]=#Y.[ID]) as C on B.YID=C.YIDID A YID B Resultcolumn 1 2 3 4 0 2 5 7 5 49 22 54 7 54 49 25 54 75 54 0
这个有点错,应该把ind改为int 我的理解应该是这样的 select X.ID ,X.A ,Y.B ,T.Result INTO Z from X,Y,( select YID,max(A)-min(A) as Result from X group by YID ) as t where X.YID=Y.ID and X.YID=t.YIDselect * from Z
,x.A
,Y.B
,t.[max(A)-min(A)]
INTO Z
from X,Y,(
select YID,max(A)-min(A) as [max(A)-min(A)]
from X
group by YID
) as t
where X.YID=Y.ID
and X.YID=t.YIDselect * from Z
into Z
from X
left join Y on X.YID=Y.ID
GROUP BY X.a,Y.b,a.YID
(
[ID] int,
A int,
YID int
)
create table #Y
(
[ID] int,
B int
)
insert into #X select 1,2,3
union all select 2,5,7
union all select 22,54,7
union all select 25,54,75
union all select 28,54,76
insert into #Y select 1,4
union all select 2,5
union all select 22,54
union all select 25,54
union all select 27,54select C.[ID],C.A,B.YID,C.B,b.ResultColumn from
(select YID,max(A.A)-min(A.A)as ResultColumn from
(select #X.[ID],#X.A,#X.YID,#Y.B from #X join #Y
on #X.[ID]=#Y.[ID]) as A
group by A.YID)as B
LEFT JOIN
(select #X.[ID],#X.A,#X.YID,#Y.B from #X join #Y
on #X.[ID]=#Y.[ID]) as C
on B.YID=C.YIDID A YID B Resultcolumn
1 2 3 4 0
2 5 7 5 49
22 54 7 54 49
25 54 75 54 0
我的理解应该是这样的
select X.ID
,X.A
,Y.B
,T.Result
INTO Z
from X,Y,(
select YID,max(A)-min(A) as Result
from X
group by YID
) as t
where X.YID=Y.ID
and X.YID=t.YIDselect * from Z