SQL server中可以用sequence来实现订单号的自动生成。
例如创建如下序列：
create sequence orderSeq
as bigint --数据类型
start with 100000 --开始值
increment by 1 --增量
minvalue 1 --最小值
maxvalue 1000000--最大值
no cycle --不循环
cache 3 --设置cache大小为3
这样订单号就会从100000开始每次自增1生成。
2. sql start with connect by prior SQL server 哪个版本支持 我用的是sql2008 看着貌似不支持start关键字
用 with .. as实现递归。
withsubqry(id,name,pid) as(
select id,name,pid from test1 where id= 5 --start with
union all
select test1.id,test1.name,test1.pid from test1,subqry
where test1.pid = subqry.id --connect by
)
select* from subqry;
3. sql中类似循环方法语句怎么写急！急
create table a_lyh_test
as
select 'A' as "字段1" , 500 as "字段2" from al
union all
select 'B' as "字段1" , 300 as "字段2" from al
union all
select 'B' as "字段1" ,400 as "字段2" from al
union all
select 'B' as "字段1" , 600 as "字段2" from al
union all
select 'A' as "字段1" , 200 as "字段2" from al
;
select f.字段1
,ltrim(max(sys_connect_by_path(f.字段2,','))
keep (dense_rank last order by f.pnum),',') as 字段2
from
(
select t.字段1
,t.字段2
,row_number() over(partition by t.字段1 order by t.字段1) as pnum
,row_number() over(partition by t.字段1 order by t.字段1)-1 as lnum
from
(
select a.字段1,a.字段2
from a_lyh_test a
) t
) f
group by f.字段1
connect by f.lnum = prior f.pnum and f.字段1 = prior f.字段1
start with f.pnum = 1;
4. oracle中start with connect by prior在sqlserver中怎么写
用
with
..
as
实现递归。
供参考：
Oracle自己提供的是
connect
by
...
start
with，而别的数据库只有使用With来实现
create
tabletest1(id
number,
name
varchar2(20),
pid
number);
insert
intotest1
values(1,'电器',null);
insert
intotest1
values(2,'家电',1);
insert
intotest1
values(3,'冰箱',2);
insert
intotest1
values(4,'洗衣机',2);
insert
intotest1
values(5,'电脑',1);
insert
intotest1
values(6,'笔记本',5);
insert
intotest1
values(7,'平板',5);
insert
intotest1
values(8,'组装机',7);
insert
intotest1
values(9,'品牌机',7);
withsubqry(id,name,pid)
as(
select
id,name,pid
fromtest1
where
id=
5
union
all
selecttest1.id,test1.name,test1.pid
fromtest1,subqry
wheretest1.pid
=
subqry.id
)
select*
fromsubqry;
drop
tabletest1;
5. sql server 2008 中有没有类似于oracel中start with的语法
你是要递归还是要构建序号的虚拟列？
递归的话没有，只能用with cte的那种方式，不过在排序上远远达不到oracle那么完美
你要是构建序号的虚拟列，只能借助系统表
select number from master..spt_values where type='p'
6. sql 语句 startwith 啥意思啊
从 zp 表 perwonID=45 开始 , 这个start with connect by是oracle中 connect by prior 递归算法 你写的___是这个树结构的递归条件,举个例子
select * from test
start with personID=1
connect by prior personID=personID+1 and prior age=age
;
上面的语句查找出了从1开始，并且personID逐渐+1 递增的，并且 age 相同的哪些个数据.
7. MS SQL 如何实现Oracle的 Start with...Connect by 这种语句
通用表达式CTE
8. start with ... connect by语句，如何用标准SQL实现
大致如下，用withas实现递归查询
（如果数据库不支持withas，只能编程用循环实现）：
withsubqry(p_id,c_id)
as
(
selectc_idp_id,c_id
fromtemp
wherec_id=1
unionall
selecttemp.p_id,temp.c_id
fromtemp,subqry
wheresubqry.c_id=temp.p_id
)
selectc_idfromsubqry;
9. sql server 序列startwith100,max120,循环之后不是从100开始而是从0开始能不能让他从100开始
SqlCommand comm=new SqlCommand(sqlstr,conn);
SqlCommand comm2 = new SqlCommand(sqlstr2, conn);
conn.Open();
SqlDataReader r = comm.ExecuteReader();
if (r.Read() == true)
{
MessageBox.Show("归还成功");
}