Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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)
 


Ïà¹ØÎĵµ£º

SQL server µÚÈýƪ ¶à±íÁª½Ó²éѯ

        ¶à±íÁª½Ó²éѯ
Ò»¡¢¶à±íÁª½Ó²éѯµÄ·ÖÀà
¶à±íÁª½Ó²éѯʵ¼ÊÉÏÊÇͨ¹ý¸÷¸ö±íÖ®¼ä¹²Í¬ÁеĹØÁªÐÔÀ´²éѯÊý¾ÝµÄ£¬ËüÊǹØÏµÊý¾Ý¿â²éѯ×îÖ÷ÒªµÄÌØÕ÷¡£
Áª½Ó²éѯ¿É·ÖΪÈý´óÀ࣬·ÖÁíΪ£º
1£®  ÄÚÁª½Ó¡£
2£®  ÍâÁª½Ó¡£
3£®  ½»²æÁª½Ó¡£
ÄÇôÎÒÃÇÒ»ÆðÀ´¿´Ò»ÏÂÈçºÎʹÓö ......

SQL ɾ³ýÖØ¸´Êý¾Ý£¬Ö»±£Áô1Ìõ

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 ......

SQL ÕÒ³ö¶à¸ö×Ö¶ÎÂú×ãÆäÖÐÒ»¸ö×ֶεÄÐÂд·¨

ÕÒ³ö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 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ