SELECT ID,Title,Content,Picurl,Dianhua,QQ,Dizhi,UpdateTime,T.Fenshu FROM A OUTER APPLY(SELECT TOP 1 Fenshu FROM B WHERE A.ID=B.ID ORDER BY Fenshu DESC)T ORDER BY T.Fenshu这个只有SQL2005才会好处理点,SQL2000再说~~
2000中Fenshu、IP要分开查询 SELECT A*, B1.Fenshu, B2.IP FROM A JOIN ( SELECT ID, MAX(Fenshu) FROM B GROUP BY ID ) B1 ON B1.ID = A.ID JOIN B B2 ON B2.ID = B1.ID AND B2.Fenshu = B1.Fenshu ORDER BY B1.Fenshu
两种都测试了 好像是不行 读取的数据ID是空值 下边是我的源码 <% set rs_Product=server.createobject("adodb.recordset") sqltext="select top 5 * from 0791idc_Product where Passed="&TrueType&"" 'sqltext="select top 5 * from P_pingjia order by fenshu desc" 读取分数表并按分数排列 rs_Product.open sqltext,conn,1,1if not rs_Product.EOF then%> <li> <p class="li_pic"><a href='Product_Show.asp?ID=<%=rs_Product("ID")%>' target=_blank title='<%=rs_Product("title")%>'>
<%if fileext="jpg" or fileext="bmp" or fileext="png" or fileext="gif" then%><img src="<%if rs_Product("DefaultPicUrl")="" then%>Img/nopic.gif<%else%><%=rs_Product("DefaultPicUrl")%><%end if%>" alt="<%=rs_Product("title")%>"></a></p> <p class="li_tit"><a href="Product_Show.asp?ID=<%=rs_Product("ID")%>" target="_blank"><%=cutstr(rs_Product("title"),8)%></a><span>原价:<%=rs_Product("Spec")%>.00</span><em><%=rs_Product("Unit")%></em>补贴券</p> <%end if%>
</li> <% rs_Product.MoveNext wend %>
<% end if rs_Product.close set rs_Product=nothing %>
不知道你是怎么处理的,你贴出来的源码并没有把我们的语句带进去 另外,我们的语句只是猜测,而你从来没说过,A表和B表之间的关系,即,通过哪个字段进行关联最后,你贴出来的东西,select top 5 * from 0791idc_Product where Passed= 和你在一楼的描述有严重的出入,无法对应上我没别的意思,我只是想说,你可以把这个问题描述得更清楚一点~
SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)Fenshu FROM P_pingjia GROUP BY ID)B ON A.ID=B.ID ORDER BY B.Fenshu DESC你试下,换行的地方自己注意下还有,有什么问题,是错误就贴出错误信息,是效果不对,也可以说明下,说还是不行,没人会明白什么意思的
替换之后的源码<% set rs_Product=server.createobject("adodb.recordset") sqltext="SELECT A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)Fenshu FROM P_pingjia GROUP BY ID)B ON A.ID=B.ID ORDER BY B.Fenshu DESC" rs_Product.open sqltext,conn,1,1 if not rs_Product.EOF then%> <li> <p class="li_pic"><a href='Product_Show.asp?ID=<%=rs_Product("ID")%>' target=_blank title='<%=rs_Product("title")%>'> <%if fileext="jpg" or fileext="bmp" or fileext="png" or fileext="gif" then%><img src="<%if rs_Product("DefaultPicUrl")="" then%>Img/nopic.gif<%else%><%=rs_Product("DefaultPicUrl")%><%end if%>" alt="<%=rs_Product("title")%>"></a></p> <p class="li_tit"><a href="Product_Show.asp?ID=<%=rs_Product("ID")%>" target="_blank"><%=cutstr(rs_Product("title"),8)%></a><span>原价:<%=rs_Product("Spec")%>.00</span><em><%=rs_Product("Unit")%></em>补贴券</p> <%end if%> </li> <% rs_Product.MoveNext wend %>
<% end if rs_Product.close set rs_Product=nothing %> 调用出来的数据所有的ID都调出来了,但是ID,title,DefaultPicUrl,Spec,Unit的值获取不到,都为空值
ACCESS 大概是不能省略表名或不能省略 AS MAX(P_pingjia.fenshu) AS fenshu
真的假的?那试试 SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu FROM P_pingjia GROUP BY ID)AS B ON A.ID=B.ID ORDER BY B.Fenshu DESC
--SQL 2005 select * from A Join ( select B.ID as BID,B.Fenshu,B.IP , row_number over(partition by B.ID order by B.Fenshu desc) as SN ) as d ON d.BID=A.ID where d.SN=1 order by d.Fenshu desc--SQL 2000 select * from A Join ( select B.ID AS BID,max(B.Fenshu) as fenshu from B ) as d on A.ID=d.BID order by fenshu desc
SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu1 FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON A.ID=B.ID ORDER BY B.Fenshu1 DESC那就取个不一样的别名
P_pingjia和0791idc_Product中的id类型各是什么呢
SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu1 FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID ORDER BY B.Fenshu1 DESC这样试试
SELECT TOP 5 A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu1 FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID ORDER BY B.Fenshu1 DESC直接TOP 5
直接TOP 5还是调用的全部数据
select top 5 * from (SELECT TOP 5 A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu1 FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID ORDER BY B.Fenshu1 DESC) 这样写好像是可以
select top 5 * from (SELECT A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu1 FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID ORDER BY B.Fenshu1 DESC)
OUTER APPLY(SELECT TOP 1 Fenshu FROM B WHERE A.ID=B.ID ORDER BY Fenshu DESC)T
ORDER BY T.Fenshu这个只有SQL2005才会好处理点,SQL2000再说~~
SELECT A*,
B1.Fenshu,
B2.IP
FROM A
JOIN ( SELECT ID, MAX(Fenshu)
FROM B
GROUP BY ID
) B1
ON B1.ID = A.ID
JOIN B B2
ON B2.ID = B1.ID
AND B2.Fenshu = B1.Fenshu
ORDER BY B1.Fenshu
下边是我的源码
<%
set rs_Product=server.createobject("adodb.recordset")
sqltext="select top 5 * from 0791idc_Product where Passed="&TrueType&""
'sqltext="select top 5 * from P_pingjia order by fenshu desc" 读取分数表并按分数排列
rs_Product.open sqltext,conn,1,1if not rs_Product.EOF then%>
<li>
<p class="li_pic"><a href='Product_Show.asp?ID=<%=rs_Product("ID")%>' target=_blank title='<%=rs_Product("title")%>'>
<%if fileext="jpg" or fileext="bmp" or fileext="png" or fileext="gif" then%><img src="<%if rs_Product("DefaultPicUrl")="" then%>Img/nopic.gif<%else%><%=rs_Product("DefaultPicUrl")%><%end if%>" alt="<%=rs_Product("title")%>"></a></p>
<p class="li_tit"><a href="Product_Show.asp?ID=<%=rs_Product("ID")%>" target="_blank"><%=cutstr(rs_Product("title"),8)%></a><span>原价:<%=rs_Product("Spec")%>.00</span><em><%=rs_Product("Unit")%></em>补贴券</p>
<%end if%>
</li>
<%
rs_Product.MoveNext
wend
%>
<%
end if
rs_Product.close
set rs_Product=nothing
%>
另外,我们的语句只是猜测,而你从来没说过,A表和B表之间的关系,即,通过哪个字段进行关联最后,你贴出来的东西,select top 5 * from 0791idc_Product where Passed=
和你在一楼的描述有严重的出入,无法对应上我没别的意思,我只是想说,你可以把这个问题描述得更清楚一点~
5条信息按P_pingjia表中的fenshu大小排列0791idc_Product表ID=P_pingjia表ID
P_pingjia表通过记录IP实现一人只能评分一次,ID不是唯一
LEFT JOIN(SELECT ID,MAX(Fenshu)Fenshu FROM P_pingjia GROUP BY ID)B ON A.ID=B.ID
ORDER BY B.Fenshu DESC你试下,换行的地方自己注意下还有,有什么问题,是错误就贴出错误信息,是效果不对,也可以说明下,说还是不行,没人会明白什么意思的
set rs_Product=server.createobject("adodb.recordset")
sqltext="SELECT A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu FROM [0791idc_Product] A LEFT JOIN(SELECT ID,MAX(Fenshu)Fenshu FROM P_pingjia GROUP BY ID)B ON A.ID=B.ID ORDER BY B.Fenshu DESC"
rs_Product.open sqltext,conn,1,1
if not rs_Product.EOF then%>
<li>
<p class="li_pic"><a href='Product_Show.asp?ID=<%=rs_Product("ID")%>' target=_blank title='<%=rs_Product("title")%>'>
<%if fileext="jpg" or fileext="bmp" or fileext="png" or fileext="gif" then%><img src="<%if rs_Product("DefaultPicUrl")="" then%>Img/nopic.gif<%else%><%=rs_Product("DefaultPicUrl")%><%end if%>" alt="<%=rs_Product("title")%>"></a></p>
<p class="li_tit"><a href="Product_Show.asp?ID=<%=rs_Product("ID")%>" target="_blank"><%=cutstr(rs_Product("title"),8)%></a><span>原价:<%=rs_Product("Spec")%>.00</span><em><%=rs_Product("Unit")%></em>补贴券</p>
<%end if%>
</li>
<%
rs_Product.MoveNext
wend
%>
<%
end if
rs_Product.close
set rs_Product=nothing
%>
调用出来的数据所有的ID都调出来了,但是ID,title,DefaultPicUrl,Spec,Unit的值获取不到,都为空值
MAX(fenshu) 后面、GROUP 前面加空格试试。
最好象我#2一样换行。
大概是不能省略表名或不能省略 AS
MAX(P_pingjia.fenshu) AS fenshu
SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu FROM [0791idc_Product] A
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu FROM P_pingjia GROUP BY ID)AS B ON A.ID=B.ID
ORDER BY B.Fenshu DESC
至少我想不出来如何在查询设计中进行这样的设计。
select * from A
Join (
select B.ID as BID,B.Fenshu,B.IP ,
row_number over(partition by B.ID order by B.Fenshu desc) as SN
) as d ON d.BID=A.ID
where d.SN=1
order by d.Fenshu desc--SQL 2000
select * from A
Join (
select B.ID AS BID,max(B.Fenshu) as fenshu from B
) as d on A.ID=d.BID
order by fenshu desc
SELECT A.ID,A.Title,A.Content,A.Picurl,A.Dianhua,A.QQ,A.Dizhi,A.UpdateTime,B.Fenshu1 FROM [0791idc_Product] A
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON A.ID=B.ID
ORDER BY B.Fenshu1 DESC那就取个不一样的别名
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID
ORDER BY B.Fenshu1 DESC这样试试
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID
ORDER BY B.Fenshu1 DESC直接TOP 5
select top 5 * from (SELECT TOP 5 A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu1 FROM [0791idc_Product] A
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID
ORDER BY B.Fenshu1 DESC)
这样写好像是可以
select top 5 * from (SELECT A.ID,A.title,A.DefaultPicUrl,A.Spec,A.Unit,B.Fenshu1 FROM [0791idc_Product] A
LEFT JOIN(SELECT ID,MAX(Fenshu)AS Fenshu1 FROM P_pingjia GROUP BY ID)AS B ON CStr(A.ID)=B.ID
ORDER BY B.Fenshu1 DESC)