create table tb(数量 int,编号 int) insert into tb select 50,1 insert into tb select 70,1 insert into tb select 30,2 insert into tb select 80,3select top 1 编号 from tb group by 编号 order by count(编号) desc1
create table tb(数量 int,编号 int) insert into tb select 50,1 insert into tb select 29,1 insert into tb select 30,2 insert into tb select 80,3select top 1 编号 from tb group by 编号 order by sum(数量) desc3
select 编号 from test WHERE 数量=(select max(数量) from test)????
declare @tb table (数量 int,编号 int) insert into @tb select 50,1 insert into @tb select 30,1 insert into @tb select 70,2 insert into @tb select 80,3select 编号 from (select 编号,sum(数量) as 数量 from @tb group by 编号) a WHERE 数量=(select max(数量) from @tb) /* 编号 ----------- 1 3(2 行受影响) */
应该是这样:declare @tb table (数量 int,编号 int) insert into @tb select 50,1 insert into @tb select 30,1 insert into @tb select 70,2 insert into @tb select 80,3select 编号 from @tb group by 编号 having sum(数量) = (select top 1 sum(数量) as 数量 from @tb group by 编号 order by 数量 desc) /* 编号 ----------- 1 3(2 行受影响) */
原来也可以from的后面跟一个子查询?OK,就说这个可以吧,不过这还不符合题意,题里面的一开头就已经固定了,select 编号 from 这样的格式了只能在from后面补上
那你要用那個,可以再加一層,o(∩_∩)o............汗死了select 編號 from ( select top 1 編號 form ( select 编号,數量=sum(數量) from p1 group by 编号 )A order by 數量 desc ) T
create table p1 table (数量 int,编号 int) insert into p1 select 50,1 insert into p1 select 30,1 insert into p1 select 70,2 insert into p1 select 80,3 select 编号 from p1 group by 编号 having sum(数量) = (select top 1 sum(数量) as 数量 from p1 group by 编号 order by 数量 desc) /* 编号 ----------- 1 3 (2 行受影响) */
--最直观的,不知上面有吗,其它方法大家想 select 编号 from p1 a group by 编号 having sum(数量)=(select max(num) from (select sum(数量) num from p1 group by 编号)A)
我在14楼就已经有答案了select 编号 from p1 group by 编号 having sum(数量) = (select top 1 sum(数量) as 数量 from p1 group by 编号 order by 数量 desc)
20分这么多人,本不想进,这不看到sdxiong发现问题了吗,指出了你又不改
select TOP1 编号,SUM(数量) from 表 GROUP BY 编号 好像是 没有测试过
select top 1 编号,SUM(数量) from 表 GROUP BY 编号 order by sum(数量) desc 呵呵 测试过 这样写 刚才忘了排序
select top 1 编号 from ( select 编号,sum(数量) as 数量 from p1 group by 编号 ) T1 order by 数量 desc
select top 1 sno from ( select sno,sum(quantity) as sumquantity from ( select 50 as quantity,1 as sno union all select 70 as quantity,1 as sno union all select 30 as quantity,2 as sno union all select 80 as quantity,3 as sno union all select 50 as quantity,4 as sno ) as tb group by sno ) as tb1 order by sumquantity desc
www.dullwolf.cn/chess/ 和我下棋吧
select top 1 编号 from tb group by 编号 order by sum(数量) desc
select 编号 from (select top 1 编号 from p1 group by 编号 order by count(*) desc) c
insert into tb select 50,1
insert into tb select 70,1
insert into tb select 30,2
insert into tb select 80,3select top 1 编号 from tb
group by 编号
order by count(编号) desc1
insert into tb select 50,1
insert into tb select 29,1
insert into tb select 30,2
insert into tb select 80,3select top 1 编号 from tb
group by 编号
order by sum(数量) desc3
from test WHERE 数量=(select max(数量) from test)????
insert into @tb select 50,1
insert into @tb select 30,1
insert into @tb select 70,2
insert into @tb select 80,3select 编号
from (select 编号,sum(数量) as 数量 from @tb group by 编号) a
WHERE 数量=(select max(数量) from @tb) /*
编号
-----------
1
3(2 行受影响)
*/
insert into @tb select 50,1
insert into @tb select 30,1
insert into @tb select 70,2
insert into @tb select 80,3select 编号
from @tb
group by 编号
having sum(数量) = (select top 1 sum(数量) as 数量 from @tb group by 编号 order by 数量 desc)
/*
编号
-----------
1
3(2 行受影响)
*/
原来也可以from的后面跟一个子查询?OK,就说这个可以吧,不过这还不符合题意,题里面的一开头就已经固定了,select 编号 from 这样的格式了只能在from后面补上
from
(
select top 1 編號 form
(
select 编号,數量=sum(數量) from p1 group by 编号
)A order by 數量 desc
) T
insert into p1 select 50,1
insert into p1 select 30,1
insert into p1 select 70,2
insert into p1 select 80,3 select 编号
from p1
group by 编号
having sum(数量) = (select top 1 sum(数量) as 数量 from p1 group by 编号 order by 数量 desc)
/*
编号
-----------
1
3 (2 行受影响)
*/
where 編號 IN (
select top 1 編號
form(select 编号,數量=sum(數量) from p1 group by 编号)A
order by 數量 desc)
这里有点怪吧?》數量=sum(數量)是什么意思?
恩,是啊,那如果是这样,那应该怎么写了呢?不用declare 一些变量出来。
select 编号 from p1 where 编号=1 不是更简单?
select 编号
from p1 a group by 编号
having sum(数量)=(select max(num) from (select sum(数量) num from p1 group by 编号)A)
from p1
group by 编号
having sum(数量) = (select top 1 sum(数量) as 数量 from p1 group by 编号 order by 数量 desc)
好像是 没有测试过
呵呵 测试过 这样写 刚才忘了排序
(
select sno,sum(quantity) as sumquantity
from
(
select 50 as quantity,1 as sno
union all
select 70 as quantity,1 as sno
union all
select 30 as quantity,2 as sno
union all
select 80 as quantity,3 as sno
union all
select 50 as quantity,4 as sno
) as tb
group by sno
) as tb1
order by sumquantity desc
和我下棋吧
group by 编号
order by sum(数量) desc