sql 语句问题

[canzai]

select ming,[time] from
(
select distinct ming,max([time]) as [time] from
(select distinct ming,max([time]) as [time] from a group by ming
union select distinct ming,max([time]) as [time] from b group by ming) table3
group by ming
) table4
order by [time] desc


上面这个语句是把重复的记录过滤掉，只显示ming的内容。
可现在我还要显示其它的字段aaa,bbb,ccc
如果我写成下面的语句，就不会过滤重复的记录，而是显示包括重复的记录，怎么改才能既过滤重复的记录，又能同时取出ming,aaa,bbb,ccc 这些字段？

select ming,[time] from
(
select distinct ming,max([time]) as [time] from
(select distinct ming,max([time]) as [time] from a group by ming
union select distinct ming,max([time]) as [time] from b group by ming) table3
group by ming
) table4
order by [time] desc

[canzai]

自己顶一下

[hzhijun]

强顶!

[cc25485697]

两个语句貌似一样的。。。

[canzai]

能不能写出来看一下

[canzai]

我写成下面这样是错的：
select ming,[time] from
(
select distinct ming,aaa,bbb,ccc,max([time]) as [time] from
(select distinct ming,aaa,bbb,ccc,max([time]) as [time] from a group by ming,aaa,bbb,ccc
union select distinct ming,aaa,bbb,ccc,max([time]) as [time] from b group by ming,aaa,bbb,ccc) table3
group by ming,aaa,bbb,ccc
) table4
order by [time] desc

请问要怎么才能既过滤重复的记录，又能同时取出ming,aaa,bbb,ccc 这些字段？

[一把编程的菜刀]

select distinct(ming),max([time]) as [time],aaa,bbb,ccc from table
不知道是不是这样的意思

[canzai]

运行后提示错误：
Microsoft JET Database Engine (0x80040E21)
试图执行的查询中不包含作为合计函数一部分的特定表达式 'ming' 。

[canzai]

高手呢？？？

[一把编程的菜刀]

<%sql="select distinct(musicid),userid,[date],id from xztj"
Set rs=Server.CreateObject("Adodb.RecordSet")
rs.Open sql,conn,1,1
if not rs.bof then
do while not rs.eof
response.Write rs("id")&":"&rs("musicid")&":"&rs("userid")&":"&rs("date")&"<br>"
rs.movenext
loop

end if%>
这是我测试的语句

一点问题都没有

[一把编程的菜刀]

<%
sql="select * from xztj where id in (select max(id) from xztj group by musicid)"
Set rs=Server.CreateObject("Adodb.RecordSet")
rs.Open sql,conn,1,1
if not rs.bof then
do while not rs.eof
response.Write rs("id")&":"&rs("musicid")&":"&rs("userid")&":"&rs("date")&"<br>"
rs.movenext
loop

end if%>

或者这样

[Lee2K]

有点长.乱乱的.建议你可以使用两个sql语句来判断,不要一个select中嵌套一个.这样容易混乱.找不出问题所在,