以下是我的SQL语句:
===========================
select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and
order by
a.QuizDate
desc
=====================
现在查询的效果
a.QuizID DealerID Qtitle QuizDate b.QuizID BackText BackDate
10030 6125 搜狐好还是新浪好 2007-07-24 10030 新浪好 2007-08-22
10029 6125 网易好还是新浪好 2007-07-24 10029 网易好 2007-08-23
10029 6125 网易好还是新浪好 2007-07-24 10029 新浪好 2007-08-24
=====================
我想要的效果
a.QuizID DealerID Qtitle QuizDate b.QuizID BackText BackDate
10030 6125 搜狐好还是新浪好 2007-07-24 10030 新浪好 2007-08-22
10029 6125 网易好还是新浪好 2007-07-24 10029 新浪好 2007-08-24
=====================
也就是说根据BackDate这个时间字段取最新的一条!
请大伙帮我看看!!
===========================
select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and
order by
a.QuizDate
desc
=====================
现在查询的效果
a.QuizID DealerID Qtitle QuizDate b.QuizID BackText BackDate
10030 6125 搜狐好还是新浪好 2007-07-24 10030 新浪好 2007-08-22
10029 6125 网易好还是新浪好 2007-07-24 10029 网易好 2007-08-23
10029 6125 网易好还是新浪好 2007-07-24 10029 新浪好 2007-08-24
=====================
我想要的效果
a.QuizID DealerID Qtitle QuizDate b.QuizID BackText BackDate
10030 6125 搜狐好还是新浪好 2007-07-24 10030 新浪好 2007-08-22
10029 6125 网易好还是新浪好 2007-07-24 10029 新浪好 2007-08-24
=====================
也就是说根据BackDate这个时间字段取最新的一条!
请大伙帮我看看!!
(select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID ) t
where t.QuizID=
(select top 1 a.QuizID
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and a.QuizID=t.QuizID order by BackDate)
where datediff(day,BackDate,getdate)>2 //2为距离今天相差的天数
那你加个DISTINCT,不知道可以不?!
(select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID ) t
where t.QuizID=
(select top 1 a.QuizID
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and a.QuizID=t.QuizID order by BackDate)
=========================
运行出错呀!
服务器: 消息 8156,级别 16,状态 1,行 1
多次为 't' 指定了列 'QuizID'。
select DISTINCT
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and
order by
a.QuizDate
desc
你试试看,我不知道可不可以这样写
那你在SQL语句中 用聚合函数得到MAX时间 就可以实现你要的效果
===============
这样写查询出的还是3条结果呀!
========
你这样也是三条记录呀!
===
你这样也是三条记录呀!
========
哥们你++我QQ 47904648
-----------------------------------------
select * from
(select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID ) t
where t.QuizID=
(select top 1 a.QuizID
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and a.QuizID=t.QuizID order by BackDate)
order by
a.QuizDate
desc
(select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,a.Qip,
b.BackID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID ) t
where t.QuizID =
(select top 1 a.QuizID
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID and
a.QuizID=t.QuizID order by BackDate)
order by
a.QuizDate
desc
===========
写错两个地方
第一个
a.Qip,现在运行出错说
服务器: 消息 107,级别 16,状态 2,行 1
列前缀 'a' 与查询中所用的表名或别名不匹配。是不是这个地方得加a.QuizID=t.QuizID order by BackDate)
a.QuizID=t.QuizID order by b.BackDate)
??
from ....
order by ...
(select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.QuizID,b.DealerID,b.BackDate,b.BackText,
c.DealerID,c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID) tselect * from #temp t where not exists(select 1 from #temp where QuizID=t.QuizID and BackDate<t.BackDate)
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.BackDate,b.BackText,
c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID
and NOT EXISTS
(SELECT *
FROM (select
a.QuizID,a.DealerID,a.Qtitle,a.Qtext,a.QuizDate,Qip,
b.BackID,b.BackDate,b.BackText,
c.user_dw,c.user_tel
from
AutoQuiz a,AutoQuizBack b,AutoDealer c
where
a.DealerID = 6125 and
a.DealerID = b.DealerID and
a.QuizID = b.QuizID and
a.DealerID = c.DealerID ) tt
where tt.QuizID=a.QuizID and tt.DealerID=a.DealerID and tt.BackDate>b.BackDate )
order by
a.QuizDate
desc