select top 50 a.id,a.userid,a.loan,a.states,a.loandate,b.userMoney,b.userName
from p_bank a inner join users b on a.userid=b.userid
join (select max(id) id from p_bank group by userid) c on a.id=c.id
where a.states=1 and a.kind=2
order by a.loan_balance
from p_bank a inner join users b on a.userid=b.userid
join (select max(id) id from p_bank group by userid) c on a.id=c.id
where a.states=1 and a.kind=2
order by a.loan_balance
最好是哪两个表的create语句
join (select max(id) as id from p_bank group by userid) c 完整,如果不行再加个as ,
CREATE TABLE [dbo].[p_bank] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[userid] [int] NULL ,
[savemoney] [int] NULL ,
[savedate] [datetime] NULL ,
[putdate] [datetime] NULL ,
[states] [bit] NULL ,
[kind] [int] NULL ,
[loan] [int] NULL ,
[loandate] [datetime] NULL ,
[loanback] [datetime] NULL ,
[balance] [int] NULL ,
[loan_balance] [int] NULL
) ON [PRIMARY]users的
CREATE TABLE [dbo].[users] (
[userId] [int] IDENTITY (1, 1) NOT NULL ,
[userName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userPassWord] [varchar] (35) COLLATE Chinese_PRC_CI_AS NULL ,
[question] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[answer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userEmail] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[realName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[userSex] [int] NULL ,
[userCode] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[userPhone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[birthDay] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[oicq] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[homePage] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[headPic] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[width] [int] NULL ,
[height] [int] NULL ,
[userFun] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[signing] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
[itrdct] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
[userJob] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userJobId] [int] NULL ,
[userSection] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userSectionId] [int] NULL ,
[userCompany] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userCompanyId] [int] NULL ,
[articleNum] [int] NULL ,
[replyNum] [int] NULL ,
[delArticleNum] [int] NULL ,
[loginNum] [int] NULL ,
[lastLogin] [datetime] NULL ,
[addDate] [datetime] NULL ,
[lock] [bit] NULL ,
[hidden] [bit] NULL ,
[userLastIp] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[userMagic] [int] NULL ,
[userExperience] [int] NULL ,
[userMoney] [int] NULL ,
[userStrength] [int] NULL ,
[lover] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[loverId] [int] NULL ,
[userSetting] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
合法的数据全部显示出来了,哪位高手能解释一下原理?:)