select distinct(b.nid) HisID,
b.senddate scandate,
b.caseid,
b.DOCNO,
b.DOCID,
b.RC,
b.PAYTYPE,
cf_mailinglist.strName flowname,
'' sender,
'' senddatetime,
stuser.receiveuser ,
stuser.receivedate,
b.flowstatus,
'' TAT,
'' OVERTAT,
'' OVERSLA,
b.REJECTUSER
from cf_circulationhistory b
Left Join cf_circulationform on (b.nCirculationFormId = cf_circulationform.nID)
Left Join cf_mailinglist on (cf_circulationform.nMailingListId=cf_mailinglist.nID)
Left Join cf_formtemplate on (cf_formtemplate.nid=cf_mailinglist.ntemplateid)
,
(select * from (
SELECT ROWNUM as RN, e.receiveuser,e.receivedate,e.COMPLETEDATE ,e.nid,e.HisID
FROM (select ROWNUM as RN, cf_circulationhistory.nid , cf_circulationprocess.nCirculationHistoryId HisID,
userSend.strUserID receiveuser,
cf_circulationprocess.startdate receivedate, cf_circulationprocess.COMPLETEDATE, CF_ATTACHMENT.STRPATH
from(cf_circulationhistory)
Left Join cf_circulationform on (cf_circulationhistory.nCirculationFormId = cf_circulationform.nID)
Left Join cf_mailinglist on (cf_circulationform.nMailingListId=cf_mailinglist.nID)
Left Join cf_circulationprocess on (cf_circulationprocess.ncirculationhistoryid=cf_circulationhistory.nID)
Left Join cf_user userSend on (userSend.nID =cf_circulationprocess.nuserid and userSend.bDeleted =0)
Left Join cf_formtemplate on (cf_formtemplate.nid=cf_mailinglist.ntemplateid)
Left join CF_ATTACHMENT on (cf_circulationhistory.nID = CF_ATTACHMENT.ncirculationhistoryid)
order by cf_circulationprocess.nid desc )e )) stuser
where
b.SendDate >= to_date('01/02/2011 00:00:00','dd-mm-yyyy hh24:mi:ss')
and b.SendDate <= to_date('28/02/2011 23:59:00','dd-mm-yyyy hh24:mi:ss')
and cf_formtemplate.NGROUP in ('การเงินเขต','การเงินส่วนกลาง')
and b.nid=stuser.HisID
order by b.senddate
ถ้าเป็นคิวรี่นี้ โดยใส่ Rownum จะไม่มีข้อมูลค่ะ
select distinct(b.nid) HisID,
b.senddate scandate,
b.caseid,
b.DOCNO,
b.DOCID,
b.RC,
b.PAYTYPE,
cf_mailinglist.strName flowname,
'' sender,
'' senddatetime,
stuser.receiveuser ,
stuser.receivedate,
b.flowstatus,
'' TAT,
'' OVERTAT,
'' OVERSLA,
b.REJECTUSER
from cf_circulationhistory b
Left Join cf_circulationform on (b.nCirculationFormId = cf_circulationform.nID)
Left Join cf_mailinglist on (cf_circulationform.nMailingListId=cf_mailinglist.nID)
Left Join cf_formtemplate on (cf_formtemplate.nid=cf_mailinglist.ntemplateid)
,
(select * from (
SELECT ROWNUM as RN, e.receiveuser,e.receivedate,e.COMPLETEDATE ,e.nid,e.HisID
FROM (select ROWNUM as RN, cf_circulationhistory.nid , cf_circulationprocess.nCirculationHistoryId HisID,
userSend.strUserID receiveuser,
cf_circulationprocess.startdate receivedate, cf_circulationprocess.COMPLETEDATE, CF_ATTACHMENT.STRPATH
from(cf_circulationhistory)
Left Join cf_circulationform on (cf_circulationhistory.nCirculationFormId = cf_circulationform.nID)
Left Join cf_mailinglist on (cf_circulationform.nMailingListId=cf_mailinglist.nID)
Left Join cf_circulationprocess on (cf_circulationprocess.ncirculationhistoryid=cf_circulationhistory.nID)
Left Join cf_user userSend on (userSend.nID =cf_circulationprocess.nuserid and userSend.bDeleted =0)
Left Join cf_formtemplate on (cf_formtemplate.nid=cf_mailinglist.ntemplateid)
Left join CF_ATTACHMENT on (cf_circulationhistory.nID = CF_ATTACHMENT.ncirculationhistoryid)
order by cf_circulationprocess.nid desc )e )where RN=1) stuser
where
b.SendDate >= to_date('01/02/2011 00:00:00','dd-mm-yyyy hh24:mi:ss')
and b.SendDate <= to_date('28/02/2011 23:59:00','dd-mm-yyyy hh24:mi:ss')
and cf_formtemplate.NGROUP in ('การเงินเขต','การเงินส่วนกลาง')
and b.nid=stuser.HisID
order by b.senddate
ผลลัพธ์จะประมาณนี้ป่าวคะ
กรณีนี้เราจะเรียกกันสั้นๆว่า first from many row selection/retrival
ค่อนข้างจะพบบ่อยในงานที่เป็น request จากฝ่ายบริหาร
และเป็นอะไรที่ค่อนข้างจะวุ่นวาย ดังที่ query ของจริงที่ยกมาข้างบน
ข่าวดีคือ มันทำได้ค่ะ เร็วกว่าการ วน loop ร้อยเท่าพันทวี
และข่าวร้ายคือมันไม่ใช่ฟังก์ชันหรือ feature มาตรฐานที่ ANSI กำหนดไว้
เพราะหากใช้การ SELECT - JOIN ปกติ ก็ทำได้แต่จะช้าและซับซ้อนเอามากๆ
คือตัวอย่างนี่ทำจาก MS SQL SERVER ค่ะ ใช้ KEYWORD พิเศษ CROSS APPLY
เข้ามาช่วย ซึ่งใน ORACLE ไม่มี !!
อะข่าวดีถัดไปคือ ORACLE จะมี AGGREGATE FUNCTION : FIRST มาให้แทน
ซึ่งทำหน้าที่เหมือนกัน
ข่าวร้ายถัดไป เครื่องนี้มะมี ORACLE ค่ะ ตัว SERVER ส่วนตัวที่ใช้ TEST
และลง ORACLE เอาไปรัน DEMO งานอยู่ เลยไม่แน่ใจกับคำตอบค่ะ
คงจาช่วยได้เท่านี้นะคะ ลองไปดูที่ link นี้ดู (ไม่ได้ทดสอบเลยไม่แน่ใจอะค่ะ) Howto select first value in a group by bunch of rows.
ส่วนของ ms sql server จะลง code ไว้เผื่อสำหรับคนอื่นละกันนะคะ
Code (C#)
-- BEGINNING TEST DATA
USE [test]
GO
-- BEGIN : CREATE TABLE SECTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[simple_document_header]') AND type in (N'U'))
DROP TABLE [dbo].[simple_document_header]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[simple_document_header](
[id] [int] NOT NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_simple_document_header] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[simple_document_flow]') AND type in (N'U'))
DROP TABLE [dbo].[simple_document_flow]
GO
CREATE TABLE [dbo].[simple_document_flow](
[id] [int] IDENTITY(1,1) NOT NULL,
[document_id] int NULL ,
[receive_date_time] datetime NULL ,
[user_name] [varchar](50) NULL,
CONSTRAINT [PK_simple_document_flow] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- END : CREATE TABLE SECTION
-- BEGIN : INSERT DUMMY DATA
INSERT INTO [test].[dbo].[simple_document_header]
([id] ,[name])
VALUES
(1 ,'Account article no:191') ,
(2 ,'Account article no:562') ,
(3 ,'Inventory request : 141')
GO
INSERT INTO [test].[dbo].[simple_document_flow]
([document_id]
,[receive_date_time]
,[user_name])
VALUES
(1 , DATEADD ( DAY ,-1 , GETDATE()) ,'Nena') ,
(1 , DATEADD ( DAY ,-2 , GETDATE()) ,'Anne') ,
(1 , DATEADD ( DAY ,-3 , GETDATE()) ,'Cifer') ,
(2 , DATEADD ( DAY ,-2 , GETDATE()) ,'Natt') ,
(2 , DATEADD ( DAY ,-4 , GETDATE()) ,'Bree') ,
(2 , DATEADD ( DAY ,-5 , GETDATE()) ,'Brom') ,
(2 , DATEADD ( DAY ,-6 , GETDATE()) ,'Briona') ,
(3 , DATEADD ( DAY ,-2 , GETDATE()) ,'Karl')
GO
SELECT *
FROM [simple_document_header] AS [Header];
SELECT [id] ,[document_id] , convert(varchar ,[receive_date_time],109) ,[user_name]
FROM [simple_document_flow] AS [Flow] ;
SELECT [Header].* ,[Complicate_Flow].*
FROM [simple_document_header] AS [Header]
CROSS APPLY
(
SELECT TOP 1 *
FROM [simple_document_flow] AS [Flow]
WHERE [Flow].[document_id] = [Header].[id]
ORDER BY [receive_date_time]
) AS [Complicate_Flow]
;
-- END : INSERT DUMMY DATA
-- BEGIN : CLEAR TABLE SECTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[simple_document_header]') AND type in (N'U'))
DROP TABLE [dbo].[simple_document_header]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[simple_document_flow]') AND type in (N'U'))
DROP TABLE [dbo].[simple_document_flow]
GO
-- END : CLEAR TABLE SECTION
Load balance : Server 05
