① 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
要想區別並列現象還要復雜一些，可能簡單的語句解決不了