select b.user_id,name=(select name from user where id=a.user_id), count=sum(a.count) from counter a group by user_id order by sum(count)
create table [user](id int, name varchar(5)) insert into [user] select 1, 'a' union all select 2 , 'b' union all select 3 , 'c' create table counter(user_id int, date datetime, count int) insert into counter select 1 , '2005-1-1' , 33 union all select 2 , '2005-1-1' , 21 union all select 3 , '2005-1-1' , 12 union all select 1 , '2005-1-2' , 76 union all select 2 , '2005-1-2' , 221 union all select 3 , '2005-1-2' , 112 union all select 1 , '2005-1-3' , 72 union all select 2 , '2005-1-3' , 22 union all select 3, '2005-1-3', 32 ----------------下面是實現語句---------------------- select a.user_id,name=(select name from [user] where id=a.user_id), count=sum(a.count) from counter a group by user_id order by sum(count) -----------------結果------------------------3 c 156 1 a 181 2 b 264
select aa.* from (select a.user_id,b.name,sum(count) as count from counter a inner join user b on a.user_id = b.id where a.date between '2005-1-1' and '2005-1-3' ) aa order by aa.count
select user_id,name,sum(count) from counter,[user] where counter.user_id=[user].id group by user_id,name order by sum(count)
SELECT * FROM [user] a order by (SELECT sum([count]) FROM counter where [user_id]=a.id) desc
--或者: SELECT a.* FROM [user] a,(SELECT [user_id],cnt=sum([count]) FROM counter group by [user_id])b where b.[user_id]=a.id order by b.cnt desc
挖,邹老大也来了,果然厉害啊。只是有一点不是很理解,我比较菜,不要拍我,上述给的sql语句中a、b代表什么意思啊?是建立的一个临时表么?为什么要这样写呢?如下写也可以吧?是因为前者效率高么?SELECT * FROM [user] order by (SELECT sum([count]) FROM counter where [user_id]=user.id) desc
SELECT user_id, SUM(COUNT) AS count1 FROM counter WHERE work_date BETWEEN '2005 - 1 - 1' AND '2005 - 1 - 3' GROUP BY user_id 我把counter表的date变成了work_date了,因为date是关键字。这个语句已经调试完。 要显示name可以用圈套,也可以两表连接。
谁能帮我解释一下啊:上述给的sql语句中a、b代表什么意思啊?是建立的一个临时表么?为什么要这样写呢?如下写也可以吧?是因为前者效率高么?SELECT * FROM [user] order by (SELECT sum([count]) FROM counter where [user_id]=user.id) desc
count=sum(a.count) from counter a group by user_id order by
sum(count)
create table [user](id int, name varchar(5))
insert into [user]
select 1, 'a' union all
select 2 , 'b' union all
select 3 , 'c'
create table counter(user_id int, date datetime, count int)
insert into counter
select 1 , '2005-1-1' , 33 union all
select 2 , '2005-1-1' , 21 union all
select 3 , '2005-1-1' , 12 union all
select 1 , '2005-1-2' , 76 union all
select 2 , '2005-1-2' , 221 union all
select 3 , '2005-1-2' , 112 union all
select 1 , '2005-1-3' , 72 union all
select 2 , '2005-1-3' , 22 union all
select 3, '2005-1-3', 32
----------------下面是實現語句----------------------
select a.user_id,name=(select name from [user] where id=a.user_id),
count=sum(a.count) from counter a group by user_id order by
sum(count)
-----------------結果------------------------3 c 156
1 a 181
2 b 264
SELECT * FROM [user] a
order by (SELECT sum([count]) FROM counter where [user_id]=a.id) desc
SELECT a.* FROM [user] a,(SELECT [user_id],cnt=sum([count]) FROM counter group by [user_id])b
where b.[user_id]=a.id
order by b.cnt desc
order by (SELECT sum([count]) FROM counter where [user_id]=user.id) desc
FROM counter
WHERE work_date BETWEEN '2005 - 1 - 1' AND '2005 - 1 - 3'
GROUP BY user_id
我把counter表的date变成了work_date了,因为date是关键字。这个语句已经调试完。
要显示name可以用圈套,也可以两表连接。
order by (SELECT sum([count]) FROM counter where [user_id]=user.id) desc