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