SQL 查询问题
0
悬赏园豆：5
[已解决问题]
解决于 2011-04-19 10:38
不罗嗦直接看数据：
A
1
1
2
4
在A的后面再添加一列Num排名,根据A的值来确定Num
A Num
1 1
1 1
2 2
4 3
最佳答案
0
用一个简单的dense_Rank()函数即可
------分组显示示例
IF NOT OBJECT_ID('[XXX]') IS NULL
DROP TABLE [XXX]
GO
CREATE TABLE [XXX](
[TID] int)
go
INSERT [XXX]
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 4
GO
----select * from XXX
/*
TID
1
1
2
4
*/
select TID,(dense_Rank() over (order by TID asc)) as num
from xxx
/*
TID num
1 1
1 1
2 2
4 3
*/
收获园豆：5
顶，我一般就使用 Row_number 了
确实很不错
其他回答(7)
0
num的公式是？？
不是，数据中没有这一列，也不是公式
0
distinct order by
这样更不行
0
select case A when A的值 then NUM
when A的值 then NUM
end
from 表名
如果有163行，那会不会要写163个 when呢，况且这列也不能确定，即不是提前预知的
这是我查询的语句，有点雷人
select row_number() over(order by Month_Rating desc) as 'Row_Id',*
from (select distinct a.SUP_MANAGE_ID,b.SUP_NAME,(select CAST(SUM(SUP_ASSESS_POINT)/COUNT(0) as numeric(5,2))
from ASSESS_SUP_CLOSE_POINT where SUP_MANAGE_ID=a.SUP_MANAGE_ID and ORGANISE_ID='YFJC00' and YEAR_MONTH_STR between '201001' and '201003') as 'Month_Rating'
from ASSESS_SUP_CLOSE_POINT a inner join SUP_INFO b on a.SUP_MANAGE_ID=b.SUP_MANAGE_ID where ORGANISE_ID='YFJC00' and YEAR_MONTH_STR between '201001' and '201003') a
select row_number() over(order by Month_Rating desc) as 'Row_Id',*
from (select distinct a.SUP_MANAGE_ID,b.SUP_NAME,(select CAST(SUM(SUP_ASSESS_POINT)/COUNT(0) as numeric(5,2))
from ASSESS_SUP_CLOSE_POINT where SUP_MANAGE_ID=a.SUP_MANAGE_ID and ORGANISE_ID='YFJC00' and YEAR_MONTH_STR between '201001' and '201003') as 'Month_Rating'
from ASSESS_SUP_CLOSE_POINT a inner join SUP_INFO b on a.SUP_MANAGE_ID=b.SUP_MANAGE_ID where ORGANISE_ID='YFJC00' and YEAR_MONTH_STR between '201001' and '201003') a
添加一列num排序，根据'Month_Rating' 来填充num,
0
你的意思是这个num跟列A的值完全一样？
不是一样的，更row_number() over(order by...) 有的一样，但是不完全一样，也就是说num 是根据A列的值来排序的
明白你的意思了，就是同行中A列值的排序数，是么？
没深入研究过Sql，找高手吧。
没深入研究过Sql，找高手吧。
算了我已经在后台给处理了
dt.Columns.Add("SUP_ASSESS_ORDER_NO", typeof(int));
int tmpInt = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
dt.Rows[i]["SUP_ASSESS_ORDER_NO"] = tmpInt;//默认第一行给1
}
else
{
if (dt.Rows[i]["SUP_ASSESS_ORDER_NO"].ToString() != dt.Rows[i - 1]["SUP_ASSESS_ORDER_NO"].ToString())//当前一行与上一行的比较，如果不等，则 +1
{
tmpInt++;
}
dt.Rows[i]["SUP_ASSESS_ORDER_NO"] = tmpInt;
}
}
dt.Columns.Add("SUP_ASSESS_ORDER_NO", typeof(int));
int tmpInt = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i == 0)
{
dt.Rows[i]["SUP_ASSESS_ORDER_NO"] = tmpInt;//默认第一行给1
}
else
{
if (dt.Rows[i]["SUP_ASSESS_ORDER_NO"].ToString() != dt.Rows[i - 1]["SUP_ASSESS_ORDER_NO"].ToString())//当前一行与上一行的比较，如果不等，则 +1
{
tmpInt++;
}
dt.Rows[i]["SUP_ASSESS_ORDER_NO"] = tmpInt;
}
}
呵呵，想法不错。。。
0
select a.A,b.Num from Tb a inner join (select row_number() over (order by A) as Num,A from tb group by A) as b on a.A = b.A
0
select * from (select row_number() over (order by A) as Num,* from tableName) as tt
0
select Table1.A, temp_table1.Expr1 from (
SELECT A, row_number() over (ORDER BY A) AS Expr1
FROM (SELECT DISTINCT A
FROM Table1
) AS derivedtbl_1 ) temp_table1 join Table1
on temp_table1.A=Table1.A
