求sql语句,累加
发布网友
发布时间：2022-04-10 10:45
共2个回答
懂视网
时间：2022-04-10 15:06
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,0 ,‘P ‘ ,1 ,0x00000001 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,1 ,‘P ‘ ,1 ,0x00000002 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,2 ,‘P ‘ ,1 ,0x00000004 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,3 ,‘P ‘ ,1 ,0x00000008 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,4 ,‘P ‘ ,1 ,0x00000010 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,5 ,‘P ‘ ,1 ,0x00000020 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,6 ,‘P ‘ ,1 ,0x00000040 ,0)
insert spt_values (name ,number ,type ,low ,high ,status) values (null ,7 ,‘P ‘ ,1 ,0x00000080 ,0)
go
-- ‘P ‘ continued....
declare
@number_track integer
,@char_number_track varchar(12)
select @number_track = 7
select @char_number_track = convert(varchar,@number_track)
-- max columns is 1024 so we need 1024 bit position rows;
-- we‘ll actually insert entries for more than that
while @number_track < 1024
begin
raiserror(‘type=‘‘P ‘‘ ,@number_track=%d‘ ,0,1 ,@number_track)
EXECUTE(
‘
insert spt_values (name ,number ,type ,low ,high ,status)
select
null
,(select max(c_val.number)
from spt_values c_val
where c_val.type = ‘‘P ‘‘
and c_val.number between 0 and ‘ + @char_number_track + ‘
)
+ a_val.number + 1
,‘‘P ‘‘
,(select max(b_val.low)
from spt_values b_val
where b_val.type = ‘‘P ‘‘
and b_val.number between 0 and ‘ + @char_number_track + ‘
)
+ 1 + (a_val.number / 8)
,a_val.high
,0
from
spt_values a_val
where
a_val.type = ‘‘P ‘‘
and a_val.number between 0 and ‘ + @char_number_track + ‘
‘)
select @number_track = ((@number_track + 1) * 2) - 1
select @char_number_track = convert(varchar,@number_track)
end --loop
go
|
master 是数据库名
spt_values是表名 spt_values 是一张常量表 系统表 该表是从sybase继承过来的，是个内部字典表，供SQL Server内部使用。
我们可以在许多系统存储过程和函数的源代码中发现它的身影。其实可以将它理解成我们编程时常用的数据字典.
列名分别为名称、值、类型、下限、上限、状态；
类型列的取值含义：
D=Database Option P=Projection DBR=Database Role DC=Database Replication I=Index L=Locks V=Device Type
因为比较多，无法一一列举。其中类型P较为特殊，它只是0-2047(与版本有关)之间的数字的简单列表，作为对所有类型之间关系的预测。
select number from master..spt_values with(nolock) where type=‘P‘ /**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/
select MONTH(convert(varchar(10), dateadd(MONTH, number - 1,
DATEADD(yy,DATEDIFF(yy,0,getdate()),0)), 120)) as yue from master.dbo.spt_values WHERE
type=‘P‘ AND number <= datediff(MONTH, DATEADD(yy,DATEDIFF(yy,0,getdate()),0),
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) ) + 1 AND number>0
select * from master.dbo.spt_values where type=‘p‘
select number from master..spt_values with(nolock) where type=‘P‘
学习使用master.dbo.spt_values表
标签：ack server for erro sql with 它的 文件 pac
热心网友
时间：2022-04-10 12:14
SQL Server 2008 测试通过：
;WITH goods AS (SELECT 1 AS id, 1 AS price
UNION ALL
SELECT 2 AS id, 4 AS price
UNION ALL
SELECT 3 AS id, 6 AS price
UNION ALL
SELECT 4 AS id, 8 AS price
), tmpTable AS (
SELECT a.*, ROW_NUMBER() OVER(ORDER BY a.id, b.number) AS px
FROM goods a, master.dbo.spt_values b /* 用这表取到的数值只能 到2047，所以如果要判断的数大于这个，就要用其他函数等来替代 */
WHERE b.number BETWEEN 1 AND a.price AND b.type ='P'
)
SELECT * FROM goods a WHERE EXISTS(SELECT TOP 1 1 FROM tmpTable aa GROUP BY id HAVING aa.id =a.id AND MAX(aa.px) <=15)
