表A t1
id hid
1 1
2 2表B
hmid hid xid
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e表C t3
hmid date price
2 2009-7-5 901 2009-7-8 100
1 2009-7-7 80
3 2009-7-6 50
4 2009-7-7 40
5 2009-7-7 305 2009-7-8 60要求得到
hid hmid date price
1 3 2009-7-6 50
1 4 2009-7-7 40
1 4 2009-7-8 90
1 1 2009-7-7 80
1 1 2009-7-8 100
2 5 2009-7-5 90
2 2 2009-7-8 60结果的排序规则是有点点复杂.
先按 hid 排
然后按 hmid 分组中 price 从小到高() 也就是说hmid 相同的得靠在一起然后按 hmid中 price 最小的一个排
再按 date 从小到大
id hid
1 1
2 2表B
hmid hid xid
1 1 a
2 2 b
3 1 c
4 1 d
5 2 e表C t3
hmid date price
2 2009-7-5 901 2009-7-8 100
1 2009-7-7 80
3 2009-7-6 50
4 2009-7-7 40
5 2009-7-7 305 2009-7-8 60要求得到
hid hmid date price
1 3 2009-7-6 50
1 4 2009-7-7 40
1 4 2009-7-8 90
1 1 2009-7-7 80
1 1 2009-7-8 100
2 5 2009-7-5 90
2 2 2009-7-8 60结果的排序规则是有点点复杂.
先按 hid 排
然后按 hmid 分组中 price 从小到高() 也就是说hmid 相同的得靠在一起然后按 hmid中 price 最小的一个排
再按 date 从小到大
from t1 a,t2 b,t3 c
where a.hid=b.hid and b.hmid=c.hmid
order by a.hid,c.price,c.date
hid hmid date price
1 4 2009-7-7 40
1 4 2009-7-8 90
1 3 2009-7-6 50
1 1 2009-7-7 80
1 1 2009-7-8 100
2 2 2009-7-8 60
2 5 2009-7-5 90
from A,B,c
where a.hid=b.hid and b.hmid=c.hmid
group by a.hid,b.hmid
order by a.hid,[date]
select B.hid,B.hmid,[DATE],price
from A,B,c
where a.hid=b.hid and b.hmid=c.hmid
group by a.hid,b.hmid
order by a.hid,price,[date]
insert into @b select 1,1,'a'
union all select 2,2,'b'
union all select 3,1,'c'
union all select 4,1,'d'
union all select 5,2,'e'
declare @c table (hmid int,date datetime,price int)
insert into @c select 2,'2009-7-5',90
union all select 1,'2009-7-8',100
union all select 1,'2009-7-7',80
union all select 3,'2009-7-6',50
union all select 4,'2009-7-7',40
union all select 5,'2009-7-7',30
union all select 5,'2009-7-8',60
select b.hid,c.* from @b b left join @c c on b.hmid=c.hmid
order by hid,price,date