首页新闻找找看学习计划

绞尽脑汁找不出下列SQL错误的原因,多谢

0
悬赏园豆:20 [已解决问题] 解决于 2009-01-07 15:07

执行下列语句时:

SELECT a.id, a.Name, b.Userii FROM books a, (SELECT MAX(borrow.UserId) AS Userii FROM borrow WHERE borrow.BookId=a.id) b

出现错误: The multi-part identifier "a.id" could not be bound.

Books: CREATE TABLE [dbo].[Books]

(

[ID] [int] NOT NULL,

[Name] [nchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL,

[Price] [decimal] (8, 2) NOT NULL

)

 

-- Columns

CREATE TABLE [dbo].[Borrow]

(

[UserId] [int] NOT NULL,

[BookId] [int] NOT NULL

)

 

 

 

 

 

 

星雨轩的主页 星雨轩 | 初学一级 | 园豆:170
提问于:2009-01-03 19:17
< >
分享
最佳答案
0

SELECT a.id, a.Name, b.Userii
FROM books a,
(SELECT MAX(borrow.UserId) AS Userii FROM borrow, books a WHERE borrow.BookId=a.id) b

--//在子查询里没有a.加上就行。最后那个userii是所有记录是最大的那个。。。。

--//也可以用下面的方法:

select a.id, a.Name, max(b.UserId) as Userii
from  books a inner join borrow b on b.BookId=a.id
group by a.id,a.Name

//userii是同一a.id中userid最大的。

看不出你这个SQL的目的是什么。。。

BB_Coder | 小虾三级 |园豆:895 | 2009-01-04 14:22
其他回答(2)
0

SELECT a.id, a.Name, b.Userii FROM books a, (SELECT MAX(borrow.UserId) AS Userii FROM borrow WHERE borrow.BookId=a.id) b

错误的原因就在于您括号中的子查询。您看下你的语句改成这样可否?

Select Top 1 a.id,a.Name,b.UserID From borrow b Inner Join books a On a.id=b.BookID Order by b.UserID DESC

吴辉军 | 园豆:240 (菜鸟二级) | 2009-01-03 19:50
0

SELECT a.id, a.Name, (SELECT MAX(borrow.UserId) AS Userii FROM borrow WHERE borrow.BookId=a.id) As Userii 

FROM books a 

Leox | 园豆:461 (菜鸟二级) | 2009-01-03 20:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册