CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jg] [decimal](18, 2) NULL ,
[flag] [int] NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO test(id,name,jg,flag,sl) VALUES('1','a','81.36','1','10')
INSERT INTO test(id,name,jg,flag,sl) VALUES('2','a','61.30','0','22')
INSERT INTO test(id,name,jg,flag,sl) VALUES('3','a','78.69','1','65')
INSERT INTO test(id,name,jg,flag,sl) VALUES('4','b','65.66','0','88')
INSERT INTO test(id,name,jg,flag,sl) VALUES('5','b','55.95','1','63')
INSERT INTO test(id,name,jg,flag,sl) VALUES('6','c','47.53','1','29')select a.name ,sum(a.jg) as 价格,'' as 价格排序
,sum(a.sl) as 数量 ,'' as 数量排序
from test a group by a.name
想要的效果是:名称 价格 价格排序 数量 数量排序
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jg] [decimal](18, 2) NULL ,
[flag] [int] NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO test(id,name,jg,flag,sl) VALUES('1','a','81.36','1','10')
INSERT INTO test(id,name,jg,flag,sl) VALUES('2','a','61.30','0','22')
INSERT INTO test(id,name,jg,flag,sl) VALUES('3','a','78.69','1','65')
INSERT INTO test(id,name,jg,flag,sl) VALUES('4','b','65.66','0','88')
INSERT INTO test(id,name,jg,flag,sl) VALUES('5','b','55.95','1','63')
INSERT INTO test(id,name,jg,flag,sl) VALUES('6','c','47.53','1','29')select a.name ,sum(a.jg) as 价格,'' as 价格排序
,sum(a.sl) as 数量 ,'' as 数量排序
from test a group by a.name
想要的效果是:名称 价格 价格排序 数量 数量排序
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3
,sum(a.sl) as 数量 ,rank() over (order by sl) as 数量排序
from test a group by a.name
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jg] [decimal](18, 2) NULL ,
[flag] [int] NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO test(id,name,jg,flag,sl) VALUES('1','a','81.36','1','10')
INSERT INTO test(id,name,jg,flag,sl) VALUES('2','a','61.30','0','22')
INSERT INTO test(id,name,jg,flag,sl) VALUES('3','a','78.69','1','65')
INSERT INTO test(id,name,jg,flag,sl) VALUES('4','b','65.66','0','88')
INSERT INTO test(id,name,jg,flag,sl) VALUES('5','b','55.95','1','63')
INSERT INTO test(id,name,jg,flag,sl) VALUES('6','c','47.53','1','29')SELECT a.name, SUM(a.jg) AS 价格, ROW_NUMBER()OVER(ORDER BY SUM(a.jg) DESC) AS 价格排序,
SUM(a.sl) AS 数量, ROW_NUMBER()OVER(ORDER BY SUM(a.sl) DESC) AS 数量排序
FROM test a
GROUP BY a.name
ORDER BY 价格 DESC/*
name 价格 价格排序 数量 数量排序
-------------------------------------------------- --------------------------------------- -------------------- ----------- --------------------
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3(3 行受影响)
*/
DROP TABLE test
as
(select a.name ,
sum(a.jg) as 价格,
sum(a.sl) as 数量
from test a
group by a.name)
select name,价格,
(select count(1) from cte where 价格 >= c.价格) as 数量排序 ,
数量,
(select count(1) from cte where 数量 >= c.数量) as 价格排序
from cte c
if object_id('ta') is not null
drop table ta
create table ta (a int,b int)
insert into ta
select 1,3 union all
select 5,8 union all
select 1,3 union all
select 7,9 union all
select 4,13 union all
select 1,3
select * from taselect *,rank() over (order by a) as ax,rank() over (order by b) as bx from ta order by a
,sum(a.sl) as 数量 ,row_number() over(order by sum(a.sl)) as 数量排序
from test a
group by a.name
order by 价格name 价格 价格排序 数量 数量排序
-------------------------------------------------- --------------------------------------- -------------------- ----------- --------------------
c 47.53 1 29 1
b 121.61 2 151 3
a 221.35 3 97 2(3 行受影响)
,sum(a.sl) as 数量 ,row_number() over(order by sum(a.sl) desc) as 数量排序
from test a
group by a.name
order by 价格 descname 价格 价格排序 数量 数量排序
-------------------------------------------------- --------------------------------------- -------------------- ----------- --------------------
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3(3 行受影响)
if object_id('test') is not null
drop table test
CREATE TABLE [test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jg] [decimal](18, 2) NULL ,
[flag] [int] NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
set IDENTITY_INSERT test on
INSERT INTO test(id,name,jg,flag,sl) VALUES('1','a','81.36','1','10')
INSERT INTO test(id,name,jg,flag,sl) VALUES('2','a','61.30','0','22')
INSERT INTO test(id,name,jg,flag,sl) VALUES('3','a','78.69','1','65')
INSERT INTO test(id,name,jg,flag,sl) VALUES('4','b','65.66','0','88')
INSERT INTO test(id,name,jg,flag,sl) VALUES('5','b','55.95','1','63')
INSERT INTO test(id,name,jg,flag,sl) VALUES('6','c','47.53','1','29')
select name as 名称,jg as 价格,rank() over (order by jg) as 价格排序,sl as 数量,rank() over (order by sl) as 数量排序 from test order by name
a 81.36 6 10 1
a 61.30 3 22 2
a 78.69 5 65 5
b 65.66 4 88 6
b 55.95 2 63 4
c 47.53 1 29 3
select a.name ,sum(a.jg) as 价格,
(select count(1) from (select distinct name from test group by name having sum(jg)>=sum(a.jg))t) as 价格排序
,sum(a.sl) as 数量 ,
(select count(1) from (select distinct name from test group by name having sum(sl)>=sum(a.sl))t) as 数量排序
from test a group by a.name
/*name 价格 价格排序 数量 数量排序
-------------------------------------------------- --------------------------------------- ----------- ----------- -----------
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3(3 行受影响)*/
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[jg] [decimal](18, 2) NULL ,
[flag] [int] NULL ,
[sl] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO test(id,name,jg,flag,sl) VALUES('1','a','81.36','1','10')
INSERT INTO test(id,name,jg,flag,sl) VALUES('2','a','61.30','0','22')
INSERT INTO test(id,name,jg,flag,sl) VALUES('3','a','78.69','1','65')
INSERT INTO test(id,name,jg,flag,sl) VALUES('4','b','65.66','0','88')
INSERT INTO test(id,name,jg,flag,sl) VALUES('5','b','55.95','1','63')
INSERT INTO test(id,name,jg,flag,sl) VALUES('6','c','47.53','1','29')
select a.name ,a.价格,
count(distinct case when a.价格<b.价格 then b.价格 end)+1 价格排序
,a.数量 ,
count(distinct case when a.数量<b.数量 then b.数量 end)+1 as 数量排序
from
(select name ,sum(jg) as 价格,sum(sl) as 数量 from test group by Name) a,
(select name,sum(sl) AS 数量,sum(jg) as 价格 from test group by name )b
where a.价格<b.价格 or a.数量<b.数量
group by a.name,a.价格,a.数量
/*
name 价格 价格排序 数量 数量排序
-------------------------------------------------- --------------------------------------- ----------- ----------- -----------
a 221.35 1 97 2
b 121.61 2 151 1
c 47.53 3 29 3
(3 row(s) affected)
*/
就是把表test 换成 (select a.* from test a )
因为我这个有很多关联.必须要加个嵌套
谢谢
select a.name ,sum(a.jg) as 价格,
(select count(1) from (select distinct name from (select a.* from test a ) group by name having sum(jg)>=sum(a.jg))t) as 价格排序
,sum(a.sl) as 数量 ,
(select count(1) from (select distinct name from (select a.* from test a ) group by name having sum(sl)>=sum(a.sl))t) as 数量排序
from ( select a.* from test a ) a group by a.name
, [价格] = sum(jg)
, [价格排序] = row_number() over(order by sum(jg) desc)
, [数量] = sum(sl)
, [数量排序] = row_number() over(order by sum(sl) desc)
from test
group by name
order by [价格排序], [数量排序]
select a.name ,a.价格,
count(distinct case when a.价格<b.价格 then b.价格 end)+1 价格排序
,a.数量 ,
count(distinct case when a.数量<b.数量 then b.数量 end)+1 as 数量排序
from
(select name ,isnull(sum(jg),0) as 价格,isnull(sum(sl),0) as 数量 from (select * from test2) group by Name) a,
(select name,isnull(sum(sl),0) AS 数量,isnull(sum(jg),0) as 价格 from (select * from test2) group by name )b
where a.价格<b.价格 or a.数量<b.数量
group by a.name,a.价格,a.数量
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'group' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 7
在关键字 'group' 附近有语法错误。
SELECT NAME,SUM(jg) AS jgTotal,RANK() OVER(ORDER BY SUM(jg) desc) AS jgRank,SUM(sl),
RANK() OVER(ORDER BY SUM(sl) desc) AS slRank FROM test GROUP BY NAME ORDER BY jgTotal DESC
(select name ,isnull(sum(jg),0) as 价格,isnull(sum(sl),0) as 数量 from (select * from test2) group by Name)
把這一段改為嵌套結果集就行了
(select name,isnull(sum(sl),0) AS 数量,isnull(sum(jg),0) as 价格
from test2 group by name )a
from (select * from test2)t group by name )a