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("歸還成功");
}