日期:2014-05-18  浏览次数:20499 次

谁能帮我解答这个sql语句?
3张表分别为:

数据是
TableA

ID Name Money Money777 OperateTime typeA
01 小明 1 10 2011-9-15 13:24:00 1
01 小明 3 15 2011-9-15 13:25:00 1
01 小明 10 11 2011-9-15 15:19:00 1
03 小张 7 8 2011-9-15 15:18:00 3
03 小张 8 5 2011-9-13 15:18:00 3
07 小王 9 5 2011-9-17 15:18:00 3






TableC


PeronID PeronName Money2 Money3 LogTime typeC
01 小明 5 1 2011-9-15 13:24:00 1
01 小明 3 3 2011-9-15 13:25:00 1
01 小明 2 7 2011-9-15 15:19:00 1
03 小张 9 8 2011-9-15 10:19:00 3
03 小张 7 2 2011-9-15 15:52:00 3
07 小王 null null null 3




TableF


Id Name type
01 小明 1
03 小张 3
07 小王 3
11 小李 1 
15 小周 1




用这个sql语句可以查询到所有人的数据
select
 isnull(a.ID,d.id) as id,isnull(a.Name,d.name) as name,a.money1,c.money2,c.money3,b.money777 
from
 (select id,name,SUM(money)money1 from tablea group by id,name)a
join
(select id,money777 from tablea a where not exists(select 1 from tablea where Id=a.ID and OperateTime>a.OperateTime))b 
on
 a.id=b.id 
join
 (select Peronid,SUM(money2)money2,SUM(money3)money3 from tableC group by Peronid)c 
on
  a.id=c.Peronid
full join
  TableF d
on
  a.Name=d.Name 


----------------结果----------------------------
/* id name money1 money2 money3 money777
---- ---- ----------- ----------- ----------- -----------
01 小明 14 10 11 11
03 小张 15 16 10 8
07 小王 9 NULL NULL 5
11 小李 NULL NULL NULL NULL
15 小周 NULL NULL NULL NULL



我只想得到type属性为1的人的数据
也就是我想要的结果是:
01 小明 14 10 11 11
11 小李 NULL NULL NULL NULL
15 小周 NULL NULL NULL NULL


但是type=1的条件应该放在一下sql语句的哪里?

select
 isnull(a.ID,d.id) as id,isnull(a.Name,d.name) as name,a.money1,c.money2,c.money3,b.money777 
from
 (select id,name,SUM(money)money1 from tablea group by id,name)a
join
(select id,money777 from tablea a where not exists(select 1 from tablea where Id=a.ID and OperateTime>a.OperateTime))b 
on
 a.id=b.id 
join
 (select Peronid,SUM(money2)money2,SUM(money3)money3 from tableC group by Peronid)c 
on
  a.id=c.Peronid
full join
  TableF d
on
  a.Name=d.Name 





------解决方案--------------------
select
 isnull(a.ID,d.id) as id,isnull(a.Name,d.name) as name,a.money1,c.money2,c.money3,b.money777
from
 (select id,name,SUM(money)money1 from tablea group by id,name)a
join
(select id,money777 from tablea a where not exists(select 1 from tablea where Id=a.ID and OperateTime>a.OperateTime))b
on
 a.id=b.id
join
 (select Peronid,SUM(money2)money2,SUM(money3)money3 from tableC group by Peronid)c
on
a.id=c.Peronid
full join
TableF d
on
a.Name=d.Name AND d.type=1