Nhờ hỗ trợ cú pháp truy vấn trong ADODB

Liên hệ QC
vanthinh3101

vanthinh3101

Thành viên tích cực
Tham gia
24/1/15
Bài viết
1,112
Được thích
1,436
Giới tính
Nam
Nghề nghiệp
Finance
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.
 

File đính kèm

  • Bao cao moi.xlsb
    10.4 KB · Đọc: 20
vanthinh3101 đã viết:
- JoinText dữ liệu từ nhiều dòng về 1 dòng (có loại trùng)
Nhấp chuột vào đây để mở rộ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
 

File đính kèm

  • GopDongDuLieu.xlsb
    27.1 KB · Đọc: 16
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
Nhấp chuột vào đây để mở rộng...
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?
 
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?
Nhấp chuột vào đây để mở rộ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.
 
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.
Nhấp chuột vào đây để mở rộng...
Em cám ơn bác đã chỉ dạy.
 
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?
Nhấp chuột vào đây để mở rộ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.
 
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).
 

File đính kèm

  • Bao cao moi.xlsb
    19.5 KB · Đọc: 6
Lần chỉnh sửa cuối:
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).
Nhấp chuột vào đây để mở rộng...
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).
Nhấp chuột vào đây để mở rộng...
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.
 
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.
Nhấp chuột vào đây để mở rộng...
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 đã 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é! :)
Nhấp chuột vào đây để mở rộng...
Anh bên Hồ Thiền Quang, welcome nhé, cf ngắm Thiên Nga...
 
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.
Nhấp chuột vào đây để mở rộng...
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
 

File đính kèm

  • Bao cao moi.xlsb
    210.3 KB · Đọc: 32
Lần chỉnh sửa cuối:
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.
Nhấp chuột vào đây để mở rộng...
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
 
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
Nhấp chuột vào đây để mở rộng...
Em cám ơn anh đã giúp đỡ ạ.
Bài đã được tự động gộp:

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
Nhấp chuột vào đây để mở rộng...
Em cám ơn anh đã góp ý và chỉ dẫn.
 
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.
Nhấp chuột vào đây để mở rộng...
Nếu là tôi thì tôi có thể làm báo biểu như sau:

1603509310044.png
 
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
Nhấp chuột vào đây để mở rộng...
Rất hay ạ.
Anh viết các lệnh trực tiếp trong Advanced Editor đúng không ạ?
 
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
...
Nhấp chuột vào đây để mở rộng...
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.
 
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

T
[Giúp đỡ] Tìm số lượng nhân viên đã tham gia làm lô hàng
Trả lời
12
Đọc
162
tsslump
T
A
[Góc giúp đỡ] Tìm kiếm, lọc và lấy dữ liệu từ nhiều file Word cấu trúc giống nhau vào 1 file excel mẫu
Trả lời
2
Đọc
49
anhgp1
A
T
  • Question
Ghép 2 cột dữ liệu
Trả lời
1
Đọc
22
Phuocam
Phuocam
N
Xin code tính tổng số cấu đúng viết trong "Visual Basic" trong PPT
Trả lời
1
Đọc
4K
hahuy_hien2000
H
8
Hàm thống kê số lượng thành viên câu lạc bộ theo từng đợt tuyển thành viên
Trả lời
3
Đọc
94
HieuCD
H
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom