create or replace view user_manager as
select t.userid,
t.nick,
(select s.username from account s where s.userid = t.userid) realname,
t.mobile,
t.email,
t.adddate,
t.agent,
t.lastlogin,
(select b.agent_name from agentinfo b where b.id = t.agent) name,
(select max(s.orderdate) from ticketorder s where s.userid=t.userid)lastvote
from userinfo t;
现在用起来很慢很慢 不知道如何优化
select t.userid,
t.nick,
(select s.username from account s where s.userid = t.userid) realname,
t.mobile,
t.email,
t.adddate,
t.agent,
t.lastlogin,
(select b.agent_name from agentinfo b where b.id = t.agent) name,
(select max(s.orderdate) from ticketorder s where s.userid=t.userid)lastvote
from userinfo t;
现在用起来很慢很慢 不知道如何优化
解决方案 »
- 求一个SQL语句,把所有表的主键都变成自增标识
- unpivot,pivot联合使用列类型不同时,转换失败的问题
- 如何在数据库表单的一行中从大到小取出前五个值
- !!!!!!!请教 sql语句!!!!!!!
- 高手莫要笑我,还是诚心请教!
- 急啊,我想重建日志文件,有什么办法????????帮帮啊,给200分,解决后再加
- sql server2000的企业管理器出了怪问题?
- 真怪呀!!!!!!!!!!!!!!!!
- MS Sql Server7.0 存储过程中RAISERROR出来的错误我怎样在程序里捕获它? (千山求教)欢迎UP
- 注意,怎么用存储过程建立触发器???
- 一个统计查询
- Report Service 中柱状图宽度
select t.userid,
t.nick,
s.username as realname,
t.mobile,
t.email,
t.adddate,
t.agent,
t.lastlogin,
b.agent_name as name,
max(s.orderdate) as lastvote
from userinfo t left join ticketorder s on s.userid=t.userid
left join agentinfo b on b.id = t.agent
left join account k on k.userid = t.userid
group by t.userid,
t.nick,
s.username ,
t.mobile,
t.email,
t.adddate,
t.agent,
t.lastlogin,
b.agent_name
难道真不是MSSQL
t.nick,
s.username AS realname,
t.mobile,
t.email,
t.adddate,
t.agent,
t.lastlogin,
b.agent_name AS name,
T1.orderdate AS lastvote
from userinfo t,account S ,
(select max(s.orderdate)AS , userid from ticketorder s GROUP BY userid)AS T1,agentinfo B
WHERE T.userid = S.userid AND T1.userid=T.userid AND T.agent=B.ID
A.UserName,I.Agent_Name,O.OrderDate as LastVote
from userinfo t Left Join Account A On T.UserID=A.UserID
Left Join AgentInfo I On T.Agent=I.ID
Left Join (Select UserID,Max(OrderDate) as OrderDate From TicketOrder Group By UserID) O On T.UserID=O.UserID**SQL Server语法,估计Oracle兼容
在TicketOrder上建个索引:UserID,OrderDate