SQL ΪÖ÷±íµÄıһÌõ¼Ç¼£¬ÔÚÖмä±íÖÐͬʱ²åÈë¶àÌõÊý¾Ý
ÓÐʱÎÒÃÇ»áÏñÏÂÃæµÄÇé¿öÒ»Ñù£¬ÎªÖ÷±íµÄıһÌõ¼Ç¼£¬ÔÚÖмä±í(T_Stud_Course ±í)ÖÐͬʱ²åÈë¶àÌõÊý¾Ý
T_Student ±í
Stud_ID
Name
1
Tom
2
Jack
T_Course ±í
Course_ID
Course
1
Chinese
2
English
T_Stud_Course ±í
ID
Stud_ID
Course_ID
1
1
1
2
1
2
3
2
2
ÏÖÔÚÎÒÃÇ¿ÉÒÔÏÂÃæµÄ´æ´¢¹ý³ÌÀ´ÊµÏÖ£º
--ΪÖ÷±íµÄıһÌõ¼Ç¼£¬ÔÚÖмä±íÖÐͬʱ²åÈë¶àÌõÊý¾Ý
ALTER proc [dbo].[wholeInsert]
(
@insert_val nvarchar(1000), --ÐÂÔöµÄµÚÒ»¸ö×ֶεÄÖµ£¨¶à¸öÓÑ,’·Ö¸ô£©,ÈçA,B,
@PK_id nvarchar(50), --ÐÂÔöÊý¾ÝµÄµÚ¶þ¸ö×Ö¶ÎÖµ,ͬʱҲÊÇɾ³ý¾ÉÊý¾ÝµÄÌõ¼þ(²»Ö§³Ö¶à¸ö)
@col_1 nvarchar(20), --µÚÒ»¸ö×Ö¶ÎÃû
@col_2 nvarchar(20), --µÚ¶þ¸ö×Ö¶ÎÃû
@Tab nvarchar(20), --±íÃû
)
as
begin
declare @sql varchar(1000)
declare @strVal nvarchar(100)
set @sql='delete from '+@Tab+' where '+@col_2+'='+@PK_id
exec(@sql)
while(len(@insert_val)>0)
begin
set @strVal=substring(@insert_val,1,charindex(',',@insert_val,1)-1)
set @insert_val=substring(@insert_val,charindex(',',@insert_val,1)+1,len(@insert_val)-charindex(',',@insert_val,1))
set @sql='insert into '+@Tab+' ('+@col_1+','+@col_2+') values ('+@strVal+','+@PK_id+')'
exec(@sql)
end
end
Ö´ÐÐ:
Exec wholeInsert '1,2,','1','Course_ID','Stud_ID','T_Stud_Course'
--±íʾͬʱΪStud_ID=1µÄѧÉúÔö¼ÓÁ½ÃſγÌ(Course_IDΪ1ºÍ2)
Ïà¹ØÎĵµ£º
¶à±íÁª½Ó²éѯ
Ò»¡¢¶à±íÁª½Ó²éѯµÄ·ÖÀà
¶à±íÁª½Ó²éѯʵ¼ÊÉÏÊÇͨ¹ý¸÷¸ö±íÖ®¼ä¹²Í¬ÁеĹØÁªÐÔÀ´²éѯÊý¾ÝµÄ£¬ËüÊǹØÏµÊý¾Ý¿â²éѯ×îÖ÷ÒªµÄÌØÕ÷¡£
Áª½Ó²éѯ¿É·ÖΪÈý´óÀ࣬·ÖÁíΪ£º
1£® ÄÚÁª½Ó¡£
2£® ÍâÁª½Ó¡£
3£® ½»²æÁª½Ó¡£
ÄÇôÎÒÃÇÒ»ÆðÀ´¿´Ò»ÏÂÈçºÎʹÓö ......
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
G ......
ÕÒ³öTABLE1ÖÐ
Column1 Âú×ãvalue1
»òÕß
Column2 Âú×ãvalue2
»òÕß
Column3 Âú×ãvalue3
µÄ¼Í¼
SELECT top 10 * from TABLE1
WHERE
CASE WHEN COLUMN1='value1' THEN 1
ELSE
(CASE WHEN COLUMN2='value2' THEN 1
ELSE
(CASE WHEN COLUMN3=value3 THEN 1 ELSE 0 END)
END)
END =1 ......