“用 "select max(id1) from xxx "的原理”这个是boss的提示,但还是懂不起
private int chg_ID() { int A = 0; int B = 0; //从文本文件中读取B的值 StreamReader reader = new StreamReader("" + Environment.CurrentDirectory + "..\\..\\..\\log.txt"); for (string line = reader.ReadLine(); line != null; line = reader.ReadLine()) B = Convert.ToInt32(line); reader.Close(); if (B % 2 == 0) { A = B / 2; } else { A = (B + 1) / 2; } //将B+1的值写入文本文件中 StreamWriter srd = File.CreateText("" + Environment.CurrentDirectory + "..\\..\\..\\log.txt"); srd.WriteLine(B + 1); srd.Close(); return A; } 这个是我的实现方法,我是通过想文本里读出数据和写入数据实现的,但boss说必须用SQL语句,存储过程也不行
if object_id('[tb]') is not null drop table [tb] go create table [tb] ( id int identity(2,1), id2 as id/2, [code] varchar(8) )insert tb select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g' union all select 'h' select * from tb/** id id2 code ----------- ----------- -------- 2 1 a 3 1 b 4 2 c 5 2 d 6 3 e 7 3 f 8 4 g 9 4 h(所影响的行数为 8 行) **/ 把这里的id2作为你的id1试试
另设一个字段id3,自动增长,1,2,3,4 设id1默认值为(id3+1)/2
就是这个怎么不行? if object_id('[tb]') is not null drop table [tb] go create table [tb] ( id int identity(2,1), id2 as (id+1)/2, [code] varchar(8) )
declare @id int select @id=id1 from tb if (select count(1) from tb where id1=@id)>1 set @id=@id+1insert tb(id1,f2,f3) values(@id,@f2,@f3)
写漏了一点。。 declare @id int select @id=max(id1) from tb if (select count(1) from tb where id1=@id)>1 set @id=@id+1insert tb(id1,f2,f3) values(@id,@f2,@f3)
可以把上面几个楼的思想整理一下. 用一个instead of触发器,实现就可以了.
USE [ManagementBD] GO /****** オブジェクト: Table [dbo].[a_charge] スクリプト日付: 03/09/2009 15:40:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[a_charge]( [chg_id] [int] NOT NULL, [chg_id2] [int] NOT NULL, [own_id] [varchar](50) NOT NULL, [inout_flg] [int] NULL, [other_id] [varchar](50) NULL, [chg_memo] [varchar](50) NULL, [sort_id] [int] NULL, [object_id] [int] NULL, [this_chg] [int] NULL, [chg_total] [int] NULL, [executed_date] [datetime] NULL, [bid_id] [int] NULL, CONSTRAINT [PK_a_charge] PRIMARY KEY CLUSTERED ( [chg_id] ASC, [chg_id2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
{
int A = 0;
int B = 0; //从文本文件中读取B的值
StreamReader reader = new StreamReader("" + Environment.CurrentDirectory + "..\\..\\..\\log.txt");
for (string line = reader.ReadLine(); line != null; line = reader.ReadLine())
B = Convert.ToInt32(line);
reader.Close();
if (B % 2 == 0)
{
A = B / 2;
}
else
{
A = (B + 1) / 2;
}
//将B+1的值写入文本文件中
StreamWriter srd = File.CreateText("" + Environment.CurrentDirectory + "..\\..\\..\\log.txt");
srd.WriteLine(B + 1);
srd.Close();
return A;
}
这个是我的实现方法,我是通过想文本里读出数据和写入数据实现的,但boss说必须用SQL语句,存储过程也不行
go
create table [tb]
(
id int identity(2,1),
id2 as id/2,
[code] varchar(8)
)insert tb
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g' union all
select 'h'
select * from tb/**
id id2 code
----------- ----------- --------
2 1 a
3 1 b
4 2 c
5 2 d
6 3 e
7 3 f
8 4 g
9 4 h(所影响的行数为 8 行)
**/
把这里的id2作为你的id1试试
设id1默认值为(id3+1)/2
就是这个怎么不行?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]
(
id int identity(2,1),
id2 as (id+1)/2,
[code] varchar(8)
)
select @id=id1 from tb
if (select count(1) from tb where id1=@id)>1
set @id=@id+1insert tb(id1,f2,f3) values(@id,@f2,@f3)
declare @id int
select @id=max(id1) from tb
if (select count(1) from tb where id1=@id)>1
set @id=@id+1insert tb(id1,f2,f3) values(@id,@f2,@f3)
用一个instead of触发器,实现就可以了.
GO
/****** オブジェクト: Table [dbo].[a_charge] スクリプト日付: 03/09/2009 15:40:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[a_charge](
[chg_id] [int] NOT NULL,
[chg_id2] [int] NOT NULL,
[own_id] [varchar](50) NOT NULL,
[inout_flg] [int] NULL,
[other_id] [varchar](50) NULL,
[chg_memo] [varchar](50) NULL,
[sort_id] [int] NULL,
[object_id] [int] NULL,
[this_chg] [int] NULL,
[chg_total] [int] NULL,
[executed_date] [datetime] NULL,
[bid_id] [int] NULL,
CONSTRAINT [PK_a_charge] PRIMARY KEY CLUSTERED
(
[chg_id] ASC,
[chg_id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]