我有三张表,用户表:
create table users(
userId int primary key identity(1,1) ,
username varchar(20) unique notnull,
password varchar(20) not null,
email varchar(30),
powers varchar(20) not null,
sex varchar(20) ,
address varchar(40) not null,
integration varchar(40) not null,
account Numeric(10,2),
present varchar(20)
)礼物表
create table present(
pid int primary key identity(1,1),
pname varchar(20) unique not null,
pjf Numeric(10,2),
pphoto varchar(20)
)
兑换表
create table redeem(
rid int primary key identity(1,1),
userid int,
pid int,
rdate Datetime default(getdate()),
rcl varchar(20),
foreign key (userid) references Users(userid),
foreign key (pid) references present(pid)
)现在我能知道username和pid.我想向redeem表中插入一条数据,就插入userid和pid.Sql语句怎么写。求大神指导SQL Server插入数据子查询
create table users(
userId int primary key identity(1,1) ,
username varchar(20) unique notnull,
password varchar(20) not null,
email varchar(30),
powers varchar(20) not null,
sex varchar(20) ,
address varchar(40) not null,
integration varchar(40) not null,
account Numeric(10,2),
present varchar(20)
)礼物表
create table present(
pid int primary key identity(1,1),
pname varchar(20) unique not null,
pjf Numeric(10,2),
pphoto varchar(20)
)
兑换表
create table redeem(
rid int primary key identity(1,1),
userid int,
pid int,
rdate Datetime default(getdate()),
rcl varchar(20),
foreign key (userid) references Users(userid),
foreign key (pid) references present(pid)
)现在我能知道username和pid.我想向redeem表中插入一条数据,就插入userid和pid.Sql语句怎么写。求大神指导SQL Server插入数据子查询
declare @uid int
declare @pid int
insert into users ....后面省略插入的内容
set @uid=@@identityinsert into present....后面省略插入的内容
set @pid =@@identity然后insert 到你的表中,对应的uid和pid就是上面两个变量
受教了,谢谢指导。我写的如下代码也能实现我想要的insert into redeem(pid,userid)
select 2, userid from users where username='张天阳'
受教了,谢谢指导。我写的如下代码也能实现我想要的insert into redeem(pid,userid)
select 2, userid from users where username='张天阳'你不好写死那个pid就是等于2吧?不过具体看你的业务需要