请邹健大哥进，存储过程分页与多表连接的问题

[sywcf]

我的数据库中的两个表：m表与s表
member表字段:
Id,Name,Email
stat表字段:
MId(m表Id),Number(登录次数),LoginTime(登录时间)

如果会员登录过我的系统，则在stat表中一定能找到MId,否则不能找到。

现在我想把所有会员都取出，但要按登录次数的多少排序,登录次数多的在上面,登录次数少的或者根本就没有登录过的放在下面,怎样写出sql语句呢？

这个语句要符合您(邹健)的那个存储过程分页程序的要求，因为我现在就在用您的存储过程分页程序呢。

[fesxe]

select a.ID,a.Name,isnull(Number,0) from member left join Mid b on b.mid=a.id

[fesxe]

select a.ID,a.Name,isnull(Number,0) from member left join Mid b on b.mid=a.id
order by 3 desc

[sywcf]

我要显示的是：stat表中的字段。

[sywcf]

多谢fesxe(想飞的鸟)

[sywcf]

我想显示：MId(m表Id),Number(登录次数),LoginTime(登录时间)，怎么写啊？

[fesxe]

select a.MId,a.Name,isnull(Number,0)as Number,LoginTime from member left join Mid b on b.mid=a.id
order by 3 desc


isnull(Number,0)这个就登录次数.

[sywcf]

我想显示：MId(m表Id),Number(登录次数),LoginTime(登录时间)这个啊。

[sywcf]

fesxe(想飞的鸟) ，再帮我看看，

select a.MId,a.Name,isnull(Number,0)as Number,LoginTime from member left join Mid b on b.mid=a.id
order by 3 desc


isnull(Number,0)这个就登录次数.

这个不行啊

提示：

服务器: 消息 107，级别 16，状态 3，行 4
The column prefix 'stat' does not match with a table name or alias name used in the query.
服务器: 消息 107，级别 16，状态 1，行 4
The column prefix 'stat' does not match with a table name or alias name used in the query.

[fesxe]

select a.MId,a.Name,isnull(b.Number,0)as Number,LoginTime from member a left join Mid b on b.mid=a.id
order by 3 desc

[fesxe]

select b.MId,a.Name,isnull(b.Number,0)as Number,b.LoginTime from member a left join Mid b on b.mid=a.id
order by 3 desc

[sywcf]

多谢： fesxe(想飞的鸟)，

select a.ID as mid,isnull(b.登录次数,0) as 登录次数 from member a left join stat b on b.会员表序号=a.序号 order by 2 desc

这个好用了。

[sywcf]

fesxe(想飞的鸟)
这句还是不行啊，出错啊。还是上面的错误

[fesxe]

select b.MId,a.Name,isnull(b.Number,0)as Number,b.LoginTime from member a left join stat b on b.mid=a.id
order by 3 desc

[syspring]

member表：序号,会员名
stat表：会员表序号,登录次数,登录时间

select b.会员表序号,member.会员名,isnull(stat.登录次数,0)as 登录次数,stat.登录时间 from member a left join stat b on b.会员表序号=a.序号 order by 3 desc

这样写有问题吗?

我在查询分析器中提示

服务器: 消息 107，级别 16，状态 3，行 1
The column prefix 'stat' does not match with a table name or alias name used in the query.
服务器: 消息 107，级别 16，状态 1，行 1
The column prefix 'member' does not match with a table name or alias name used in the query.
服务器: 消息 107，级别 16，状态 1，行 1
The column prefix 'stat' does not match with a table name or alias name used in the query.
服务器: 消息 107，级别 16，状态 1，行 1
The column prefix 'stat' does not match with a table name or alias name used in the query.

[sywcf]

to : fesxe(想飞的鸟)
我把语句改成这样好用了：

select b.会员表序号,isnull(b.登录次数,0) as 登录次数 from member a left join stat b on b.会员表序号=a.序号 order by 2 desc

但是：结果中显示的是，如果登录次数为0时，会员表序号等其它字段就变成null了，这样不是我要的啊。

[sywcf]

再等

[fesxe]

select isnull(b.会员表序号,0),isnull(b.登录次数,0) as 登录次数 from member a left join stat b on b.会员表序号=a.序号 order by 2 desc

或者

select a.ID as mid,isnull(b.登录次数,0) as 登录次数 from member a left join stat b on b.会员表序号=a.序号 order by 2 desc