Bạn có hai khả năng để làm những gì bạn muốn - một khả năng hoạt động cho các phiên bản MySQL từ 5. 5 (sử dụng tổng hợp) trở lên và phiên bản còn lại hoạt động cho MySQL 8 trở lên (sử dụng các chức năng của cửa sổ) Tất cả các mã bên dưới đều có sẵn trên fiddle tại đây. GHI CHÚ. Fiddle dành cho MySQL phiên bản 8. Nếu bạn muốn chạy phiên bản 5. 5 (hoặc 5. 6 hoặc 5. 7), vui lòng thay đổi máy chủ trong danh sách thả xuống ở đầu câu đố. Tôi đã làm điều này bởi vì INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
1 chỉ có thể được sử dụng với MySQL > 8. 0. 18 - các phiên bản trước không sử dụng đượcTrước tiên, hãy tạo bảng của bạn (bạn nên tự cung cấp bảng này dưới dạng câu đố cho loại câu hỏi này). Tôi đã thực hiện một vài thay đổi đối với giản đồ của bạn CREATE TABLE cas -- French for "case" - CASE is an SQL keyword and should not be used for table names!
(
case_id INTEGER NOT NULL,
cat TEXT NOT NULL,
birth DATE NOT NULL, -- store as a date - only requires 4 bytes
c_type TEXT NULL -- type is also a keyword, best avoided https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words
);
và sau đó cư nó. Tôi đã thêm một vài bản ghi cho mục đích thử nghiệm INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
RẤT QUAN TRỌNGSự hiểu biết về câu lệnh INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 là rất quan trọng để theo dõi phần còn lại của câu trả lời này. Truy vấn sẽ đi xuống thông qua INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 và khi gặp điều kiện phù hợp đầu tiên, truy vấn sẽ thực hiện phép gán và sau đó bỏ qua INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 và bắt đầu lại ở bản ghi tiếp theo - điều này hơi giống với lập trình INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
5 (và khác) Đây là lý do tại sao một INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
7 rất quan trọng để theo dõi rằng bài tập của bạn đang được tiến hành chính xác và bạn không bỏ sót điều gìbạn phải có một con đường rõ ràng cho các điều kiện của bạn. Khi có INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 câu nói bên trong người khác, bạn có thể rất dễ nhầm lẫn bản thân
Dạng truy vấn đầu tiên (sử dụng các chức năng của cửa sổ - chỉ khả dụng trong MySQL >= 8)SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
Tôi sẽ đi qua từng phần vì có một vài điều phức tạp phần 1 CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
điều này bao gồm Đây là một ví dụ về một INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 trong một INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2. Nếu chỉ có một bản ghi với một SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
1 nhất định thì theo định nghĩa, đó phải là chủ sở hữu trường hợp. Sau đó, chúng tôi kiểm tra ngày sinh của họ và nếu họ trên 21 tuổi (diễn biến thông thường), thì đặt giá trị thành SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
2, ngược lại là SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
3SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
4 là một ví dụ về chức năng cửa sổ. Đây là những công cụ cực kỳ mạnh mẽ và rất đáng để tìm hiểu kỹ (phần giới thiệu ngắn ở đây) - chúng sẽ đền đáp mọi nỗ lực đã bỏ ra để học chúng nhiều lầnCó nhiều cách khác để tính tuổi ở đây - tùy thuộc vào độ chính xác mà bạn yêu cầu Phần 2WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
Điều này bao gồm các trường hợp - b) Nếu CASE_ID có CAT CASE_OWNER VÀ VỢ (KHÔNG CÓ CON) cho CASE_ID, LOẠI phải là PAIR_NO_CHILD trong mỗi hàng khớp với CASE_ID cụ thể
Đoạn thú vị ở đây là cấu trúc SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
5 cho phép chúng ta phân biệt giữa những SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
1 có và không có SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
7phần thứ 3WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
Điều này bao gồm các trường hợp Phần thứ 4WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN(EXTRACT('YEAR' FROM AGE(birth))) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
Điều này bao gồm các trường hợp phần thứ 5FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
Ở đây, chúng tôi sử dụng một SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
-- 4th section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
THEN
CASE
WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!' -- ALWAYS!! have a default - helps to keep track of where you may have missed a case!
END
-- 5th section:
FROM
cas
ORDER BY
case_id,
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END,
birth DESC;
8 với một INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
2 "được nhúng" trong đó. Điều này cho phép chúng tôi có toàn quyền kiểm soát thứ tự các bản ghi của mình - dựa trên các yêu cầu, đây là một phương pháp sắp xếp hợp lý và cực kỳ hữu ích để thử nghiệmKết quảcase_id cat birth c_type c_t
20033738 CASE_OWNER 1996-04-08 PAIR_NO_CHILD
20033738 WIFE 1995-08-22 PAIR_NO_CHILD
20033831 CASE_OWNER 1975-03-05 SINGLE_WITH_CHILD
20033831 CHILD 2013-10-25 SINGLE_WITH_CHILD
...
.. snipped for brevity
...
dạng truy vấn thứ 2 (sử dụng tổng hợp và truy vấn con) - hoạt động từ ít nhất 5. 5SELECT
s.case_id,
s.cat,
s.birth,
--
-- 1st section: these sections correspond to the sections in query 1 above.
--
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
--
-- 2nd section:
--
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
--
-- 3rd section:
--
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
--
-- 4th section:
--
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
CASE
WHEN s.a_min < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
ELSE 'No assigned type!'
END AS c_t
FROM
(
SELECT
case_id,
cat,
birth,
(SELECT COUNT(c2.case_id)
FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
(SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)
FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
(SELECT FLOOR(MIN(DATEDIFF(NOW(), c4.birth) / 365.25))
FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min
FROM cas c1
ORDER BY
c1.case_id,
CASE
WHEN c1.cat = 'CASE_OWNER' THEN 1
WHEN c1.cat = 'WIFE' THEN 2
WHEN c1.cat = 'CHILD' THEN 3
END,
c1.birth DESC
) AS s;
Kết quảINSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
0Chỉ cần một vài điểm cần lưu ý Như đã đề cập ở trên, vui lòng luôn cung cấp câu hỏi của bạn khi thích hợp - thông thường nếu bạn muốn hiển thị dữ liệu thuộc bất kỳ loại nào CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
0 nghe có vẻ phi lý - một CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
1 thường đề cập đến một loại động vật hoang dã nào đó, hoặc có thể là động vật nuôi trong nhà hoặc trang trại, nhưng không phải con người. Tuy nhiên, cả '"con"_______11_______2"vợ"` chắc chắn đều ám chỉ con người. Bạn có thể muốn đặt "Cặp đôi có con" hoặc tương tựTôi đã bao gồm một CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
3 ở cuối câu đố
Vì vậy, để trả lời các câu hỏicâu hỏi của tôi là
- Điều này có thể thực hiện được bằng SQL không?
Có, xem ở trên - Điều này nên được giải quyết bằng SQL hay sử dụng ngôn ngữ lập trình?
Không có lý do gì để không sử dụng SQL trong trường hợp này. SQL hiện đã hoàn tất Turing, tuy nhiên chỉ vì bạn có thể làm điều gì đó bằng một ngôn ngữ nhất định, không có nghĩa là bạn nên làm điều đó bằng ngôn ngữ đó Sẽ đến lúc bạn có những yêu cầu rất phức tạp khi sử dụng SQL sẽ dẫn đến lợi nhuận giảm dần về nỗ lực của bạn so với nỗ lực của bạn. kết quả - kinh nghiệm sẽ cho bạn biết khi nào nên sử dụng một công cụ khác tốt hơn - Nếu điều này có thể thực hiện được trong SQL - nó nên được thực hiện như thế nào?
Xem ở trên. Một câu đố cho biết cách cập nhật bằng cách sử dụng tổng hợp và CTE được cung cấp tại đây Cuối cùng, phân tích hiệu suấtTôi đã xem xét các kế hoạch (từ MySQL>= 8) và không thể hiểu được ý nghĩa của chúng. Các lưu ý thông thường về kiểm tra hiệu suất được áp dụng - chỉ có 22 bản ghi trong bộ dữ liệu này. Bạn nên kiểm tra truy vấn/truy vấn của mình trên tập dữ liệu đầy đủ của riêng bạn với phần cứng của riêng bạn và các ràng buộc hệ thống khác. Tuy nhiên, chỉ để ghi lại, trên một phiên bản MySQL được cài đặt cục bộ (8. 0. 27), Windows 11, RAM 16GB, bộ xử lý 8 nhân, ổ NVMe 512GB Tôi đã nhận được những kết quả này mysql quý 2. 'Tổng_hợp_không_ORDER_BY - bắt đầu'; . 187165 giây Q1. 'Cửa sổ_không_ORDER_BY - kết thúc'; . 229719 giây quý 2. 'Tổng_hợp_với_ORDER_BY - kết thúc'; . 296987 giây Q1 'Window_with_ORDER_BY - kết thúc'; . 344441 giây PostgreSQL (cùng một máy) - sử dụng EnterpriseDB's 14. 1 nhị phân từ đây. Xem tại đây để biết câu đố về PostgreSQL với CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN EXTRACT('YEAR' FROM AGE(birth)) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
4Q1. Windows_order_by 1. 328 mili giây quý 2. Windows-NO-order_by 1. 35 mili giây Q1. Aggregate_order_by 1. 8 mili giây quý 2. Tổng hợp_no_order_by 2. 7 mili giây Các kết quả cho MySQL dường như không phù hợp với mức độ phức tạp của các kế hoạch hoặc thực tế là bảng phải được quét 4 lần (hoặc phải không?) Điều thực sự khó hiểu là MySQL chậm hơn 140 lần so với PostgreSQL?
Làm cách nào để sử dụng nhiều điều kiện trong MySQL?
Ví dụ - Kết hợp điều kiện AND & OR
. SELECT * FROM suppliers WHERE (state = 'Florida' AND supplier_name = 'IBM') OR (supplier_id > 5000); This MySQL WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND Condition and the OR Condition.
Bạn có thể có nhiều mệnh đề WHERE trong MySQL không?
Nhiều điều kiện mệnh đề WHERE sử dụng MySQL VÀ toán tử logic. Thông thường, bạn cần nhiều điều kiện lọc trong mệnh đề WHERE để nhắm mục tiêu các hàng dữ liệu cụ thể .
Làm cách nào để CHỌN nhiều giá trị trong MySQL?
Để chọn nhiều giá trị, bạn có thể sử dụng mệnh đề where với toán tử OR và IN . |