帮忙修改一下SQL 语句

[psuo]

SELECT d.subject,d.id,m.spacedir,m.nickname
FROM diarys d,members m
WHERE d.member=m.username and (d.is_recommended=1 or m.vip=1 )
ORDER BY d.id DESC LIMIT 12

diarys 是博客表存放博客内容
members 是会员表

is_recommended=1表示文章已审核
vip=1表示该会员是VIP会员

VIP的文章无需审核直接可以显示，普通会员的文章需要才可以显示、

现在要获取最后发表的12条数据但是条件是vip会员的文章或者普通会员的已审核文章（无论VIP还是普通会员只能显示一条并且是最后发表的）

[jacklinchen]

SELECT top 12 d.subject,d.id,m.spacedir,m.nickname
FROM diarys d,members m
WHERE d.member=m.username and (d.is_recommended=1 or m.vip=1 )
ORDER BY d.id DESC

[lgxyz]

表结构贴出来吧
这样比较方便
一看就知道

[psuo]

members表
id int(11) 否 auto_increment
username varchar(15) utf8_general_ci 否
password char(32) utf8_general_ci 否
nickname varchar(30) utf8_general_ci 否
icon varchar(100) utf8_general_ci 否
gender tinyint(4) 否
email varchar(50) utf8_general_ci 是 NULL
oicq varchar(15) utf8_general_ci 否
description text utf8_general_ci 是 NULL
area_id int(11) 否 1
regdate int(11) 否
has_space tinyint(3) 否 0
has_group tinyint(3) 否 0
spacedir varchar(15) utf8_general_ci 是 NULL
groupdomain varchar(50) utf8_general_ci 是 NULL
lastdate int(10) UNSIGNED 否
mstatus tinyint(2) UNSIGNED 否 0
mremind tinyint(1) UNSIGNED 否 0
vip varchar(10) utf8_general_ci 是 0

diarys 表
id int(10) UNSIGNED 否 auto_increment
author varchar(30) utf8_general_ci 否
member varchar(15) utf8_general_ci 否
subject varchar(80) utf8_general_ci 否
message mediumtext utf8_general_ci 否
allow_comment tinyint(4) 否 1
postdate int(10) UNSIGNED 否
views int(10) UNSIGNED 否
commets int(10) UNSIGNED 否
ups int(5) UNSIGNED 否 0
downs int(5) UNSIGNED 否 0
summary text utf8_general_ci 否
class_id int(10) UNSIGNED 否 1
private tinyint(3) 否 1
readed tinyint(3) UNSIGNED 否 0
is_recommended tinyint(3) UNSIGNED 否 0
is_hot tinyint(2) UNSIGNED 否 1

[pt1314917]

--没数据，随手写的。。try:
select top 12 d.subject,d.id,m.spacedir,m.nickname from diarys a,members m
where (is_recommended=1 or m.vid=1) and d.member=m.username
and id in(select top 1 id from diary where member=a.member and (d.is_recommended=1 or m.vid=1) order by id desc)

[psuo]

SELECT d.subject,d.id,m.spacedir,m.nickname
FROM diarys d,members m
WHERE d.member=m.username and (d.is_recommended=1 or m.vip=1 )
ORDER BY d.id DESC LIMIT 12

diarys 是博客表存放博客内容
members 是会员表

is_recommended=1表示文章已审核
vip=1表示该会员是VIP会员

VIP的文章无需审核直接可以显示，普通会员的文章需要才可以显示、

现在要获取最后发表的12条数据但是条件是vip会员的文章或者普通会员的已审核文章（无论VIP还是普通会员只能显示一条并且是最后发表的）
表结构如下
members表
id username password nickname icon gender email oicq description area_id regdate has_space
spacedir groupdomain lastdate mstatus mremind vip
diarys 表
id author member subject message allow_comment postdate views commets ups downs summary class_id private
readed is_recommended is_hot

[psuo]

这样不行啊

我要显示12篇最新的文章，一个作者的文章只能显示最新一篇。

[jonsondb]

将表结构贴出来,这样容易处理

[psuo]

救命啊没人会吗？

[ojuju10]

--增加一个重复记录只保留一条的限制条件
SELECT d.subject,d.id,m.spacedir,m.nickname 
                FROM diarys d,members m 
                    WHERE d.member=m.username and (d.is_recommended=1 or m.vip=1 ) 
                         and not exists(select 1 from diarys n where d.username=n.username
                                                  and d.id<n.id)
                        ORDER BY d.id DESC LIMIT 12