1.先根据用户id,group物品表创建视图 Create View dbo.wpsum as select id,sum(物品数量字段) as wp from 物品表 group by id2.select * from 用户表 inner join wpsum on 用户表.id=wpsum.id where email='[email protected]' on wp desc
SELECT [user],[itemcount],[place] FROM (SELECT [user],SUM[item] AS [itemcount],IDENTITY(INT,1,1) AS [palce] FROM table_name GROUP by [user] ORDER BY SUM[item]) WHERE [user]=@user
少了个括号~SELECT [user],[itemcount],[place] FROM (SELECT [user],SUM([item]) AS [itemcount],IDENTITY(INT,1,1) AS [palce] FROM table_name GROUP by [user] ORDER BY SUM[item]) WHERE [user]=@user
1.你可以创建一个视图,里面包括用户的基本信息,然后还包括用户的物品总数. select username,email,(select count(1) from Product p where p.UserID=UserID) as totalProduct from useraccount2.然后利用sql server 2005带的rank函数进行排名 select totalProduct,Rank() OVER(ORDER BY totalProduct Desc) from 视图 where email='[email protected]'or Dense_Rank()
还是少括号,这样:SELECT [user],[itemcount],[place] FROM (SELECT [user],SUM([item]) AS [itemcount],IDENTITY(INT,1,1) AS [palce] FROM table_name GROUP by [user] ORDER BY SUM([item])) WHERE [user]=@user
6楼的脚本好像不行。 identity只能在有into时才可以使用。 select identity(int,1,1) as id,* into #temp from tb where...
如果你使用的sql server 2005,建议用rank
楼主写个存储过程吧。 试下这个吧: create procedure getuserinfo @user as varchar(100) as drop table #temp select identity(int,1,1) as id,count(b.*) c into #temp,a.email from userinfo a,物品表 b where a.id=b.userid group by a.emailselect * from #temp where email='[email protected]' go
if object_id('tempdb..#tmp') is not null drop table #tmp SELECT XXX as 用户ID, count(*) AS 物品数,IDENTITY(INT,1,1) AS 排名 into #tmp from 物品表 group by 用户ID order by 物品数 desc select * from 用户表 inner join #tmp on 用户表.ID=#tmp.用户ID where email='XXXXXXXX' 用临时表吧,我试过了,可以
把六楼的改了下 你可以试试SELECT [user],SUM([item]) AS [itemcount],IDENTITY(INT,1,1) AS [palce] into #a FROM table_name GROUP by [user] SELECT [user],[itemcount],[place] FROM #a ORDER BY SUM([item])) WHERE [user]=@user
select id,(select sum(物品数量字段) from 物品表 a where a.用户=b.用户) as s from 用户表 where email='[email protected]' on s desc
select u.id,u.name,sum(wp.query) as w from user123 as u join wp on u.id=wp.userid where u.email='[email protected]' group by wp.userid,u.id,u.name order by w desc试过可以
Create View dbo.wpsum
as
select id,sum(物品数量字段) as wp from 物品表 group by id2.select * from 用户表 inner join wpsum on 用户表.id=wpsum.id
where email='[email protected]' on wp desc
FROM (SELECT [user],SUM[item] AS [itemcount],IDENTITY(INT,1,1) AS [palce]
FROM table_name GROUP by [user] ORDER BY SUM[item])
WHERE [user]=@user
FROM (SELECT [user],SUM([item]) AS [itemcount],IDENTITY(INT,1,1) AS [palce]
FROM table_name GROUP by [user] ORDER BY SUM[item])
WHERE [user]=@user
select username,email,(select count(1) from Product p where p.UserID=UserID) as totalProduct from useraccount2.然后利用sql server 2005带的rank函数进行排名 select totalProduct,Rank() OVER(ORDER BY totalProduct Desc) from 视图 where email='[email protected]'or Dense_Rank()
FROM (SELECT [user],SUM([item]) AS [itemcount],IDENTITY(INT,1,1) AS [palce]
FROM table_name
GROUP by [user]
ORDER BY SUM([item]))
WHERE [user]=@user
identity只能在有into时才可以使用。
select identity(int,1,1) as id,* into #temp from tb where...
试下这个吧:
create procedure getuserinfo
@user as varchar(100)
as
drop table #temp
select identity(int,1,1) as id,count(b.*) c into #temp,a.email
from userinfo a,物品表 b
where a.id=b.userid
group by a.emailselect * from #temp where email='[email protected]'
go
http://topic.csdn.net/u/20080501/11/1ef3af67-9358-4104-9bcf-03955b4d4551.html
if object_id('tempdb..#tmp') is not null drop table #tmp
SELECT XXX as 用户ID, count(*) AS 物品数,IDENTITY(INT,1,1) AS 排名 into #tmp
from 物品表 group by 用户ID order by 物品数 desc
select * from 用户表 inner join #tmp on 用户表.ID=#tmp.用户ID where email='XXXXXXXX'
用临时表吧,我试过了,可以
FROM table_name
GROUP by [user] SELECT [user],[itemcount],[place]
FROM #a
ORDER BY SUM([item]))
WHERE [user]=@user
from 用户表 where email='[email protected]' on s desc
where u.email='[email protected]'
group by wp.userid,u.id,u.name
order by w desc试过可以