select sum(sales),city from stable group by city 这个sql语句可以查询出按城市划分,每个城市中的销售额,但是我不知道该怎么单独取出总销售额最高的城市的名字。
select city,max(sum(sales)) from stable group by city
create table stable(id int,city nvarchar(10),sales int) insert into stable select 1,'bj',100 insert into stable select 2,'cd',100 insert into stable select 3,'bj',100 insert into stable select 4,'cd',500 insert into stable select 5,'bj',100 go ;with cte as ( select city as c,sum(sales) as s from stable group by city )select * from cte where s=(select max(s) from cte) go drop table stable /* c s ---------- ----------- cd 600(1 行受影响)*/
select top 1 * from(select city,sum(sales) as summoney from stable group by city) a order by a.summoney
create table stable(id int,city nvarchar(10),sales int) insert into stable select 1,'bj',100 insert into stable select 2,'cd',100 insert into stable select 3,'bj',100 insert into stable select 4,'cd',500 insert into stable select 5,'bj',100 go--如果最高的只有一个。 select top 1 city , sum(sales) sales from stable group by city order by sales desc /* city sales ---------- ----------- cd 600(所影响的行数为 1 行) */--如果最高的有多个 select city , sum(sales) sales from stable group by city having sum(sales) = (select top 1 sum(sales) sales from stable group by city order by sales desc) /* city sales ---------- ----------- cd 600(所影响的行数为 1 行) */drop table stable
create table stable(id int,city nvarchar(10),sales int) insert into stable select 1,'bj',100 insert into stable select 2,'cd',100 insert into stable select 3,'bj',100 insert into stable select 4,'cd',500 insert into stable select 5,'bj',100 go--如果最高的只有一个。 select top 1 city , sum(sales) sales from stable group by city order by sales desc /* city sales ---------- ----------- cd 600(所影响的行数为 1 行) */--如果最高的有多个 select city , sum(sales) sales from stable group by city having sum(sales) = (select top 1 sum(sales) sales from stable group by city order by sales desc) /* city sales ---------- ----------- cd 600(所影响的行数为 1 行) */drop table stable
关键字: sum(sales) as s 加 group by 语句,加 order by s desc (降序) 应该就能满足你的需要了
select top 1 city from stable group by city order by sum(sales) desc 不是挺简单的嘛..
create table stable(id int,city nvarchar(10),sales int) insert into stable values( 1,'bj',100) insert into stable values (2,'cd',100) insert into stable values (3,'bj',100) insert into stable values (4,'cd',500) insert into stable values (5,'bj',100) insert into stable values (5,'sh',600) goselect city ,sum(sales) sumsales from stable where city in ( select city from ( select city ,sum(sales) as sumsales from stable Group by city ) a inner join ( select top 1 sum(sales) as sumsales from stable Group by city order by sum(sales) desc ) b on a.sumsales=b.sumsales ) group by city
这样就不会遗漏了 'cd' 'sh' 2个城市的销售总额都是600 就不会只查出一个了
if object_id('dbo.stable') is not null drop table stable create table stable(sid int,city varchar(20),sales int) insert stable select 1,'bj',100 union all select 2,'cd',100 union all select 3,'bj',100 union all select 4,'cd',500 union all select 5,'bj',100 create function dbo.fn_maxsales(@city varchar(20)) returns int as begin declare @i int set @i=0; select @i=@i+sales from stable where city=''+@city+'' return @i end --查询销售额最高的城市 select top 1 city,sales from (select distinct(city),sid,sales=dbo.fn_maxsales(city) from stable group by city,sid,sales) as stb order by sales descdrop table stable drop function fn_maxsales
select top 1 city,sum(sales) as salessum from stable group by city order by sum(sales) desc
CREATE TABLE stable(id INT, city CHAR(2),sales INT) INSERT INTO stable SELECT 1 ,'bj', 100 UNION ALL SELECT 2 ,'cd', 100 UNION ALL SELECT 3 ,'bj', 100 UNION ALL SELECT 4 ,'cd', 500 UNION ALL SELECT 5 ,'bj', 100 go --1 SELECT * FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) bb WHERE bb.sales IN (SELECT MAX(sales) sale FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) aa) --2 SELECT TOP 1* FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) aa ORDER BY sales DESC --3 ;WITH cte AS ( SELECT city,SUM(sales) sales FROM stable GROUP BY city ) SELECT TOP 1* FROM cte ORDER BY sales DESCDROP TABLE stable
这个sql语句可以查询出按城市划分,每个城市中的销售额,但是我不知道该怎么单独取出总销售额最高的城市的名字。
insert into stable select 1,'bj',100
insert into stable select 2,'cd',100
insert into stable select 3,'bj',100
insert into stable select 4,'cd',500
insert into stable select 5,'bj',100
go
;with cte as (
select city as c,sum(sales) as s from stable group by city
)select * from cte where s=(select max(s) from cte)
go
drop table stable
/*
c s
---------- -----------
cd 600(1 行受影响)*/
insert into stable select 1,'bj',100
insert into stable select 2,'cd',100
insert into stable select 3,'bj',100
insert into stable select 4,'cd',500
insert into stable select 5,'bj',100
go--如果最高的只有一个。
select top 1 city , sum(sales) sales from stable group by city order by sales desc
/*
city sales
---------- -----------
cd 600(所影响的行数为 1 行)
*/--如果最高的有多个
select city , sum(sales) sales from stable group by city having sum(sales) =
(select top 1 sum(sales) sales from stable group by city order by sales desc)
/*
city sales
---------- -----------
cd 600(所影响的行数为 1 行)
*/drop table stable
insert into stable select 1,'bj',100
insert into stable select 2,'cd',100
insert into stable select 3,'bj',100
insert into stable select 4,'cd',500
insert into stable select 5,'bj',100
go--如果最高的只有一个。
select top 1 city , sum(sales) sales from stable group by city order by sales desc
/*
city sales
---------- -----------
cd 600(所影响的行数为 1 行)
*/--如果最高的有多个
select city , sum(sales) sales from stable group by city having sum(sales) =
(select top 1 sum(sales) sales from stable group by city order by sales desc)
/*
city sales
---------- -----------
cd 600(所影响的行数为 1 行)
*/drop table stable
sum(sales) as s 加 group by 语句,加 order by s desc (降序)
应该就能满足你的需要了
不是挺简单的嘛..
2 可以用select语句本身做一个结果集合然后再从这个集合中搜索,如5楼所说。
3 可以通过语句关联查询,如7楼楼住的基础不是很好,把几种方法都研究明白把。。
create table stable(id int,city nvarchar(10),sales int)
insert into stable values( 1,'bj',100)
insert into stable values (2,'cd',100)
insert into stable values (3,'bj',100)
insert into stable values (4,'cd',500)
insert into stable values (5,'bj',100)
insert into stable values (5,'sh',600)
goselect city ,sum(sales) sumsales from stable where city in
( select city from
(
select city ,sum(sales) as sumsales from stable
Group by city
) a
inner join
(
select top 1 sum(sales) as sumsales from stable
Group by city order by sum(sales) desc
) b
on a.sumsales=b.sumsales
)
group by city
create table stable(sid int,city varchar(20),sales int)
insert stable
select 1,'bj',100 union all
select 2,'cd',100 union all
select 3,'bj',100 union all
select 4,'cd',500 union all
select 5,'bj',100
create function dbo.fn_maxsales(@city varchar(20))
returns int
as
begin
declare @i int
set @i=0;
select @i=@i+sales from stable where city=''+@city+''
return @i
end
--查询销售额最高的城市
select top 1 city,sales from
(select distinct(city),sid,sales=dbo.fn_maxsales(city) from stable group by city,sid,sales) as stb
order by sales descdrop table stable
drop function fn_maxsales
CREATE TABLE stable(id INT, city CHAR(2),sales INT)
INSERT INTO stable
SELECT 1 ,'bj', 100 UNION ALL
SELECT 2 ,'cd', 100 UNION ALL
SELECT 3 ,'bj', 100 UNION ALL
SELECT 4 ,'cd', 500 UNION ALL
SELECT 5 ,'bj', 100
go
--1
SELECT * FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) bb
WHERE bb.sales IN
(SELECT MAX(sales) sale FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) aa)
--2
SELECT TOP 1* FROM (SELECT city,SUM(sales) sales FROM stable GROUP BY city) aa
ORDER BY sales DESC
--3
;WITH cte AS
(
SELECT city,SUM(sales) sales FROM stable GROUP BY city
)
SELECT TOP 1* FROM cte ORDER BY sales DESCDROP TABLE stable