我如何使用select,max和where where where with insert query in sql server [英] How i used select , max and where where with insert query in sql server
问题描述
我有这样的表: - P_id,R_Number,Date_Reservation,Exame_id,Doctor_id
P_id是FK -------- -------- -------- -------- ---------
50,1,20-2-2016,1,2
20,2,20-2-2016,1,1
60,3,20-2-2016,1,3
50,1, 21-2-2016,2,4
30,2,21-2-2016,2,2
这是我的插入查询添加新的P_id: -
i have table Like that :- P_id, R_Number, Date_Reservation ,Exame_id , Doctor_id
P_id is FK ---- -------- ---------------- -------- ---------
50 , 1 , 20-2-2016 , 1 , 2
20 , 2 , 20-2-2016 , 1 , 1
60 , 3 , 20-2-2016 , 1 , 3
50 , 1 , 21-2-2016 , 2 , 4
30 , 2 , 21-2-2016 , 2 , 2
this is my insert query to add new P_id :-
insert into ResrvationData (P_id ,R_Number ,Date_Reservation ,Exame_id , Doctor_id)
values (1 ,(select MAX(R_Number) from ResrvationData where Date_Reservation = '2016-02-22' and Exame_id = 1 ) +1 ,'2016-02-22' ,1 ,1 )
这个新的P_id变为: - 1,Null,22-2-2016,2,3
i想要使用Date_Reservation和exam_id中的2个值进行R_number计数
我尝试了什么:
当我添加一个新的时候我如何解决这个问题P_id
this new P_id become :- 1 , Null , 22-2-2016 , 2 , 3
i want make R_number count using 2 values in Date_Reservation and exam_id
What I have tried:
How i solve this problem when i add a new P_id
推荐答案
在此查询中使用IsNull:
插入ResrvationData(P_id,R_Number,Date_Reservation, Exame_id,Doctor_id)
值(1,(选择IsNull(MAX(R_N)来自ResrvationData的0)),其中Date_Reservation ='2016-02-22'和Exame_id = 1)+ 1,'2016-02-22',1,1)
Ashish Nigam
Use IsNull on this Query:
insert into ResrvationData (P_id ,R_Number ,Date_Reservation ,Exame_id , Doctor_id)
values (1 ,(select IsNull(MAX(R_Number),0) from ResrvationData where Date_Reservation = '2016-02-22' and Exame_id = 1 ) +1 ,'2016-02-22' ,1 ,1 )
Ashish Nigam
这篇关于我如何使用select,max和where where where with insert query in sql server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!