Question

ASP里，两张表联合查询问题。

表1：baodan
ID baodanhao chezhu
1 4343 张三
2 55545 李四

表2：huifang
ID baodanID huifangshijian
1 1 2010-09-12
2 1 2010-10-12
3 1 2010-11-12
4 2 2010-09-12
5 2 2010-12-12
baodan.id=huifang.baodanID，我想查询出表2：huifang中最近的huifangshijian超过3个月的保单，列表出来，并且可以用chezhu做查询。怎么搞？
谢谢！
想得到的结果为：
ID baodanhao chezhu huifangshijian
1 4343 张三 2010-11-12
2 55545 李四 2010-12-12

Answer 1

SELECT baodan.ID, baodan.baodanhao, baodan.chezhu, huifang.huifangshijian
FROM baodan INNER JOIN huifang ON baodan.ID = huifang.baodanID

至于后面“huifang中最近的huifangshijian超过3个月的保单”，我没看明白是什么意思

Answer 2

SELECT baodan.ID, baodan.baodanhao, baodan.chezhu, huifang.huifangshijian
FROM baodan INNER JOIN huifang ON baodan.ID = huifang.baodanID where DATEDIFF(mm,getdate(),huifang. huifangshijian) >= 3

Answer 3

sql server？
最近的
SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian)
FROM baodan as a, huifang as b
WHERE a.id = b.baodanID
group by a.id,a.baodanhao,a.chezhu
超90天最近的
SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian)
FROM baodan as a, huifang as b
WHERE a.id = b.baodanID
and datediff(d,b.huifangshijian,getdate()) > 90
group by a.id,a.baodanhao,a.chezhu
超3个月最近的
SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian)
FROM baodan as a, huifang as b
WHERE a.id = b.baodanID
and datediff(m,b.huifangshijian,getdate()) > 3
group by a.id,a.baodanhao,a.chezhu
要加chezhu查询的话
SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian)
FROM baodan as a, huifang as b
WHERE a.id = b.baodanID
and datediff(m,b.huifangshijian,getdate()) > 3
and a.chezhu = '张三'
group by a.id,a.baodanhao,a.chezhu

asp中

<%
If chezhu = "" then
sql = "SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian) FROM baodan as a, huifang as b WHERE a.id = b.baodanID and datediff(m,b.huifangshijian,getdate()) > 3 group by a.id,a.baodanhao,a.chezhu"
Else
sql = "SELECT a.id,a.baodanhao,a.chezhu,max(b.huifangshijian) FROM baodan as a, huifang as b WHERE a.id = b.baodanID and datediff(m,b.huifangshijian,getdate()) > 3 and a.chezhu = '" & chezhu & "' group by a.id,a.baodanhao,a.chezhu"
End if

'执行语句
%>