sql分組序號
⑴ sql按欄位分組，排序，並且找出每組最大的數據
select [序號],[名稱],max([號碼]) from table
group by [名稱] ,[序號]
⑵ 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 中如何根據只根據第一個欄位分組
使用分析函數row_number() over (partiion by ... order by ...)來進行分組編號，然後取分組標號值為1的記錄即可。目前主流的資料庫都有支持分析函數，很好用。
其中，partition by 是指定按哪些欄位進行分組，這些欄位值相同的記錄將在一起編號；order by則是指定在同一組中進行編號時是按照怎樣的順序。
示例(SQL Server 2005或以上適用)：
123456
select s.* from ( select *, row_number() over (partition by [手機號] order by [店鋪]) as group_idx from table_name) swhere s.group_idx = 1
⑷ sql中，按照某個欄位的內容進行分組，並在組內加序號
sqlserver2005及攜悶以上版洞隱伏本
SELECT ROW_NUMBER() OVER(PARTITION BY 種類 ORDER BY 種類納攜) AS 序號,*
FROM 表
⑸ sql 如何根據總大小分組排序
--建表
CreateTableT
(
AVarchar(10),
BVarchar(10),
CVarchar(10),
Dint
)
--插入數據
insertintoTvalues('a','cd','cd2',1)
insertintoTvalues('a','cd2','cd2',2)
insertintoTvalues('a','cd4','cd2',1)
insertintoTvalues('B','蠢爛cd4','cd5',52)
insert兆嫌intoTvalues('B','cd7','cd5',5)
insertintoTvalues('C','cd7','cd8',105)
insertintoTvalues('C','cd8','cd9',族檔手107)
--查詢(MSSQL2008)
--已A分組求得最大值,再按該值降序排列
SelectA,B,C,DFrom
(
Select*,MAX(D)over(partitionbyA)ASEfromT
)S
OrderbyEDesc,DDesc
⑹ 按分組來自動生成序號的SQL怎麼寫
假設磨肆扒表名雹悉為 test1, 欄位名為 a, b
SQL 語句如下：瞎昌
select t.a, t.b, (SELECT COUNT(*) FROM test1 WHERE a = t.a AND b <= t.b) AS xuhao FROM test1 t ORDER BY t.a
⑺ SQL銷售日報表分組排序問題，請高手指導,謝謝。。。
參考這個：
Select Case when B.fdate is null and b.fnumber is null then '合計：'
圓局稿 when B.fnumber is null and B.fdate is not null then null
else to_char(B.fdate,'yyyy-mm-dd') end as fdate,
case when b.fnumber is null and b.fdate is not null then '小計：' else to_char(b.fnumber) end as fnumber,
b.fqty,
b.famount
From
橘孝(
select trunc(A.Fdate) fdate, A.Fnumber, sum(A.Fqty) fqty, sum(A.Famount) famount From table_c A
臘櫻 Group BY rollup (trunc(A.Fdate), A.Fnumber)
) B
⑻ sql server 2000分組增加序號問題
CREATE TABLE #temp (
Code1 VARCHAR(10),
Code2 VARCHAR(7)
);
go
INSERT INTO #temp
SELECT '330200101', '3302001' UNION ALL
SELECT '330200102', '3302001' UNION ALL
SELECT '330200103', '3302001' UNION ALL
SELECT '330200105', '3302001' UNION ALL
SELECT '330200201', '3302002' UNION ALL
SELECT '330200202', '3302002' UNION ALL
SELECT '330200203', '3302002' UNION ALL
SELECT '330200204', '3302002' UNION ALL
SELECT '330200205', '3302002'
GO
1> SELECT
2> (SELECT RIGHT('000' + CAST( COUNT(1) AS VARCHAR ), 3)
3> FROM #temp sub
4> WHERE
5> sub.Code2 = main.Code2
6>搏碰 AND sub.Code1 <= main.Code1
7> ) AS 序號侍段,
8> Code1 AS 代碼1,
9> Code2 AS 代碼2
10> FROM
11> #temp main;
12> GO
序號 代碼1 代碼2
------ ---------- -------
001 330200101 3302001
002 330200102 3302001
003 330200103 3302001
004 330200105 3302001
001 330200201 3302002
002 330200202 3302002
003 330200203 3302002
004 330200204 3302002
005 330200205 3302002
(9 行受老銀譽影響)
⑼ sql 分組編序號
題主你已經把SQL文寫出來了，既然誰時間早排前面，那你把desc去掉就行了。
SELECT*FROM(SELECTROW_NUMBER()OVER(PARTITIONBYSUBSTRING(CONVERT(varchar(100),Time,20),0,11)ORDERBYTime)ASxh,*FROMdbo.表)T
我用的oracle資料庫，sql文如下：
SELECT*FROM(SELECTROW_NUMBER()OVER(PARTITIONBYto_char(t.time,'yyyy-mm-dd')ORDERBYTime)ASxh,t.*FROMtestt)
⑽ sql 如何分組排序同時進行
1、首先輸入代碼：
SELECT * FROM (select * from CJ where Gender='女') m
where( select COUNT(*) from (select * from CJ where Gender='女') n
where m.Classid = n.Classid and n.English > m.English)<2
order by Classid, English desc
2、然後再輸入代碼：
SELECT * FROM CJ m
where(
select COUNT(*) from CJ n
where m.Classid = n.Classid and n.English > m.English and n.Gender='女')<2 --指的是內表
and Gender='女' --指的是外表
order by Classid, English desc