求一个SQL或解决方法.

[feitongyiwang]

公司表 等级 职位表

A公司 1 XXX A职位 发布时间
XXX B职位 发布时间

B公司 2 XXX C职位 发布时间
XXX D职位 发布时间


公司表有：ID（自动编号），name（公司名），rating（等级）
职位表里ID（职位自动编号），gsid（对应公司ID），zhiwei（职）位

我是想让，等级为1的公司，不受发布时间限制，而等级为2的公司,发布时间过了1个月就不在显示。

我应该如何做呢???

[小逗狗]

if rs("等级")=2 and datediff("d",now(),rs("发布时间"))>=30 then
response.write ""
else
respone.write rs("职位")
end if

[sy_binbin]

sql = "select * from 公司表 as a inner join 职位表 as b on a.id = b.gsid where 等级=2 and datediff('m',发布时间,getDate())>0"

[sy_binbin]

[Quote=引用 2 楼 sy_binbin 的回复:]
sql = "select * from 公司表 as a inner join 职位表 as b on a.id = b.gsid where 等级=2 and datediff('m',发布时间,getDate())>0"
[/Quote]

错了

sql = "select * from 公司表 as a inner join 职位表 as b on a.id = b.gsid where 等级=2 and datediff('d',发布时间,getDate())<0"

[「已注销」]

1楼正解，理解简单，呵呵~~~

[feitongyiwang]

楼上的,等级为1的呢??会显示吗?这个出来的是等级为2的吧?

[feitongyiwang]

我可能是需要分页的,1楼的是简单,但似乎不太适合.

[sy_binbin]

分页的时候1楼也可以的

要不就试试我的SQL语句对不对


具体用法搜搜SQL语句里的datediff用法

SQL数据库和ACCESS数据库不太一样

[feitongyiwang]

不灵光.不成，提示ODBC 驱动程序不支持所需的属性。

[toury]

[Quote=引用 13 楼 feitongyiwang 的回复:]
不灵光.不成，提示ODBC 驱动程序不支持所需的属性。
[/Quote]
1、换成OLEDB连库；
2、把IIF（）函数改为if 。。then 语句看看

我没测试，也不知道结果如何，呵呵

[feitongyiwang]

<table width="802" border="0" align="center" cellpadding="0" cellspacing="0" style="margin-top:5px; margin-bottom:15px;">
<tr>
<td valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="201"><img src="images/lpl_05.jpg" width="201" height="23" alt="" /></td>
<td style="border-bottom:#B52636 1px solid;"><table width="100%" border="0" cellspacing="0" cellpadding="0"><form id="form1" name="form1" method="post" action="">
<tr>
<td width="85%" align="right">每页显示条数：</td>
<td width="15%" align="right">
<select name="numpPage" id="numpPage" onchange='javascript:submit()'>
<option value="10" <%if session("numpPage")=10 then%> selected="selected"<%end if%> >10</option>
<option value="20" <%if session("numpPage")=20 then%> selected="selected"<%end if%>>20</option>
<option value="40" <%if session("numpPage")=40 then%> selected="selected"<%end if%>>40</option>
</select>
</td>
</tr></form>
</table></td>
</tr>
</table><%
sql="select * from zhaopins where infotype='1'"
if zhuanye<>"" then
sql=sql&" and zhuanye="&zhuanye&""
else
sql=sql&" and zhuanye="&session("zhuanye")&""
end if
sql=sql&" order by pubdate desc"
rs.open sql,conn,1,1
%>
<table width="100%" height="21" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="5" background="images/51.jpg"></td>
<td width="47" background="images/lpl_09.jpg" class="baise" style="border-bottom:#B52636 1px solid;"><img src="images/jjjttt.jpg" width="7" height="20" border="0" align="absmiddle" usemap="#Map" style="margin-right:3px;" />日期</td>
<td background="images/lpl_09.jpg" class="baise" style="border-bottom:#B52636 1px solid;"><span class="STYLE3" style="border-bottom:#B52636 1px solid;"><img src="images/jjjttt.jpg" width="7" height="20" border="0" align="absmiddle" usemap="#MapMap" style="margin-right:3px;" /></span>职位名称</td>
<td width="108" background="images/lpl_09.jpg" class="baise" style="border-bottom:#B52636 1px solid;"><span class="STYLE3" style="border-bottom:#B52636 1px solid;"><img src="images/jjjttt.jpg" width="7" height="20" border="0" align="absmiddle" usemap="#MapMap" style="margin-right:3px;" /></span>招聘地区</td>
<td width="193" background="images/lpl_09.jpg" class="baise" style="border-bottom:#B52636 1px solid;"><span class="STYLE3" style="border-bottom:#B52636 1px solid;"><img src="images/jjjttt.jpg" width="7" height="20" border="0" align="absmiddle" usemap="#MapMap" style="margin-right:3px;" /></span>招聘专业</td>
<td width="66" background="images/lpl_09.jpg" class="baise" style="border-bottom:#B52636 1px solid;"><span class="STYLE3" style="border-bottom:#B52636 1px solid;"><img src="images/jjjttt.jpg" width="7" height="20" border="0" align="absmiddle" usemap="#MapMap" style="margin-right:3px;" /></span>年薪</td>
<td background="images/511.jpg" width="7"></td>
</tr><%
if rs.eof then
%>
<tr>
<td height="35"></td>
<td colspan="5" align="center">暂时无职位</td>
<td></td>
</tr><%
else'if rs.eof then
'sDesURL = "?qiyeid="&qiyeid&"&city="&city&"&keyword="&keyword&"&"
sDesURL = "?hangye="& hangye &"&qiyeid="&qiyeid&"&city="&city&"&keyword="&keyword&"&"
nTotalNumber = rs.recordCount
nMaxPerPage = session("numpPage")
rs.pageSize = nMaxPerPage
If request("page") <> "" then nCurrentPage = cint(request("page"))
if nCurrentPage < 1 then nCurrentPage = 1
bShowTotal=true
bShowCombo = true
sUnit = "条"
rs.absolutepage = nCurrentPage
for n = 1 to nMaxPerPage/2
if rs.eof then exit for
if rs("rating")=2 and rs("rating")=3 then
' if (isnull(rs("rating")) and rs("rating")=2 and rs("rating")=3) and datediff("d",now(),rs("PubDate"))>=120 then
'if (date() <dateadd("m",4,rs("PubDate"))) and (isnull(rs("rating")) and rs("rating")=2 and rs("rating")=3) then
else
%>
<tr>
<td height="35"></td>
<td><%=month(rs("pubdate"))%>/<%=day(rs("pubdate"))%></td>
<td><span class="lanse"><a href="job-info.asp?id=<%=rs("id")%>&tp=<%=tp%>" target="_blank"><%=rs("zhiwei")%></a></span>　<%if rs("isonly")="1" then response.Write("<img src='images/news-01_05.gif' width='32' height='17' alt='独家信息' />")%> <%if rs("pubinfo")="0" then response.Write ("<img src='images/news-01_03.gif' width='32' height='17' alt='免费信息' />")%>
<%if rs("Have_strokes")="1" then response.Write("<img src='images/news-02_03.gif' width='32' height='17') />") %></td>
<td><%=rs("areaname")%></td>
<td><%=rs("zhuanyename")%></td>
<td><%=rs("allxinshui")%></td>
<td></td>
</tr>
<tr>
<td height="35"></td>
<td><span class="lanse"><a href="Save-job.asp?id=<%=rs("id")%>" target="_blank">保存</a></span></td>
<td colspan="4"><%=left(ClearHtml(rs("detail")),72)%></td>
<td></td>
</tr><%end if%>
<tr>
<td height="2" colspan="7" bgcolor="#CDCDCD"></td>
</tr><%
rs.movenext
if rs.eof then exit for
if rs("rating")=2 and rs("rating")=3 then
' if (isnull(rs("rating")) and rs("rating")=2 and rs("rating")=3) and datediff("d",now(),rs("PubDate"))>=120 then
'if (date() <dateadd("m",4,rs("PubDate"))) and (isnull(rs("rating")) and rs("rating")=2 and rs("rating")=3) then
else
%>
<tr>
<td height="35" bgcolor="#F6F5F5"></td>
<td bgcolor="#F6F5F5"><%=month(rs("pubdate"))%>/<%=day(rs("pubdate"))%></td>
<td bgcolor="#F6F5F5"><a href="job-info.asp?id=<%=rs("id")%>&tp=<%=tp%>" target="_blank"><%=rs("zhiwei")%></a>　<%if rs("isonly")="1" then response.Write("<img src='images/news-01_05.gif' width='32' height='17' alt='独家信息' />")%> <%if rs("pubinfo")="0" then response.Write ("<img src='images/news-01_03.gif' width='32' height='17' alt='免费信息' />")%>
<%if rs("Have_strokes")="1" then response.Write("<img src='images/news-02_03.gif' width='32' height='17' />") %></td>
<td bgcolor="#F6F5F5"><%=rs("areaname")%></td>
<td bgcolor="#F6F5F5"><%=rs("zhuanyename")%></td>
<td bgcolor="#F6F5F5"><%=rs("allxinshui")%></td>
<td bgcolor="#F6F5F5"></td>
</tr>
<tr>
<td height="35" bgcolor="#F6F5F5"></td>
<td bgcolor="#F6F5F5"><span class="lanse"><a href="Save-job.asp?id=<%=rs("id")%>" target="_blank">保存</a></span></td>
<td colspan="4" bgcolor="#F6F5F5"><%=left(ClearHtml(rs("detail")),60)%></td>
<td bgcolor="#F6F5F5"></td>
</tr><%end if%><tr>
<td height="2" colspan="7" bgcolor="#CDCDCD"></td>
</tr><%
rs.movenext
if rs.eof then exit for
next
%>
</table>
<table width="100%" border="0" cellspacing="10" cellpadding="0">
<tr>
<td height="20" align="right"><%showpage sDesURL, nTotalNumber, nMaxPerPage, nCurrentPage, bShowTotal, bShowCombo, sUnit%></td>
</tr><%
end if
%>
</table><%

rs.close
set rs = nothing
%></td>
</tr>
</table>

[feitongyiwang]

请帮我修改一下呢?总是不灵

[feitongyiwang]

楼上的,假如公司级别等于1,不受限制情况下，是不是就不会出现了呢?因为条件已经限制出现的级别为2.超过1个月不在显示.

[情绝书生]

sql = "select * from 公司表 as a inner join 职位表 as b on a.id = b.gsid where 等级=2 and datediff('d',发布时间,getDate()) <0 or 等级=1"

[toury]

SELECT *,datediff('d',[发布时间],date()) FROM [公司表] WHERE IIF([等级]=1,1=1,datediff('d',riqi ,date())<=30)

没什么把握，你试一下吧；如果是SQL，你把date()改为getdate()

[toury]

SELECT * FROM [公司表] WHERE IIF([等级]=1,1=1,datediff('d',riqi ,date())<=30)

刚才多了一个datediff,呵呵

[feitongyiwang]

楼上的好象有点意思,我中午吃饭后，回来按你方法试一下.