# sql 语句

## 1. 乐观锁

说明：「此处以insert into 为例」

- A、sql语句样式： insert into 表名(字段名) select 参数形式的值 where 乐观锁条件， 返回值影响记录入
- B、根据返回值判断： == 0 插入不成功，条件不匹配； > 0 插入成功。 
- C、如果需要返回插入的 ID 可以在 slq 语句最后增加： select @@identity ； 插入成功返回ID值，不成功返回 null 。

```csharp
int c = db.Ado.ExecuteCommand(@"insert into QuestionPageAnswer(EmpID, PageID, AnswerDT) 
            select @EmpID, @PageID, @AnswerDT 
            where not exists(select * from QuestionPageAnswer where EmpID = @EmpID and PageID = @PageID)", new { EmpID = Convert.ToInt32(this.User.ID), PageID = pageId, AnswerDT = DateTime.Now });
```

## 2.创建表

```sql
--DictExam_Abnormal
	create table DictExam_Abnormal(
		DID int identity(1,1) not null,
		ParentID int not null default 0,
		DName nvarchar(200) ,
	CONSTRAINT PK_DictExam_Abnormalr_ID PRIMARY KEY CLUSTERED (DID)
)
```

## 3.sqlserver

- 日期计算

```sql
-- 计算两个日期之间相差的天数,后面的日期，减去前面的日期
SELECT DATEDIFF(day, '2023-01-01', '2023-01-31') AS DaysDifference;
-- 当前日期加30天
SELECT *
FROM YourTable
WHERE YourDateColumn > GETDATE() 
  AND YourDateColumn <= DATEADD(day, 30, GETDATE())
```

- 表增加字段

```sql
 alter table M_Prescription add SumPrice decimal(18,4) 
```

- 翻页

```sql
select * from (
select id ,
ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS RowIndex  
from M_InMedicine
) t
where RowIndex between 1 and 20 -- RowIndex 从1开始
```