ROW_NUMBER()
declare @tb table (col1 int,col2 int)
insert into @tb
select 2009001,5 union all
select 2009002,4 union all
select 2009003,3 union all
select 2009004,1 union all
select 2009005,5 union all
select 2009006,9 union all
select 2009007,0 union all
select 2009008,5
select col2
from (select id=row_number() over ( order by col1),* from @tb) as t
where id=
(select max(id)-6 from t )
-------------------------------------------
(8 行受影响)
消息 208,级别 16,状态 1,第 12 行
对象名 't' 无效。
declare @tb table (col1 int,col2 int)
insert into @tb
select 2009001,5 union all
select 2009002,4 union all
select 2009003,3 union all
select 2009004,1 union all
select 2009005,5 union all
select 2009006,9 union all
select 2009007,0 union all
select 2009008,5
select col2
from (select id=row_number() over ( order by col1),* from @tb) as t
where id=
(select max(id)-6 from t )
-------------------------------------------
(8 行受影响)
消息 208,级别 16,状态 1,第 12 行
对象名 't' 无效。
解决方案 »
- 关于数据整合
- 一个SQL批处理的问题
- *****************请问如何随机生成160位长度的数字*******************
- 如何将 SQL Server 的验证模式更改为"SQL Server and Windows"(也称作"混合模式")。
- 弱弱的问一个两表关联查询问题
- SQL server 已插入数据如何自动生成主键
- 如何给数据库建立用户名和加密?
- 狠人帮忙!!!!!!!!!!!!!!!!!!!!!!!!!!!!!(急)
- 安装sqlserver出了问题,帮忙解决?
- 如何将 SQL Server 7.0 数据库升级到 SQL Server 2000
- sql 问题
- 一道复杂的SQL查询面试题,关于行列转换的。
(
select id=row_number() over ( order by col1),* from @tb
)
select col2
from cte
where id=
(select max(id)-6 from cte )
col2
from
(select id=row_number() over ( order by col1),* from @tb) as t
where
id=(select max(id)-6 from (select id=row_number() over ( order by col1),* from @tb)t )
看一楼
Id(期号不连续是因为08年到09年过渡时就不连续的,而且每年也不一定就是356期) 3D 组选数据构成情况表:
----------------------------------
期号 开奖号1 开奖号2 开奖号3 开奖号1遗漏值
------------------------------------------------
2008356 5 6 1 x(未知上次出现5的位置) 2009001 3 3 6 x(未知上次出现3的位置)
2009002 7 1 3 x(未知上次出现7的位置)
2009003 5 0 3 2(2009003期与2008356期之间间隔2) 现在就是想求直选遗漏值:就是求当前期的开奖号1 与上次出现被遗漏过几次? 现在是由前三列值,咋求取第四列的值( 开奖号1遗漏值)用SQL语句实现输出!
这个有没有好方法解决?
我的解决方法是C#拼接sql
/// <summary>
/// 组选中的组遗漏总尾值 Max Mid Min三列的遗憾值后相加取余数
/// 遗漏值的是:同列值与上次出现之间的间隔
/// </summary>
public static int getMissEnd_group(int _Id)
{
MY3D my3d = GetMY3DById(_Id);
int _maxnum = my3d.MaxNum;
int _midnum = my3d.MidNum;
int _minnum = my3d.MinNum;
string sql1 = "select Count(Id) from tb3D where Id<"
+ _Id + " and Id>"
+ "(select Max(Id) from tb3D where MaxNum=" + _maxnum + " and Id<" + _Id + ")";
string sql2 = "select Count(Id) from tb3D where Id<"
+ _Id + " and Id>"
+ "(select Max(Id) from tb3D where MidNum=" + _midnum + " and Id<" + _Id + ")";
string sql3 = "select Count(Id) from tb3D where Id<"
+ _Id + " and Id>"
+ "(select Max(Id) from tb3D where MinNum=" + _minnum + " and Id<" + _Id + ")";
try
{
object obj1 = SqlDBHelper.ExecScalar(sql1, null);
object obj2 = SqlDBHelper.ExecScalar(sql2, null);
object obj3 = SqlDBHelper.ExecScalar(sql3, null);
return (Convert.ToInt32(obj1) + Convert.ToInt32(obj2) + Convert.ToInt32(obj3)) % 10;
}
catch { return 99; }
}
咋用sql直接解决掉?