[SQL ] 请问rank的用法

楼主: oherman (qq)   2015-10-12 15:04:21
数据库名称:sql server
数据库版本:2014
内容/问题描述:
请问我有一个资料表名称是table_schema
字段及资料如下:
table_name field_name
==================================
customer id
customer name
employee ide
employee name
我想用rank或row_number产生如下资料:
sequnence table_name field_name
=====================================
01 customer id
02 customer name
01 employee id
02 employee name
squnence的产生由rank语法产生,请问sql语法应该怎么下?
重点就是不同的table_name,sequence必须重新计数,
试过over by (table_name,field_name)
结果都不是我想要的,用group by更不行,请问各位先进有没有合适的语法?
作者: ishewood (木头)   2015-10-12 15:48:00
SELECT table_name, field_name,ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY field_name) AS sequenceFROM table_schemaORDER BY table_name, field_name;看是不是你要的
楼主: oherman (qq)   2015-10-12 16:21:00
感谢,正是我要的^^

Links booklink

Contact Us: admin [ a t ] ucptt.com