_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 讌螻覦れ

Contents

1 讌 螻覦 蠍磯蓋
2


1 讌 螻覦 蠍磯蓋 #

一危磯伎る 讌企. SQL 讌 蟆郁骸 讌 蟆企. 蠏碁蠍 覓語 讌 豌襴螳 企伎語 覃, 蠍一ヾ 3GL 襦蠏碁 覦朱 SQL 燕る, 焔レ 覓語襦 螻覩殊 蟆 . SELECT ~ FROM ~ 朱 DBMS 蟆郁骸讌 豌朱, 蠏 蟆郁骸 讌企. 讀, 覓朱Μ朱 企襦 豬蠍 る 蟆企. 企ゼ SQL 豐覲伎蟆 襷れ る.

願 企 谿 螻襯 螳讌る, 麹 覲旧″ 襦讌 伎 る 蟆企. 覿 EMP企 C語企 蟲譟一牡襯 伎 2谿 覦一伎 伎 蟆郁骸襯 豢ロ 襦蠏碁 燕る 企至 蟆螳? 螳襷企 襾碁Μ螳 . 覦襦 企 谿 螻覦 蠍一誤 豌襴覦螻 讌 螻覦 蠍一誤 豌襴覦 谿伎企. 覿 讌蠍 螳 SQL覓語 伎 る 蟆 讌 螻 覦 螳讌蠍 . るジ 襯 覲企襦 .
TEST 企 れ螻 螳 蟲煙  . 
Id
--
1
2
3
.
.
.
100
 
1 ~ 100蟾讌  螻, 企  螳 覈 1 蟆 螻 矩. 

谿 螻覦
USE TEMPDB
GO
 
IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST
IF OBJECT_ID('TEST2') IS NOT NULL DROP TABLE TEST2
GO
 
--1覿 100蟾讌 螳 れ願 企 
SELECT TOP 100 
(SELECT COUNT(*)  
FROM NORTHWIND..ORDERS B WHERE B.ORDERID > A.ORDERID) AS ID
        INTO TEST
FROM NORTHWIND..ORDERS A
ORDER BY 1
GO
 
--蠍一覿 貉れ襯 襴蠍  
-- 企  : TEST2
SELECT * INTO TEST2 FROM TEST
WHERE 1 = 0
 
--貉れ  
DECLARE cur CURSOR READ_ONLY
FOR SELECT *  FROM TEST
 
DECLARE @id int
 
--貉れ ろ
OPEN cur
 
--豺
FETCH NEXT FROM cur INTO @id
WHILE (@@fetch_status <> -1) BEGIN
             IF (@@fetch_status <> -2) BEGIN
                           SET @id = @id + 1
                           INSERT INTO TEST2 VALUES(@id)
             END
FETCH NEXT FROM cur INTO @id
END
 
CLOSE cur
DEALLOCATE cur
 
SELECT * FROM TEST2
GO

るゼ 覿企慨覃 朱企. 企 企 蟇伎 蟶朱伎 1 螻, 企 INSERT襯 讌 100覯 覦覲牛. 蠏碁 企 覈 SELECT 蟆郁骸企. 讀, 谿 螻 覦 襦 豌襴螻 螳 襭襯 襴 朱 貉れ襯 . 蠏碁る 讌 螻覦朱 覃 企蟾?

讌 螻覦

SELECT ID + 1 FROM TEST

企. 螳 蟆郁骸朱 豌 谿企ゼ 覿覈. 谿 螻 覦 襦 豌襴り 朱, 讌 螻 覦 豌襴伎 讌 蠏 讌 1 蟆 訖企. 襴襦 豕 螻襴讀朱 豌襴襯 蟆企. れ 襷 一危一 谿企 豌る 蟆 蟆企.

貉れ襯 讌 襷手 伎 豌襴 觜 覓語企. 蟇一 覈 蟆 貉れ襯 讌 . 貉れ襯 讌 螻 旧 蠍 覓語 貉れ襯 讌 襷朱 蟆企. 伎 貉れ襯 伎 蟆曙磯 貉れ襯 豕 豌襴伎 讌 譴碁る 焔レ 蟇煙 讌 . 伎 伎 覦蟆 蟆曙一企.

螳 襷る 蟆 讌 螻 覦 る朱 蟆 . る 谿 螻 讌(觜谿) 螻襯 企 螻褐 伎 蟆 覓語螳 蟆企. 企 蟆れ 谿 覿 企旭襯 覓手, 貉ろ朱ゼ 襷螳覃伎 螻 蟆渚 蟆一 蟆企. れ 螻螳 危語 れ 覦 企.

2 #

蠍 伎 襦襯 伎伎 3豐 伎 Duration 覲伎企 SQL覓語 覈磯 譴伎. 蠍磯るΜ 螳蠍 れ螻 螳 貎朱Μ螳 企.

delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'002360' 
and WorkDate between N'20040501' and N'20040510' and wkconfirm='Y'
 
delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'004327' 
and WorkDate between N'20040501' and N'20040510'
 
delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'005315' 
and WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'002360',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'004327',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'005315',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A 
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
go

覈磯 螻 伎 螳 伎 譯 . 覿覈 危襴貅伎 覓伎 殊 覯伎螻 讌螳. れ 螳覦蟆 螳 企 蟆語 覲伎手 . 危襴貅伎 SELECT襯 Grid 訖れ手, 襷一れ 企Ν 企欧瑚 覦覃 豌危 豌危 蟆 伎 曙, , 一危 れ 企伎 伎伎. 旧 貎朱Μ 蟆郁骸讌 るジ 讌 觸企 蟆企. 蠏碁覩襦 WHERE 譟郁唄 AND襦 覿譯手鍵襷 覃 蟆企. 讀, 螳覦 豌襴襯 螻 . れ螻 螳 螻豎譯殊.

DELETE FROM hrEmpWorkPlan
WHERE SiteCode = N'N100' 
AND WorkDate BETWEEN N'20040501' 
AND N'20040510' AND wkcONfirm='Y'
AND EmpCode in ('005315','004327','002360') --蠍 譯朱
 
INSERT INTO hrEmpWorkPlan (SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkCONfirm)
SELECT 
	A.SiteCode
,	B.EmpCode2
,	A.WorkDate
,	A.WkScheduleCode
,	'N'
FROM (
	SELECT 
		A.SiteCode
	,	A.EmpCode
	,	C.PaySystemCode
	,	A.WorkDate
	,	A.WkScheduleCode
	,	B.TotalWkTime
	,	A.WkCONfirm
    FROM hrEmpWorkPlan A 
		LEFT OUTER JOIN hrWkScheduleHeader B
			ON A.SiteCode = B.SiteCode AND A.WkScheduleCode = B.WkScheduleCode
		LEFT OUTER JOIN EmpMASter C
			ON A.SiteCode = C.SiteCode AND A.EmpCode = C.EmpCode
    WHERE C.ComCode = N'NXN1' 
	AND A.SiteCode LIKE N'N100%'  
	AND A.EmpCode LIKE N'013122%') A INNER JOIN (
				SELECT 
					'013122' AS EmpCode
				,	EmpCode AS EmpCode2 
				FROM EmpMASter 
				WHERE ComCode=N'NXN1' 
				AND EmpCode in ('005315','004327','002360')) B --蠍 譯朱
ON A.EmpCode=B.EmpCode 
WHERE A.SiteCode = N'N100' 
AND A.EmpCode = N'013122' 
AND  A.WorkDate between N'20040501' AND N'20040510'

願 1谿 企. れ襦 螳 襷一る 豌危 蟆 覓伎瑚 蠍一 伎 豌危 蟆 蟆企. 蠏 豌危 譟郁唄襷 覦蟆伎 SQL覓語 螻豎譴る 譬讌 蟆企. 危襴貅伎 DB襦 郁屋 螻, DBMS SQL覓語企 襦襯 語 讌 螳覲企 觜 襷 企. SQL覓語 貉危狩螻, ろ 蟆襷 DBMS Call 觜 襷襷豺 .
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

碁一 蟇煙 (企 るΠろ)