这样的sql怎样写呢？（请高手们赐教）
有三个表：T1 T2 T3
T1
code time
a1 t1
a2 t2
a3 t3
T2
code name product
a1 john a
a1 john c
a2 amy c
a2 amy b
a3 tom b
T3
code name product b
a1 john a b1
a1 john c b2
a1 john c b3
a2 amy c b1
查询条件：T1.time <= t2
查询结果：
name count(distinct T3.prod) count(T3.b)
john 2 3
amy 1 1
[解决办法]
你测试一下，看看
----------------------
select T3.name,T3.code,A.qty1,count(prod) qty2
from T3,
(select code
name,
count(prod) qty1
from T2
where code in(
select code
from T1
where time <=t2)
group by code,name) A
where T3.code = A.code
and T3.name= A.name
group by name,code
[解决办法]
还是看不懂你的需求.
1.T1.time <= t2?
怎么比较的?看不明白.
2.结果貌似不对.
应该为:
name count(T2.prod) count(distinct T3.prod) count(T3.b)
john 3 2 3
amy 2 1 1
tom 2 1 1
[解决办法]
明白了，查询条件：T1.time <= t2，这里的't2'是一个时间值，大家都把它当成T2表了
select T2.name,count(distinct T2.prod) as prod_sl1,count(distinct T3.prod) as prod_sl2,count(distinct T3.b) as b_sl
from T1 left join T2 on T1.code = T2.code
left join T3 on T1.code = T3.code
where T1.time <= t2
呵呵，很简单的问题，但是你描述得太复杂了
[解决办法]
sorry,上面少写了group by
select T2.name,count(distinct T2.prod) as prod_sl1,count(distinct T3.prod) as prod_sl2,count(distinct T3.b) as b_sl
from T1 left join T2 on T1.code = T2.code
left join T3 on T1.code = T3.code
where T1.time <= t2
group by T2.name
