Đố vui về ADO, DAO.

dhn46 · 5/7/13

Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

    lsSQL = "Select * From " _
            & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _
            & "Union all " _
            & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _
            & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _
            & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

Hai Lúa Miền Tây · 5/7/13

dhn46 đã viết:
Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

lsSQL = "Select * From " _ & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _ & "Union all " _ & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _ & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _ & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

Tất cả phương án bạn đưa ra đều đúng yêu cầu. Tuy nhiên phải gõ tay cột gom nhóm. Bạn thử không gõ tay cột này xem sao nhé.

VetMini · 5/7/13

dhn46 đã viết:
Em đưa ra 1 phương án nữa, cái này có được không anh HLMT?

Mã:

lsSQL = "Select * From " _ & "(Select top 10 f1,f2,f3,f4,'1:10' as Col from [Data$A2:D600] Order by f1 " _ & "Union all " _ & "Select top 10 a.f1,a.f2,a.f3,a.f4,'91:10' as Col " _ & "from (Select f1,f2,f3,f4 From [Data$A2:D600] Order by f1 Desc)as a) as b " _ & "Order by b.f1"

ADO ứng dụng viết Code rất hay, ngày trước khi chưa biết thì mày mò với Array, Dictionary. Giờ ứng dụng ADO thấy đơn giản hơn và cũng rất thú vị, không hiểu sao Topic này ít người tham gia thế?

Cảm ơn anh HLMT nhiều!

ADO có tất cả 5 Objects, trong Excel ta sử dụng 3.
Khi sử dụng ADO, Excel VBA dùng engine của Access. Trước đây engine này theo tiêu chuẩn JET (Access giấu mặt); về sau này MS xác định thẳng nó là Access. (*)
Sử dụng nó có 2 điểm bất lợi so với code trực tiếp (array mapping):
1. Đối tượng ADO bắt buộc phải xem file (xls*, txt, ...) là file phẳng (flat) và xuôi (sequential) cho nên nó phải tuân thủ theo các giới hạn về cột, dòng; và các giới hạn về thao tác (ví dụ cách chèn dòng, xoá dòng)
2. Phải học thêm ngôn ngữ SQL. SQL của Access lại không theo sát tiêu chuẩn cho nên lúc sử dụng phải học hết các ngoại lệ của nó.

Vì các bất lợi trên cho nên người ta chỉ dùng nó để import dữ liệu hàng loạt. Các thao tác khác chỉ biết chơi cho vui thôi chứ không nên xem như là một chiều hướng lập trình.

(*) Tôi chỉ nói trong phạm vi kết nối file xls,csv,txt mà chúng ta vẫn dùng ở đây. Đương nhiên là nếu bạn cho OLE DB nối với SQL Server, Orracle thì chúng dùng engine và tiêu chuẩn SQL khác.

VetMini · 5/7/13

Nếu id là số liên tục thì có thể không dùng Union. Nhưng lệnh này sẽ chạy chậm.

select * from Bảng where (id <= 10 or (select max(id) from Bang) - id <= 9)

hungpecc1 · 5/7/13

VetMini đã viết:
Nếu id là số liên tục thì có thể không dùng Union. Nhưng lệnh này sẽ chạy chậm.

select * from Bảng where (id <= 10 or (select max(id) from Bang) - id <= 9)

cho Em hỏi thêm anh là: nếu trong cột ID định dạng là text --> thì các mệnh đề WHERE ID <=10 có vẻ không ổn cho lắm , lúc này ta phải giải quyết như thế nào !

VetMini · 5/7/13

hungpecc1 đã viết:
cho Em hỏi thêm anh là: nếu trong cột ID định dạng là text --> thì các mệnh đề WHERE ID <=10 có vẻ không ổn cho lắm , lúc này ta phải giải quyết như thế nào !

Bạn có thấy chuỗi từ "Nếu id là số liên tục" không?
Nếu nó không phải là số liên tục thì phải dùng phương pháp rank. Tôi luời tra cứu cách sử dụng hàm rank trong Access quá (tôi chỉ chuyên dùng T-SQL và SQL Plus)

hungpecc1 · 5/7/13

VetMini đã viết:
Bạn có thấy chuỗi từ "Nếu id là số liên tục" không?
Nếu nó không phải là số liên tục thì phải dùng phương pháp rank. Tôi luời tra cứu cách sử dụng hàm rank trong Access quá (tôi chỉ chuyên dùng T-SQL và SQL Plus)

Không ý em không phải "ID có liên tục không ", mà bây giờ tại file excel , tất cả các giá trị cột ID là text (ví dụ như 1 --> "1") thì câu lệnh WHERE ID < = 10 sẽ gặp lỗi !

VetMini · 5/7/13

Nếu biết chắc nó là chuỗi số thì dùng hàm CInt hoặc CLng
select * from Bang where (CInt(id) <= 10 or (select CInt(max(id)) from Bang) - CInt(id) <= 9)

Đính chính: CInt là hàm của VBA, trong query dùng hàm VAL

Hai Lúa Miền Tây · 5/7/13

VetMini đã viết:
Nếu biết chắc nó là chuỗi số thì dùng hàm CInt hoặc CLng
select * from Bang where (CInt(id) <= 10 or (select CInt(max(id)) from Bang) - CInt(id) <= 9)

Đính chính: CInt là hàm của VBA, trong query dùng hàm VAL

Ta dùng between cho nó gọn và nhẹ

VetMini · 6/7/13

Sau một đêm suy nghĩ, đã tìm ra cách viết không dùng UNION và cũng không phải dựa vào tính chất liên tục của ID.

Select * from Bang
Where ID IN (Select Top 10 ID From Bang Order By ID Asc)
OR ID IN (Select Top 10 ID From Bang Order By ID Desc)

Cách này chỉ là mẹo khai triển định nghĩa của UNION. Lưu ý là tính tố IN có thể chạy rất chậm đối với một số CSDL

@HLMT:
BETWEEN hàm tính tố AND. Ở đây tuy đề bài là 10 đầu vả 10 cuối nhưng nếu không dùng Union thì giải thuật là 10+10 ở đầu hoặc ở cuối

Hai Lúa Miền Tây · 9/7/13

VetMini đã viết:
Sau một đêm suy nghĩ, đã tìm ra cách viết không dùng UNION và cũng không phải dựa vào tính chất liên tục của ID.

Select * from Bang
Where ID IN (Select Top 10 ID From Bang Order By ID Asc)
OR ID IN (Select Top 10 ID From Bang Order By ID Desc)

Cách này chỉ là mẹo khai triển định nghĩa của UNION. Lưu ý là tính tố IN có thể chạy rất chậm đối với một số CSDL

@HLMT:
BETWEEN hàm tính tố AND. Ở đây tuy đề bài là 10 đầu vả 10 cuối nhưng nếu không dùng Union thì giải thuật là 10+10 ở đầu hoặc ở cuối

Còn cột Group thì bạn sẽ xử lý như thế nào hả bạn?

Hai Lúa Miền Tây · 10/7/13

Hai Lúa Miền Tây đã viết:
Tất cả phương án bạn đưa ra đều đúng yêu cầu. Tuy nhiên phải gõ tay cột gom nhóm. Bạn thử không gõ tay cột này xem sao nhé.

Hai Lúa Miền Tây đã viết:
Còn cột Group thì bạn sẽ xử lý như thế nào hả bạn?

Không biết các bạn giải quyết được yêu cầu trên chưa?

dhn46 · 10/7/13

Hai Lúa Miền Tây đã viết:
Không biết các bạn giải quyết được yêu cầu trên chưa?

Em xin đưa ra 2 câu truy vấn cho 2 phương án em giải lần trước
Gom nhóm không đánh tay các trường

1/ Dùng Union

Mã:

lsSQL = "Select a.*,'1:10' as f5 from [Data$A2:D600] a where f1<11 union all select b.*,'91:100' as f5 from [Data$A2:D600] b where f1>90"

2/ Không dùng Union

Mã:

     lsSQL = "Select a.*,iif(f1<11,'1:10','91:100') as col from [Data$A2:D600] a where f1<11 or f1>90"

Hai Lúa Miền Tây · 10/7/13

dhn46 đã viết:
Em xin đưa ra 2 câu truy vấn cho 2 phương án em giải lần trước
Gom nhóm không đánh tay các trường

1/ Dùng Union

Mã:

lsSQL = "Select a.*,'1:10' as f5 from [Data$A2:D600] a where f1<11 union all select b.*,'91:100' as f5 from [Data$A2:D600] b where f1>90"

2/ Không dùng Union

Mã:

lsSQL = "Select a.*,iif(f1<11,'1:10','91:100') as col from [Data$A2:D600] a where f1<11 or f1>90"

Rất sáng tạo nhưng vẫn chưa theo yêu cầu. Thôi thì làm từng phần, là làm sao lấy ra kết quả cột Group (1:10,11:20,21:30...91:100), bỏ điều kiện đi nhé.

dhn46 · 10/7/13

Hai Lúa Miền Tây đã viết:
Rất sáng tạo nhưng vẫn chưa theo yêu cầu. Thôi thì làm từng phần, là làm sao lấy ra kết quả cột Group (1:10,11:20,21:30...91:100), bỏ điều kiện đi nhé.

Giờ em mới hiểu câu hỏi bỏ đánh trường bằng tay...hihi +-+-+-+

Em đưa ra phương án như thế này, anh góp ý nhé

Mã:

lsSQL = "Select a.*,int((f1-1)/10)*10+1&':'&int((f1-1)/10+1)*10 as col from [Data$A2:D600] a where f1<11 or f1>90"

Hai Lúa Miền Tây · 11/7/13

dhn46 đã viết:
Giờ em mới hiểu câu hỏi bỏ đánh trường bằng tay...hihi
Em đưa ra phương án như thế này, anh góp ý nhé

Mã:

lsSQL = "Select a.*,int((f1-1)/10)*10+1&':'&int((f1-1)/10+1)*10 as col from [Data$A2:D600] a where f1<11 or f1>90"

Sẽ mất hết 1 dòng dữ liệu đầu tiên bạn à.

VetMini · 11/7/13

Cột group tôi cố tình không nói tới. Lý do là bị ngặt chỗ, nếu bảng có dưới 20 dòng thì sao? Trong câu truy vấn trước đây, tôi không có đề cập tới chuyện này, nhưng nó vẫn có thể giải quyết được dễ dàng bằng cách dùng lệnh lọc DISTINCT. Một khi phải đặt Group thì group ra sao? Những dòng trùng nhau thuộc về group nào?

Hai Lúa Miền Tây · 11/7/13

VetMini đã viết:
Cột group tôi cố tình không nói tới. Lý do là bị ngặt chỗ, 1./ nếu bảng có dưới 20 dòng thì sao? Trong câu truy vấn trước đây, tôi không có đề cập tới chuyện này, nhưng nó vẫn có thể giải quyết được dễ dàng bằng cách dùng lệnh lọc DISTINCT. Một khi phải đặt Group thì group ra sao? 2./ Những dòng trùng nhau thuộc về group nào?

1./ Dưới 20 dòng thì cũng chạy bình thường
2./ Những dòng trùng nhau sẽ thuộc về group thỏa điều kiện với nó. Ví dụ có 15 dòng hoặc n dòng nhỏ hơn 10 thì toàn bộ dòng đó sẽ thuộc về Group 1:10

dhn46 · 11/7/13

Hai Lúa Miền Tây đã viết:
Sẽ mất hết 1 dòng dữ liệu đầu tiên bạn à.

Anh HLMT em kiểm tra sao không thấy mất nhỉ? Nhưng với câu trả lời này của anh thì chắc chắn còn phương án "tuyệt chiêu" nữa...

Hai Lúa Miền Tây · 12/7/13

dhn46 đã viết:
Anh HLMT em kiểm tra sao không thấy mất nhỉ? Nhưng với câu trả lời này của anh thì chắc chắn còn phương án "tuyệt chiêu" nữa...

Thôi trả lời luôn, đó là mình dùng hàm Partition để chia dữ liệu:

[GPECODE=sql]Sub Loc_HLMT()
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "SELECT ID, I_DATE, [Q'TY], REMARKS, trim(Partition([id],1,100,10)) " & _
"FROM [Data$] where trim(Partition([id],1,100,10)) in ('1: 10','91:100')"
End With
With Sheet1
.[H2:L100].ClearContents
.[H2].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing

End Sub

[/GPECODE]

Đố vui về ADO, DAO.

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Ăn cùng góc phố

Ăn cùng góc phố

Thành viên gắn bó

Ăn cùng góc phố

Thành viên gắn bó

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Hướng tới tương lai

❆❆❆❆❆❆❆❆

Ăn cùng góc phố

❆❆❆❆❆❆❆❆

Hướng tới tương lai

File đính kèm

❆❆❆❆❆❆❆❆