以下为2表查询的语句
sql="select cp.*,qy.*,0 as px from cp,qy where cp.id in (select max(cp.id) from cp Where 1=1 group by cp.dy_id )and cp.dy_id=qy.id and (qy.vip<=3) union all select cp.*,qy.*,1 as px from cp,qy where cp.dy_id=qy.id and qy.key=1 order by px,qy.vip,cp.date DESC" 说明分别为cp,qy两个表,先查询qy表中VIP字段小于的3的每个用户发布的产品(cp)最新一张,然后再查询全部,这段语句本来在ACCEESS中使用时正确的,现在将数据库转换为SQLserver后,还能运行,但是部分字段读取就不正确了出错一:cp和qy表中都有id字段,本来ACCESS的时候<%=rs("cp.id")%>是能正常显示但是现在是空的
cp和qy表中都有pic字段,本来ACCESS的时候<%=rs("cp.pic")%>现在也是空的出错二:
在2表联合查询内部有这个语句
<%Set rs1 = Server.CreateObject ("ADODB.Recordset")
sql1 = "select id from cp where key=1 and SMT_sortid="&rs("sortid")&" and dy_id="&qy.id&" "
rs1.Open sql1,conn,1,1
sl=rs1.recordcount
rs1.close
set rs1=nothing%>现在sl=rs1.recordcount这个值都是0本来在ACESS都是能准确统计数据的
sortid为cp表中独有字段期待高手出现,谢谢!O(∩_∩)O
sql="select cp.*,qy.*,0 as px from cp,qy where cp.id in (select max(cp.id) from cp Where 1=1 group by cp.dy_id )and cp.dy_id=qy.id and (qy.vip<=3) union all select cp.*,qy.*,1 as px from cp,qy where cp.dy_id=qy.id and qy.key=1 order by px,qy.vip,cp.date DESC" 说明分别为cp,qy两个表,先查询qy表中VIP字段小于的3的每个用户发布的产品(cp)最新一张,然后再查询全部,这段语句本来在ACCEESS中使用时正确的,现在将数据库转换为SQLserver后,还能运行,但是部分字段读取就不正确了出错一:cp和qy表中都有id字段,本来ACCESS的时候<%=rs("cp.id")%>是能正常显示但是现在是空的
cp和qy表中都有pic字段,本来ACCESS的时候<%=rs("cp.pic")%>现在也是空的出错二:
在2表联合查询内部有这个语句
<%Set rs1 = Server.CreateObject ("ADODB.Recordset")
sql1 = "select id from cp where key=1 and SMT_sortid="&rs("sortid")&" and dy_id="&qy.id&" "
rs1.Open sql1,conn,1,1
sl=rs1.recordcount
rs1.close
set rs1=nothing%>现在sl=rs1.recordcount这个值都是0本来在ACESS都是能准确统计数据的
sortid为cp表中独有字段期待高手出现,谢谢!O(∩_∩)O
<%=rs("cp.pic")%>
都去掉表名<%=rs("id")%>
<%=rs("pic")%>
b.*,
0 AS px
FROM cp a,
qy b
WHERE a.id IN (SELECT MAX(id)
FROM cp
WHERE 1 = 1
GROUP BY dy_id)
AND cp.dy_id = qy.id
AND ( qy.vip <= 3 )
UNION ALL
SELECT x.*,
y.*,
1 AS px
FROM cp x,
qy y
WHERE x.dy_id = y.id
AND y.[KEY] = 1
ORDER BY px, --ORDER BY应该是是UNION ALL完了再执行
vip, --要求px, vip, DATE在cp和qy表中不能重复
[DATE] DESC
请问高手SELECT a.*,
b.*,
0 AS px
FROM cp a,
qy b
是建立临时表a,b吗?有什么好处?本人是菜鸟,不大懂,另外我这个语句以前运行都还可以的,现在ACESS这2个表数据上万条后,就会出现服务器CPU100%,我试了转为SQL后还是一样,应该怎么优化这句SQL语句才能提高查询的效率