这里有个SQL语句。大家看看效率如何？有没有更优形式？进者有分
如题
String sql = " ";
sql = sql +
"select isnull(AcctName,CardUser.UserName) as name,t3.UserId as UserId,mapTab.area1 " +
"area1,mapTab.area2 area2,Sed810.Sed810Name,t3.UserId as UserId,t3.CardType,t3.InTime,t3.OutTime " +
"from( " +
"select " +
"ISNULL(t1.UserId, t2.UserId) as UserId, " +
"ISNULL(t1.Sed810Id, t2.Sed810Id) as Sed810Id, " +
"ISNULL(t1.CardType, t2.CardType) as CardType, " +
"t1.LogTime as InTime, t2.LogTime as OutTime " +
"from ( " +
"select * " +
",(select count(*) from CardLog where UserId=a.UserId and " +
"Sed810Id=a.Sed810Id and CardType=a.CardType and " +
"LogTime <=a.LogTime and LogTime> ' " + stime + " ' and " +
"LogTime <= ' " + etime + " ') as no " +
"from CardLog a where LockPart=1 and LogTime> ' " + stime +
" ' and " +
"LogTime <= ' " + etime + " ' " +
") as t1 full join " +
"( " +
"select * " +
",(select count(*) from CardLog where UserId=a.UserId and " +
"Sed810Id=a.Sed810Id and CardType=a.CardType and " +
"LogTime <=a.LogTime and LogTime> ' " + stime + " ' and " +
"LogTime <= ' " + etime + " ') as no " +
"from CardLog a where LockPart=2 and LogTime> ' " + stime +
" ' and " +
"LogTime <= ' " + etime + " ' " +
") as t2 " +
"on t1.UserId=t2.UserId " +
"and t1.Sed810Id=t2.Sed810Id " +
"and t1.CardType=t2.CardType " +
"and t2.no=t1.no+1 " +
") as t3 " +
"left join Sed810 on Sed810.Sed810Id = t3.Sed810Id " +
"left join ( " +
"select areaTab.area1 area1,areaTab.area2 area2,AreaMap.Sed810Id " +
"Sed810Id " +
"from AreaMap " +
"left join ( " +
"select first.AreaID areaId,first.AreaName area1,second.AreaName area2 " +
"from Area as first " +
"left join Area as second " +
"on first.AreaParent=second.AreaID " +
") as areaTab " +
"on AreaMap.AreaId=areaTab.areaId where AreaMap.AreaId in ( ";
for (int i = 0; i < vec2.size(); i++) {
if (i != 0) {
sql = sql + ", ";
}
sql = sql + ((Integer) vec2.elementAt(i)).intValue();
}
sql = sql + ") ";
sql = sql + ") as mapTab " +
"on t3.Sed810Id=mapTab.Sed810Id " +
"left join CardUser on t3.UserId=CardUser.UserId and t3.CardType=0 " +
"left join Acct on t3.UserId=Acct.AcctId and (t3.CardType=2 or " +
"t3.CardType=3) where ( ";
if (turn == 3 || turn == 2) {
sql = sql + "((t3.CardType=2 or t3.CardType=3) ";
if (v4 == false) {
sql = sql + " and t3.UserId in ( ";
for (int i = 0; i < vec4.size(); i++) {
if (i != 0) {
sql = sql + ", ";
}
sql = sql + ((Integer) vec4.elementAt(i)).intValue();
}
sql = sql + ") ";
}
sql = sql + ") ";
}
if (turn == 3) {
sql = sql + " or ";
}
if (turn == 1 || turn == 3) {
sql = sql + "((t3.CardType=0 ";
if (v1 == false) {
sql = sql + " and t3.UserId in ( ";
for (int i = 0; i < vec1.size(); i++) {
if (i != 0) {
sql = sql + ", ";
}
sql = sql + ((Integer) vec1.elementAt(i)).intValue();
}
sql = sql + ") ";
}
if (com == false) {
sql = sql + " and t3.UserId in ( ";
for (int i = 0; i < comUser.size(); i++) {
if (i != 0) {
sql = sql + ", ";
}
sql = sql + ((Integer) comUser.elementAt(i)).intValue();
}
sql = sql + ") ";
}
sql = sql + ") ";
if ((v1 && v4 && com) == true) {
sql = sql + " or (t3.CardType=1) ";
}
sql = sql + ") ";
}
sql = sql + ") and t3.Sed810Id in ( ";
for (int i = 0; i < vec3.size(); i++) {
if (i != 0) {
sql = sql + ", ";
}
sql = sql + ((Integer) vec3.elementAt(i)).intValue();
}
sql = sql + ") ";
sql = sql + "order by isnull(t3.InTime, t3.OutTime) Desc ";
[解决办法]
sf
[解决办法]
哥哥，别闹了。这么多，想优化都难。
你就说说你想实现什么功能吧
[解决办法]
这么长的SQL能跑起来吗?把你的SQL分成几步吧.
[解决办法]
好长的语句
牛人
[解决办法]
太长些了吧
