有点难度的sql,请帮我看看...

[evjen]

http://topic.csdn.net/u/20090616/22/828dfbf9-b0c6-4fe7-bb3f-7c3bfe9ac930.html?16033

谢谢

[lg3605119]

sf

[ai_li7758521]

看看，怎么连到.net去了..

[中国风]

--> --> (Roy)生成測試數據
 
declare @A table([fixtureid] int,[hometeamid] int,[awayteamid] int)
Insert @A
select 183364,567,534 union all
select 183365,571,123
 
--> --> (Roy)生成測試數據
 
declare @B table([fixtureid] int,[teamid] int,[goaltime] int)
Insert @B
select 183364,567,4 union all
select 183365,571,47 union all
select 183365,571,68 union all
select 183365,123,21 union all
select 183365,123,89
 
Select 
	a.[fixtureid], a.[hometeamid],a.[awayteamid],[比分]=rtrim(count(distinct b1.[goaltime]))+':'+rtrim(count(distinct b2.[goaltime]))
from @A a
	left join @B b1 on a.[fixtureid]=b1.[fixtureid] and a.[hometeamid]=b1.[teamid]
	left join @B b2 on a.[fixtureid]=b2.[fixtureid] and a.[awayteamid]=b2.[teamid]
group by a.[fixtureid], a.[hometeamid],a.[awayteamid]


fixtureid   hometeamid  awayteamid  比分
----------- ----------- ----------- -------------------------
183364      567         534         1:0
183365      571         123         2:2

[nalnait]

转到SQL 版

[中国风]

看错

--> --> (Roy)生成測試數據
 
declare @A table([fixtureid] int,[hometeamid] int,[awayteamid] int)
Insert @A
select 183364,567,534 union all
select 183365,571,123 union all
select 183366,571,123
 
--> --> (Roy)生成測試數據
 
declare @B table([fixtureid] int,[teamid] int,[goaltime] int)
Insert @B
select 183364,567,4 union all
select 183365,571,47 union all
select 183365,571,68 union all
select 183365,123,21 union all
select 183365,123,89
 
Select 
    a.[fixtureid], [firstget]=case when isnull(min(b1.[goaltime]),min(b2.[goaltime]))<isnull(min(b2.[goaltime]),min(b1.[goaltime])) or min(b2.[goaltime]) is null then a.[hometeamid] else a.[awayteamid] end,
	[firstlose]=case when isnull(min(b1.[goaltime]),min(b2.[goaltime]))<isnull(min(b2.[goaltime]),min(b1.[goaltime]))or min(b2.[goaltime]) is null then a.[awayteamid] else  a.[hometeamid] end
from @A a
    left join @B b1 on a.[fixtureid]=b1.[fixtureid] and a.[hometeamid]=b1.[teamid]
    left join @B b2 on a.[fixtureid]=b2.[fixtureid] and a.[awayteamid]=b2.[teamid]
group by a.[fixtureid], a.[hometeamid],a.[awayteamid]


fixtureid   firstget    firstlose
----------- ----------- -----------
183364      567         534
183365      123         571
183366      571         123

[swot2ly_100]

declare @A table(fixtureid int,hometeamid int,awayteamid int)
insert into @A(fixtureid,hometeamid,awayteamid)
select 183364,567,534 union
select 183365,571,123

declare @B table(fixtureid int,teamid int,goaltime int)
insert into @B(fixtureid,teamid,goaltime)
select 183364,567,4 union
select 183365,571,47 union
select 183365,571,68 union
select 183365,123,21 union
select 183365,123,89

select A.fixtureid,T2.teamid as firstget,case when A.hometeamid = T2.teamid then A.awayteamid else A.hometeamid end as firstlose,
convert(varchar(2),isnull(T2.goalno,0))+':'+convert(varchar(2),isnull(T3.goalno,0)) as goal
from 
@A as A
left outer join
(
	select fixtureid,min(goaltime) as firsttime from @B group by fixtureid
) as T
on A.fixtureid = T.fixtureid
left outer join
(
	select fixtureid,teamid,min(goaltime) as firsttime,count(goaltime) as goalno from @B group by fixtureid,teamid
) as T2
on T.fixtureid = T2.fixtureid and T.firsttime = T2.firsttime
left outer join
(
	select fixtureid,teamid,min(goaltime) as firsttime,count(goaltime) as goalno from @B group by fixtureid,teamid
) as T3
on T.fixtureid = T3.fixtureid and T.firsttime <> T3.firsttime