请大伙来看看我的SQL语句问题！顶这有分！不够+++！SOS

[net_lhb]

以下是我的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这个时间字段取最新的一条!
请大伙帮我看看！！

[welove1983]

select t.* into #temp from
(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

select * from #temp t where not exists(select 1 from #temp where QuizID=t.QuizID and BackDate<t.BackDate)

[zhchg6666]

up

[net_lhb]

up

[xtra6714]

select * from
(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)

[tds__609]

可以考虑
where datediff(day,BackDate,getdate)>2 //2为距离今天相差的天数

[小李251号]

是要不相同的两条,并且是最新的吗?
那你加个DISTINCT,不知道可以不?!

[xu050615]

我也存在这个问题。。。哪位高手能解决

[net_lhb]

select * from
(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'。

[小李251号]

即:
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
你试试看,我不知道可不可以这样写

[zwwlovezy1]

select xx,...,max(BackDate)as BackDate from xx where xxx group by xx,xx,... having count(*)>=1

因为时间没有重复 所以显示不出你要的效果
那你在SQL语句中 用聚合函数得到MAX时间 就可以实现你要的效果

[net_lhb]

ljf_1314520()
===============
这样写查询出的还是3条结果呀！

[net_lhb]

zwwlovezy1(Eminem)
========
你这样也是三条记录呀！

[net_lhb]

zwwlovezy1(Eminem)
===
你这样也是三条记录呀！

[小李251号]

哦,不错!!

[zwwlovezy1]

你把你表结构的代码 发过来 我自己填几条数据帮你写写

[net_lhb]

zwwlovezy1(Eminem)
========
哥们你++我QQ 47904648

[zwwlovezy1]

加了

[xtra6714]

光等着别人喂不太好吧


－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－


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

[net_lhb]

select * from
(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)
??

[Dlugger]

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
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