Nhờ hỗ trợ cú pháp truy vấn trong ADODB (1 người xem)

vanthinh3101 · 23/10/20

Em đang có vướng mắc xin nhờ mọi người giúp.
Dữ liệu giả định và kết quả cần có em đã để trong file đính kèm.
Em đang mắc về cú pháp truy vấn
- JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng)
- Tính tổng doanh thu theo từng KH (cần quy đổi giá trị đối với trường hợp Currency là USD).

Rất mong nhận được sự giúp đỡ của mọi người.

ongke0711 · 23/10/20

vanthinh3101 đã viết:
- JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng)

Đối với bài toán của bạn thì tôi vẫn phải dùng thêm một hàm "gộp dòng thành cột" rồi chạy câu lệnh SQL kết hợp hàm đó. Nói chung là cũng dài dòng. Không biết nếu dữ liệu lớn thì nó chạy như thế nào.
Tôi chưa biết cách nào khác.

Mã:

s = "SELECT ID FROM [" & sRngName & "] GROUP BY ID"
    Set oRst = GetADORecordset(s)
    oRst.MoveFirst
    i = 2
    Do Until oRst.EOF
        s1 = "SELECT ID, Name, 'VND','" & LienKetDong(oRst!ID) & "' AS Product2 , Sum(IIf([Currency]='USD',[Revenue]*23000,[Revenue])) AS Rev " & _
             "FROM [" & sRngName & "] " & _
             "GROUP BY ID, Name HAVING [ID]='" & oRst!ID & "'"
        'Debug.Print s1
        Set oRst2 = GetADORecordset(s1)
        Sheet1.Range("H" & i).CopyFromRecordset oRst2
        oRst2.Close
        i = i + 1
        oRst.MoveNext
    Loop

vanthinh3101 · 23/10/20

ongke0711 đã viết:
Đối với bài toán của bạn thì tôi vẫn phải dùng thêm một hàm "gộp dòng thành cột" rồi chạy câu lệnh SQL kết hợp hàm đó. Nói chung là cũng dài dòng. Không biết nếu dữ liệu lớn thì nó chạy như thế nào.
Tôi chưa biết cách nào khác.

Mã:

s = "SELECT ID FROM [" & sRngName & "] GROUP BY ID" Set oRst = GetADORecordset(s) oRst.MoveFirst i = 2 Do Until oRst.EOF s1 = "SELECT ID, Name, 'VND','" & LienKetDong(oRst!ID) & "' AS Product2 , Sum(IIf([Currency]='USD',[Revenue]*23000,[Revenue])) AS Rev " & _ "FROM [" & sRngName & "] " & _ "GROUP BY ID, Name HAVING [ID]='" & oRst!ID & "'" 'Debug.Print s1 Set oRst2 = GetADORecordset(s1) Sheet1.Range("H" & i).CopyFromRecordset oRst2 oRst2.Close i = i + 1 oRst.MoveNext Loop

Em cám ơn bác đã giúp đỡ.
Nó dài dòng thật bác ạ, chạy vòng lặp mãi.
Giá như ADODB hỗ trợ luôn Group_Concat có phải tốt không?

VetMini · 23/10/20

vanthinh3101 đã viết:
Em cám ơn bác đã giúp đỡ.
Nó dài dòng thật bác ạ, chạy vòng lặp mãi.
Giá như ADODB hỗ trợ luôn Group_Concat có phải tốt không?

ADODB không có bổn phận phải hỗ trợ lệnh SQL. Nó chỉ gởi câu truy vấn qua giao diện với CSDL thôi.
Bổn phận này thuộc về cỗ máy dùng để kết nối. Vì CSDL ở đây là bảng tính Excel cho nên cỗ máy dùng là Access. Và câu SQL phải tuân theo cú pháp của Access.
Nếu CSDL là SQL Server thì câu SQL sẽ theo cú pháp T-SQL. Nếu từ Oracle thì sẽ theo cú pháp SQL-Plus.

vanthinh3101 · 23/10/20

VetMini đã viết:
ADODB không có bổn phận phải hỗ trợ lệnh SQL. Nó chỉ gởi câu truy vấn qua giao diện với CSDL thôi.
Bổn phận này thuộc về cỗ máy dùng để kết nối. Vì CSDL ở đây là bảng tính Excel cho nên cỗ máy dùng là Access. Và câu SQL phải tuân theo cú pháp của Access.
Nếu CSDL là SQL Server thì câu SQL sẽ theo cú pháp T-SQL. Nếu từ Oracle thì sẽ theo cú pháp SQL-Plus.

Em cám ơn bác đã chỉ dạy.

ongke0711 · 23/10/20

vanthinh3101 đã viết:
Em cám ơn bác đã giúp đỡ.
Nó dài dòng thật bác ạ, chạy vòng lặp mãi.
Giá như ADODB hỗ trợ luôn Group_Concat có phải tốt không?

Trong code này tôi còn quên một phương thức Filter để giảm việc mở Recordset nhiều lần. Thứ 2 là giảm tương tác nhiều lần với sheet bằng việc gán kết quả vào mảng rồi gán một lần xuống sheet. Tôi rảnh xem lại như thế nào.

Cá ngừ F1 · 23/10/20

Chơi thử Dictionary được không @vanthinh3101

Mã:

Sub TongHop()

    Dim i&, sArr(), dArr(), Dic1 As Object, TG(), Dic2 As Object, k&
    TG = Range(Sheet1.[B14], Sheet1.[C15])
    sArr = Range(Sheet1.[A2], Sheet1.[E9])
    ReDim dArr(1 To UBound(sArr), 1 To 5)
    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set Dic2 = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(TG)
        Dic1(TG(i, 1)) = TG(i, 2)
    Next
   
    For i = 1 To UBound(sArr)
        itm = sArr(i, 1) & sArr(i, 2)
        If Not Dic2.Exists(itm) Then
            k = k + 1
            Dic2(itm) = k
            dArr(k, 1) = sArr(i, 1)
            dArr(k, 2) = sArr(i, 2)
            dArr(k, 3) = "VND"
            dArr(k, 4) = sArr(i, 4)
            dArr(k, 5) = sArr(i, 5) * Dic1.Item(sArr(i, 3))
        Else
            dArr(Dic2.Item(itm), 4) = sArr(i, 4) & ";" & dArr(Dic2.Item(itm), 4)
            dArr(Dic2.Item(itm), 5) = sArr(i, 5) * Dic1.Item(sArr(i, 3)) + dArr(Dic2.Item(itm), 5)
        End If
    Next
    Sheet1.[H14].Resize(i - 1, 5) = dArr
End Sub

Tôi có chế thêm bảng tỷ giá, vì giả sử có EUR/JPG/GPB....
Mỗi cái đoạn mã sản phẩm trùng nhau (trường Product tôi chưa nghĩ ra cách loại trùng, nhờ các thành viên hỗ trợ thêm phần loại trùng này).

vanthinh3101 · 23/10/20

Cá ngừ F1 đã viết:

Chơi thử Dictionary được không @vanthinh3101

Mã:

Sub TongHop()

    Dim i&, sArr(), dArr(), Dic1 As Object, TG(), Dic2 As Object, k&
    TG = Range(Sheet1.[B14], Sheet1.[C15])
    sArr = Range(Sheet1.[A2], Sheet1.[E9])
    ReDim dArr(1 To UBound(sArr), 1 To 5)
    Set Dic1 = CreateObject("Scripting.Dictionary")
    Set Dic2 = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(TG)
        Dic1(TG(i, 1)) = TG(i, 2)
    Next
  
    For i = 1 To UBound(sArr)
        itm = sArr(i, 1) & sArr(i, 2)
        If Not Dic2.Exists(itm) Then
            k = k + 1
            Dic2(itm) = k
            dArr(k, 1) = sArr(i, 1)
            dArr(k, 2) = sArr(i, 2)
            dArr(k, 3) = "VND"
            dArr(k, 4) = sArr(i, 4)
            dArr(k, 5) = sArr(i, 5) * Dic1.Item(sArr(i, 3))
     Else
            dArr(Dic2.Item(itm), 4) = sArr(i, 4) & ";" & dArr(Dic2.Item(itm), 4)
            dArr(Dic2.Item(itm), 5) = sArr(i, 5) * Dic1.Item(sArr(i, 3)) + dArr(Dic2.Item(itm), 5)
        End If
    Next
    Sheet1.[H14].Resize(i - 1, 5) = dArr
End Sub

Tôi có chế thêm bảng tỷ giá, vì giả sử có EUR/JPG/GPB....
Mỗi cái đoạn mã sản phẩm trùng nhau (trường Product tôi chưa nghĩ ra cách loại trùng, nhờ các thành viên hỗ trợ thêm phần loại trùng này).

Em cám ơn anh nhé!
Dùng Scripting.Dictionary thì em làm được rồi anh ạ.
Dữ liệu lớn nên em mới tính đến phương án dùng ADODB.

Mỗi cái đoạn mã sản phẩm trùng nhau (trường Product tôi chưa nghĩ ra cách loại trùng, nhờ các thành viên hỗ trợ thêm phần loại trùng này).

Anh thêm 1 thủ tục kiểm tra xem sản phẩm đó đã tồn tại chưa như sau:

PHP:

...
Else
    if Instr(1,darr(Dic2.Item(itm),4),sArr(i,4)) = 0 then
        dArr(Dic2.Item(itm), 4) = sArr(i, 4) & ";" & dArr(Dic2.Item(itm), 4)
    end if

Em xin phép góp ý 1 chút về code của anh:
- Không cần phải khai báo thêm 1 mảng kết quả vì mảng kết quả luôn luôn nhỏ hơn hoặc bằng mảng ban đầu.
- Anh cứ duyệt qua từng dòng của mảng ban đầu rồi thay thế kết quả luôn vào mảng ban đầu là được.

Cá ngừ F1 · 23/10/20

vanthinh3101 đã viết:
Em cám ơn anh nhé!
Dùng Scripting.Dictionary thì em làm được rồi anh ạ.
Dữ liệu lớn nên em mới tính đến phương án dùng ADODB.

Anh thêm 1 thủ tục kiểm tra xem sản phẩm đó đã tồn tại chưa như sau:

PHP:

... Else if Instr(1,darr(Dic2.Item(itm),4),sArr(i,4)) = 0 then dArr(Dic2.Item(itm), 4) = sArr(i, 4) & ";" & dArr(Dic2.Item(itm), 4) end if

Em xin phép góp ý 1 chút về code của anh:
- Không cần phải khai báo thêm 1 mảng kết quả vì mảng kết quả luôn luôn nhỏ hơn hoặc bằng mảng ban đầu.
- Anh cứ duyệt qua từng dòng của mảng ban đầu rồi thay thế kết quả luôn vào mảng ban đầu là được.

hoá ra múa rìu qua mắt thợ, sori sori... cảm ơn @vanthinh nhé

Bạn đã nghĩ đến dùng power query chưa? Chắc ăn được ADO (tôi nghĩ vậy)

vanthinh3101 · 23/10/20

Cá ngừ F1 đã viết:
hoá ra múa rìu qua mắt thợ, sori sori... cảm ơn @vanthinh nhé

Có gì đâu anh?
Ngày đầu vào diễn đàn, em cũng học hỏi từ đầu.
Diễn đàn cần những người nhiệt tình như anh thì mới phát triển được chứ.
Hôm nào rảnh, em gọi anh ngồi cafe/bia nhé!

Cá ngừ F1 · 23/10/20

vanthinh3101 đã viết:
Có gì đâu anh?
Ngày đầu vào diễn đàn, em cũng học hỏi từ đầu.
Diễn đàn cần những người nhiệt tình như anh thì mới phát triển được chứ.
Hôm nào rảnh, em gọi anh ngồi cafe/bia nhé!

Anh bên Hồ Thiền Quang, welcome nhé, cf ngắm Thiên Nga...

excel_lv1.5 · 23/10/20

vanthinh3101 đã viết:
Em đang có vướng mắc xin nhờ mọi người giúp.
Dữ liệu giả định và kết quả cần có em đã để trong file đính kèm.
Em đang mắc về cú pháp truy vấn
- JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng)
- Tính tổng doanh thu theo từng KH (cần quy đổi giá trị đối với trường hợp Currency là USD).

Rất mong nhận được sự giúp đỡ của mọi người.

Dữ liệu nhiều bạn có thể dùng các tool của excel, Power query hay power pivot đều được
Cho bạn cách làm của 2 tool trên, thay đổi dữ liệu bấm refresh trong pivot là được

tigertiger · 23/10/20

vanthinh3101 đã viết:
Em cám ơn anh nhé!
Dùng Scripting.Dictionary thì em làm được rồi anh ạ.
Dữ liệu lớn nên em mới tính đến phương án dùng ADODB.

Anh thêm 1 thủ tục kiểm tra xem sản phẩm đó đã tồn tại chưa như sau:

PHP:

... Else if Instr(1,darr(Dic2.Item(itm),4),sArr(i,4)) = 0 then dArr(Dic2.Item(itm), 4) = sArr(i, 4) & ";" & dArr(Dic2.Item(itm), 4) end if

Em xin phép góp ý 1 chút về code của anh:
- Không cần phải khai báo thêm 1 mảng kết quả vì mảng kết quả luôn luôn nhỏ hơn hoặc bằng mảng ban đầu.
- Anh cứ duyệt qua từng dòng của mảng ban đầu rồi thay thế kết quả luôn vào mảng ban đầu là được.

Bạn nên uyển chuyển sử dụng kết hợp giữa SQL và các công cụ của VBA thì hay hơn
Vì Concat mà dùng Engine của Access thì vất vả rồi, và như thế có thể lặp ở VBA còn nhanh hơn
Nên thử nghiệm kết hợp xem sao

vanthinh3101 · 23/10/20

excel_lv1.5 đã viết:
Dữ liệu nhiều bạn có thể dùng các tool của excel, Power query hay power pivot đều được
Cho bạn cách làm của 2 tool trên, thay đổi dữ liệu bấm refresh trong pivot là được

Em cám ơn anh đã giúp đỡ ạ.

Bài đã được tự động gộp: 23/10/20

tigertiger đã viết:
Bạn nên uyển chuyển sử dụng kết hợp giữa SQL và các công cụ của VBA thì hay hơn
Vì Concat mà dùng Engine của Access thì vất vả rồi, và như thế có thể lặp ở VBA còn nhanh hơn
Nên thử nghiệm kết hợp xem sao

Em cám ơn anh đã góp ý và chỉ dẫn.

ongke0711 · 23/10/20

vanthinh3101 đã viết:
Dùng Scripting.Dictionary thì em làm được rồi anh ạ.
Dữ liệu lớn nên em mới tính đến phương án dùng ADODB.

Vậy bạn chạy thử 2 cách (Dictionary và ADODB) thì thấy thời gian xử lý như thế nào đối với CSDL của bạn?

Hai Lúa Miền Tây · 24/10/20

vanthinh3101 đã viết:
Em đang có vướng mắc xin nhờ mọi người giúp.
Dữ liệu giả định và kết quả cần có em đã để trong file đính kèm.
Em đang mắc về cú pháp truy vấn
- JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng)
- Tính tổng doanh thu theo từng KH (cần quy đổi giá trị đối với trường hợp Currency là USD).

Rất mong nhận được sự giúp đỡ của mọi người.

Nếu là tôi thì tôi có thể làm báo biểu như sau:

Cá ngừ F1 · 24/10/20

excel_lv1.5 đã viết:
Dữ liệu nhiều bạn có thể dùng các tool của excel, Power query hay power pivot đều được
Cho bạn cách làm của 2 tool trên, thay đổi dữ liệu bấm refresh trong pivot là được

Rất hay ạ.
Anh viết các lệnh trực tiếp trong Advanced Editor đúng không ạ?

excel_lv1.5 · 24/10/20

Cá ngừ F1 đã viết:
Rất hay ạ.
Anh viết các lệnh trực tiếp trong Advanced Editor đúng không ạ?

Vâng, nó cho phép viết trực tiếp (M language), bạn có thể sử dụng các công cụ có sẵn rồi vào code sửa lại cho phù hợp với tình huống

VetMini · 24/10/20

excel_lv1.5 đã viết:
Dữ liệu nhiều bạn có thể dùng các tool của excel, Power query hay power pivot đều được
...

Trong giai đoạn phát triển Power Query và Power Pivot, MS đã có dự định dùng chúng cho Data Mining (qua Data Model). Vì vậy cỗ máy sau lưng bọn 'Power' này rất hiệu quả.
Nếu tôi không lầm thì cỗ máy sau lưng Data Model dựa trên kỹ thuật của SQL Server.

Vì vậy, nhiều người trong ngành của tôi (ngành phân tích dữ liệu) cho rằng khi dữ liệu nhiều thì bọn 'Power' sẽ tỏ rõ hiệu quả của chúng.

Túm lại, theo tôi thì nếu chơi với dữ liệu cỡ chục ngàn dòng trở lên mà không chịu học Power Query, cứ bám vào ADO là tự mình lỗi thời.

Cá ngừ F1 · 24/10/20

excel_lv1.5 đã viết:
Vâng, nó cho phép viết trực tiếp (M language), bạn có thể sử dụng các công cụ có sẵn rồi vào code sửa lại cho phù hợp với tình huống

Đọc cú pháp thấy khó nhằn quá ạ, trông không thân thiện như trong VBA.

Nhờ hỗ trợ cú pháp truy vấn trong ADODB (1 người xem)

Người dùng đang xem chủ đề này

Thành viên tích cực

File đính kèm

Thành viên gắn bó

File đính kèm

Thành viên tích cực

Ăn cùng góc phố

Thành viên tích cực

Thành viên gắn bó

( ͡° ͜ʖ ͡°)

File đính kèm

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Thành viên tích cực

( ͡° ͜ʖ ͡°)

Thành viên tích cực

File đính kèm

Coming back ...

Thành viên tích cực

Thành viên gắn bó

❆❆❆❆❆❆❆❆

( ͡° ͜ʖ ͡°)

Thành viên tích cực

Ăn cùng góc phố

( ͡° ͜ʖ ͡°)

Đếm ngược thời gian