SQL 查询问题

Question

不罗嗦直接看数据： A 1 1 2 4 在A的后面再添加一列Num排名,根据A的值来确定Num A         Num 1            1 1            1 2            2 4            3

Answer 1

添加一列num排序，根据'Month_Rating' 来填充num,

Answer 2

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

Answer 3

 select *  from (select row_number() over  (order by A) as Num,*  from tableName) as tt

Answer 4

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

Answer 5

呵呵，想法不错。。。

Answer 6

算了我已经在后台给处理了
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;
}
}

Answer 7

明白你的意思了，就是同行中A列值的排序数，是么？
没深入研究过Sql，找高手吧。

Answer 8

不是一样的，更row_number() over(order by...) 有的一样，但是不完全一样，也就是说num 是根据A列的值来排序的

Answer 9

你的意思是这个num跟列A的值完全一样？

Answer 10

用一个简单的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
*/

Answer 11

这是我查询的语句，有点雷人
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

Answer 12

如果有163行，那会不会要写163个 when呢，况且这列也不能确定，即不是提前预知的

Answer 13

select case A when A的值 then  NUM

                  when A的值 then  NUM

                  end

from 表名    

Answer 14

这样更不行

Answer 15

distinct    order by

Answer 16

不是，数据中没有这一列，也不是公式

Answer 17

num的公式是？？