找出十个上传产品最多的用户,然后接上传数由多到少排序。语句如下:Select Top 10 UserID,UserName,(Select Count(*) From [Product] Where Passed = True And UploadUserID=userID) As UploadFileNum From [User] a Where UserDel = 0 And LockUser = 0 Order by UploadFileNum Desc
数据库是ACCESS的,得到的sql 错误提示是:No value given for one or more required parameters. 这样写怎么总是提示说不正确呢?请高手指点。
数据库是ACCESS的,得到的sql 错误提示是:No value given for one or more required parameters. 这样写怎么总是提示说不正确呢?请高手指点。
(Select Count(*) From [Product] as b Where b.Passed = 'True' And b.UploadUserID=a.userID) As UploadFileNum From [User] AS a
Where UserDel = 0 And LockUser = 0
Order by UploadFileNum Desc这样试试
Select Top 10 UserID,UserName,(Select Count(*) From [Product] Where Passed = True And UploadUserID=userID) As UploadFileNum From [User] a Where UserDel = 0 And LockUser = 0) as tb
Order by UploadFileNum Desc那这样查呢
Select UserID,UserName,(Select Count(*) From [Product] Where Passed = True And UploadUserID=userID) As UploadFileNum From [User] a Where UserDel = 0 And LockUser = 0) as tb
Order by UploadFileNum Desc应该试这样,上一个逻辑不对
select Top 10 *
from (
Select UserID,UserName,
(Select Count(*)
From [Product] b
Where Passed = True
And b.UploadUserID=a.userID) As UploadFileNum
From [User] a
Where UserDel = 0
And LockUser = 0
) as tb
Order by UploadFileNum Desctry again
(Select Count(*)
From [Product] b
Where Passed = True
And b.UploadUserID=a.userID) As UploadFileNum
From [User] a 但独这句试试
from [User] m , [Product] n
where m.userID = n.UploadUserID and m.UserDel = 0 And m.LockUser = 0 and n.Passed = 'True'
group by m.UserID,m.UserName
order by UploadFileNum desc
Count(*) as UploadFileNum
From [User] a ,[Product] b
Where UserDel = 0 And LockUser = 0
and a.userID =b.UploadUserID and b.passed=true
group by a.UserID,a.UserName
Order by Count(*) Desc结果:UserID UserName UploadFileNum
11 小李9 2
9 小李7 2
8 小李6 2
7 小李5 2
5 小李3 2
4 小李2 2
3 小李1 2
2 小张 2
1 小王 2
10 小李8 1
6 小李4 1
ProductID UploadUserID ProductName Passed
1 1 娃哈哈1 Yes
2 2 娃哈哈1 Yes
3 3 娃哈哈1 Yes
4 4 娃哈哈1 Yes
5 5 娃哈哈1 Yes
6 6 娃哈哈1 Yes
7 7 娃哈哈1 Yes
8 8 娃哈哈1 Yes
9 9 娃哈哈1 Yes
10 10 娃哈哈1 Yes
11 11 娃哈哈1 Yes
12 1 娃哈哈1 Yes
13 2 娃哈哈1 Yes
14 3 娃哈哈1 Yes
15 4 娃哈哈1 Yes
16 5 娃哈哈1 Yes
17 6 娃哈哈1 No
18 7 娃哈哈1 Yes
19 8 娃哈哈1 Yes
20 9 娃哈哈1 Yes
21 11 娃哈哈1 Yes
User:
UserID UserName UserDel LockUser
1 小王 0 0
2 小张 0 0
3 小李1 0 0
4 小李2 0 0
5 小李3 0 0
6 小李4 0 0
7 小李5 0 0
8 小李6 0 0
9 小李7 0 0
10 小李8 0 0
11 小李9 0 0
数据好像是对的,但后面加了排序 Order by UploadFileNum Desc 就出错了
list the error ~~
这个是正解。感谢xys_777 如果有其它思路,欢迎继续发表
(select 11 as UploadFileNum from tb) t
Order by UploadFileNum Desc