Hỏi về lấy dữ liệu từ nhiều bảng SQL

longhoang2510 · January 15, 2020, 8:42am

Chào mọi người,
Như tiêu đề Topic của mình, mình đang gặp khó khi ghép nhiều bảng và lấy dữ liệu bằng SQL.
Mình có 3 bảng như sau:
Table 1:

CREATE TABLE [dbo].[TBL_CASE_LABEL](
[LABEL_ID] [varchar](15) NOT NULL,
[QUANTITY] [float] NULL,
[GROUP_ID] [int] NULL,
[TS_1] [datetime] NULL

Table 2: Table master có GROUP_ID là quan hệ 1-n với GROUP_ID 2 bảng còn lại

CREATE TABLE [dbo].[TBL_GROUP_MST](
[GROUP_ID] [int] IDENTITY(1,1) NOT NULL,
[GROUP_NAME] [nvarchar](20) NOT NULL

Table 3:

CREATE TABLE [dbo].[TBL_WLOT_LOC](
[ID] [int] IDENTITY(1,1) NOT NULL,
[GROUP_ID] [int] NULL,
[ISSUE_DATE] [date] NULL,
[QUANTITY] [float] NULL

Dưới đây là lệnh SQL của mình:

SELECT *
FROM ((TBL_CASE_LABEL AS a 
JOIN TBL_GROUP_MST AS b 
ON b.GROUP_ID = '61' 
AND a.GROUP_ID=b.GROUP_ID 
AND DATEPART(WK, a.TS_1) = DATEPART(WK, a.TS_1)
AND YEAR(a.TS_1) = YEAR(a.TS_1)
AND CONVERT(DATE,a.TS_1) BETWEEN CONVERT(DATE,'2020-01-10') AND CONVERT(DATE,'2020-01-10'))
JOIN TBL_WLOT_LOC AS c 
ON c.GROUP_ID = b.GROUP_ID
AND DATEPART(WK, c.ISSUE_DATE) = DATEPART(WK, c.ISSUE_DATE)
AND YEAR(c.ISSUE_DATE) = YEAR(c.ISSUE_DATE)
AND CONVERT(DATE,c.ISSUE_DATE) BETWEEN CONVERT(DATE,'2020-01-10') AND CONVERT(DATE,'2020-01-10'))

Mình muốn nạp dữ liệu là GROUP_ID cho bảng TBL_GROUP_MST và cùng một ngày giờ (Date) cho TS_1 của Table 1 và ISSUE_DATE của Table_2. Câu lệnh ghép 3 bảng của mình sai ở đâu vậy ạ. Mong mọi người giúp, thanks!

thaipt · January 16, 2020, 3:46pm

Query của bạn có một số vấn đề như sau:

Các statement DATEPART(WK, a.TS_1) = DATEPART(WK, a.TS_1), YEAR(a.TS_1) = YEAR(a.TS_1), DATEPART(WK, c.ISSUE_DATE) = DATEPART(WK, c.ISSUE_DATE) và YEAR(c.ISSUE_DATE) = YEAR(c.ISSUE_DATE) không có tác dụng vì luôn luôn đúng.
Statement CONVERT(DATE,a.TS_1) BETWEEN CONVERT(DATE,'2020-01-10') AND CONVERT(DATE,'2020-01-10')) thật ra không có gì khác với CONVERT(DATE,a.TS_1) = CONVERT(DATE,'2020-01-10').
Bạn không cần phải JOIN 2 table đầu rồi mới JOIN với table thứ ba mà có thể JOIN trực tiếp 3 table với nhau.
Bạn không nên dùng SELECT * mà nên chỉ định chính xác các column nào bạn cần query.

Theo định nghĩa của bạn, query cần trả về GROUP_ID, TS_1 và ISSUE DATE với điều kiện là TS_1 = ISSUE_DATE. Do đó, chúng ta có thể viết lại như sau:

SELECT b.GROUP_ID, a.TS_1, c.ISSUE_DATE
FROM TBL_CASE_LABEL AS a 
INNER JOIN TBL_GROUP_MST AS b ON a.GROUP_ID = b.GROUP_ID 
INNER JOIN TBL_WLOT_LOC AS c ON c.GROUP_ID = b.GROUP_ID
WHERE CONVERT(DATE, c.ISSUE_DATE) = CONVERT(DATE, a.TS_1)
AND b.GROUP_ID = '61'

longhoang2510 · January 17, 2020, 2:41am

Em cám ơn câu câu trả lời và góp ý của a.
Em xin bổ sung thêm 1 chút về vấn đề em đang gặp phải.
Em có 3 Table với dữ liệu bên trong như hình dưới đây, trong đó:
TBL_WOT_LOC.ISSUE_DATE và TBL.CASE_LABEL.TS_1 không phải là 2 cột dữ liệu giống nhau hoàn toàn. TBL.CASE_LABEL.TS_1 có thể có nhiều bản ghi của 1 ngày (10-01-2020) còn TBL_WOT_LOC.ISSUE_DATE thì số bản ghi ít hơn (10-01-2020). Nên khi em dùng:

CONVERT(DATE, c.ISSUE_DATE) = CONVERT(DATE, a.TS_1)

Thì kết quả trả ra bị lặp lại 2 lần cho bảng TBL.CASE_LABEL và lặp lại nhiều lần cho bảng TBL_WOT_LOC:

Em đang muốn tính tổng TBL_WOT_LOC.QUANTITY và TBL_CASE_LABEL.QUANTITY với dữ liệu nạp vào là 1 GROUP_ID và cùng chọn 1 ngày (TBL_WOT_LOC.ISSUE_DATE, TBL.CASE_LABEL.TS_1) theo nhóm (em thử GROUP_BY cho GROUP ID, SUM cho QUANTITY nhưng câu lệnh này không chạy được).

TBL_WLOT_LOC

TBL_GROUP_MST

thaipt · January 17, 2020, 10:37pm

Bạn có thể thử query này:

SELECT a.GROUP_ID, a.ACTUAL, b.TARGET, b.ISSUE_DATE
FROM (SELECT GROUP_ID, SUM(QUANTITY) AS ACTUAL, TS_1
FROM TBL_CASE_LABEL
GROUP BY GROUP_ID, TS_1) a
INNER JOIN TBL_GROUP_MST c ON a.GROUP_ID = c.GROUP_ID
INNER JOIN 
(SELECT GROUP_ID, SUM(QUANTITY) AS TARGET, ISSUE_DATE
FROM TBL_WLOT_LOC
GROUP BY GROUP_ID, ISSUE_DATE) b ON b.GROUP_ID = c.GROUP_ID 
WHERE CONVERT(DATE, b.ISSUE_DATE) = CONVERT(DATE, a.TS_1)
AND CONVERT(DATE, b.ISSUE_DATE) = '2019-12-09'
AND c.GROUP_ID = '56';

longhoang2510 · January 18, 2020, 4:14am

Thanks anh @thaipt,
Em chạy thử Query thì hiện tượng lặp ko gặp nữa nhưng kết quả trả ra chưa GROUP BY được theo hàm SUM của ACTUAL.

Em thử Query riêng lẻ để tính SUM của từng bảng, bỏ SELECT TS_1 và ISSUE_DATE thì được kết quả:

ACTUAL TARGET

Em cũng thử sửa lại theo hướng ý dựa trên câu lệnh của anh nhưng không được ạ.

thaipt · January 18, 2020, 4:23am

Tôi chỉ phỏng đoán câu trả lời vì tôi không có dữ liệu giống như bạn, nhưng đó là hướng giải quyết: Bạn tạo hai sub query để có sum của ACTUAL và TARGET trong mỗi sub query rồi join lại với nhau.

longhoang2510 · January 20, 2020, 2:46am

Hướng xử lý ban đầu của em cũng như thế nhưng vấp chỗ kết quả trả ra bị lặp ở 2 bảng nên bị lỗi @@

longhoang2510 · January 20, 2020, 4:26am

Đây là Script file của em. Anh xem giúp em với

thaipt · January 22, 2020, 3:52am

Tôi đã kiểm tra dữ liệu của bạn và tìm được lý do như sau:

Có nhiều row sau khi trả về là vì column TS_1 trong table TBL_CASE_LABEL của bạn có kiểu là DATETIME chứ không phải chỉ là DATE như là column ISSUE_DATE trong table TBL_WLOT_LOC. Vì vậy, trong kết quả trả về sẽ có nhiều row có cùng GROUP_ID và cùng DATE nhưng khác TIME. Tuy nhiên, trong sub query này chúng ta không thể dùng hàm CONVERT để đưa về cùng kiểu DATE trong quá trình group.

Cách giải quyết đơn giản nhất (nhưng không tối ưu) là bạn tiến hành filter lần nữa trong kết quả cuối cùng để loại bỏ các kết quả lặp lại bằng DISTINCT, câu query đầy đủ như sau:

SELECT DISTINCT a.GROUP_ID, a.ACTUAL, b.TARGET, b.ISSUE_DATE
FROM (SELECT GROUP_ID, SUM(QUANTITY) AS ACTUAL, TS_1
FROM TBL_CASE_LABEL
GROUP BY GROUP_ID, TS_1) a
INNER JOIN TBL_GROUP_MST c ON a.GROUP_ID = c.GROUP_ID
INNER JOIN 
(SELECT GROUP_ID, SUM(QUANTITY) AS TARGET, ISSUE_DATE
FROM TBL_WLOT_LOC
GROUP BY GROUP_ID, ISSUE_DATE) b ON b.GROUP_ID = c.GROUP_ID 
WHERE CONVERT(DATE, b.ISSUE_DATE) = CONVERT(DATE, a.TS_1)
AND CONVERT(DATE, b.ISSUE_DATE) = '2019-12-09'
AND c.GROUP_ID = '56';

longhoang2510 · January 30, 2020, 6:21am

Thanks a! Chúc a năm mới thành công, vạn sự như ý.
Trong sử dụng lọc trùng trên thì hàm SUM cho a.ACTUAL chưa trả ra kết quả:

GROUP_ID	ACTUAL	TARGET	ISSUE_DATE
56	30	951.56	2019-12-09

GROUP_ID	ACTUAL	TARGET	ISSUE_DATE
56	12	675.87	2019-12-29
56	18	675.87	2019-12-29
56	24	675.87	2019-12-29
56	30	675.87	2019-12-29
56	36	675.87	2019-12-29

Em chưa hiểu lắm là hàm SUM cho a.ACTUAL đã được chạy nhưng lại không tính ra kết quả, nếu hàm này hoạt động như của b.TARGET thì em nghĩ vấn đề ok.

longhoang2510 · February 3, 2020, 8:56am

@thaipt Anh giúp em xem lỗi tính tổng của ACTUAL với. Em thử sửa nhưng chưa khắc phục được lỗi ạ.

longhoang2510 · February 3, 2020, 10:58am

@thaipt Thank a đã đưa ra gợi ý và hướng giải quyết cho em cho bài toán này. Em đã thử theo cách này chạy ok:

SELECT c.GROUP_NAME, a.ACTUAL, b.TARGET
FROM (SELECT GROUP_ID, SUM(QUANTITY) AS TARGET
FROM TBL_WLOT_LOC
WHERE CONVERT(DATE, ISSUE_DATE) = '2019-12-09'
GROUP BY GROUP_ID) b
INNER JOIN TBL_GROUP_MST c ON b.GROUP_ID = c.GROUP_ID
INNER JOIN 
(SELECT GROUP_ID, SUM(QUANTITY) AS ACTUAL
FROM TBL_CASE_LABEL
WHERE CONVERT(DATE, TS_1) = '2019-12-09'
GROUP BY GROUP_ID) a ON a.GROUP_ID = c.GROUP_ID
AND c.GROUP_ID = '56';