① sql里树形结构分组排序
createtableT1(thisvarchar(10),parentvarchar(10))
insertintoT1(this,parent)
values('id1',null)
,('id2',null)
,('id3','id1')
,('id4','id2')
,('id5','id3')
,('id6','id3')
,('id7','id4')
,('id8','id7')
--sqlserver的cte功能
withtree(this,parent,root,depth)as(
selectthis,parent,thisasroot,
unionall
selecta.this,a.parent,b.root,b.depth+1asdepthfromT1a,treebwherea.parent=b.this
)
selectthis,parent,root,depth
fromtree
orderbyroot,depth,this
② SQL销售日报表分组排序问题，请高手指导,谢谢。。。
你要怎么分组？ 能把需求说的明白点吗？
给你写一个类似的例子 你自己还得动动脑筋 需要的知识我帮你解决了
Select CASE WHEN
GROUPING(FItemNumber)=1
THEN 'Total:' ELSE
FItemNumber END as FItemNumber
,CASE WHEN GROUPING(Fdate)=1 THEN 'State Total:' ELSE Fdate END as Fdate
,Sum(FSaleQty) AS FSaleQty ,Sum(FAmount) AS FAmount ,Sum(FGain) AS FGain
From T_ItemSale
GROUP BY
ROLLUP(Fdate,FItemNumber)
③ SQL如何对分组后的结果进行排序并且取前几名
SQL取分组中的前几名
[sql] www.2cto.com
create table #aa(class varchar(10),name varchar(10),lang int,math int)
go
insert into #aa
select '1','a1',60,70
union all
select '1','a2',80,70
union all
select '1','a3',90,70
union all
select '1','a4',50,70
go
insert into #aa
select '2','b1',60,70
union all
select '2','b2',90,90
union all
select '2','b3',90,70
union all
select '2','b4',50,70
go
select * from #aa
--取每年级前一名的成级
select * from
(select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where rr<2
--取每年级前二名的成级
select * from
(select ROW_NUMBER() over(partition by class order by lang+math desc) rr, * from #aa ) a
where rr<3
④ sql 同一个字段先分组再排序
您好，您这样：
Create Table zxt_tes1
(
Id Number,
Name Varchar2(10),
Type Char(1)
)Nologging
Insert Into zxt_tes1 Values(001, 'ssss', 'A');
Insert Into zxt_tes1 Values(002, 'dddd', 'A');
Insert Into zxt_tes1 Values(001, 'aaaa', 'B');
Insert Into zxt_tes1 Values(002, 'dddd', 'B');
Insert Into zxt_tes1 Values(001, 'cccc', 'C');
Insert Into zxt_tes1 Values(002, 'eeee', 'C');
commit;
SQL> Select * From zxt_tes1
2 ;
ID NAME TYPE
---------- ---------- ----
1 ssss A
2 dddd A
1 aaaa B
2 dddd B
1 cccc C
2 eeee C
6 rows selected
---
sql如下：
SQL> Select t.*,row_number() over ( Partition By t.Id Order By t.Name) From zxt_tes1 t;
ID NAME TYPE ROW_NUMBER()OVER(PARTITIONBYT.
---------- ---------- ---- ------------------------------
1 aaaa B 1
1 cccc C 2
1 ssss A 3
2 dddd A 1
2 dddd B 2
2 eeee C 3
6 rows selected
⑤ 关于SQL相同表分组排列组合的问题
建两个序列，并通过一个函数调用序列（union 不支持直接使用序列），用于排序。
先用第二组所有行（3行），union all 3遍第一组第一行（left join 第二组，行数就和第二组一样了）
在用第二组所有行（3行），union all 3遍第一组第二行（left join 第二组，行数就和第二组一样了）
最后按照序号，组别，编号排序
/*
dropsequenceBig_Letter1;
CreatesequenceBig_Letter1
Incrementby1
Startwith65
Maxvalue999999
Minvalue1
Nocycle
nocache;
dropsequenceBig_Letter2;
CreatesequenceBig_Letter2
Incrementby1
Startwith65
Maxvalue999999
Minvalue1
Nocycle
nocache;
--获取数列下一个值
createorreplacefunctionget_seq_next(seq_nameinvarchar2)returnnumber
is
seq_valnumber;
begin
executeimmediate'select'||seq_name||'.nextvalfromal'intoseq_val;
returnseq_val;
endget_seq_next;
*/
withtmpas(
select'1'groupid,'1'numfromal
union
select'1','2'fromal
union
select'2','1'fromal
union
select'2','2'fromal
union
select'2','3'fromal
)
selectchr(get_seq_next('Big_Letter1'))xuhao,t1.groupid,t1.num
fromtmpt1wheregroupid='2'
unionall
selectchr(get_seq_next('Big_Letter2'))xuhao,t1.groupid,t1.num
fromtmpt1,tmpt2
wheret1.groupid='1'andt1.num='1'andt2.groupid='2'
union
selectchr(get_seq_next('Big_Letter1'))xuhao,t1.groupid,t1.num
fromtmpt1wheregroupid='2'
unionall
selectchr(get_seq_next('Big_Letter2'))xuhao,t1.groupid,t1.num
fromtmpt1,tmpt2
wheret1.groupid='1'andt1.num='2'andt2.groupid='2'
orderbyxuhao,groupid,num
执行结果如下：
按照这个思路拼动态SQL吧
⑥ SQL 分组统计并排序
with tmp as
(select row_number() over(order by count(val)) as rn,val,count(val) as cnum from table1 group by val)
select * from tmp where rn<3
要想区别并列现象还要复杂一些，可能简单的语句解决不了