绞尽脑汁找不出下列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
)
最佳答案
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的目的是什么。。。
其他回答(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
0
SELECT a.id, a.Name, (SELECT MAX(borrow.UserId) AS Userii FROM borrow WHERE borrow.BookId=a.id) As Userii
FROM books a
