Как выбрать отдельную сумму в мультитаблице
Как я могу выбирать разные суммы за несколькими столами с multi состоянии (В (выбрать..)). Я попробовал код для отображения данных в таблице, код может отображать соответствующие результаты, но когда я объединяю код для отображения данных в 2 таблицах, результаты не подходят.
Что я уже пробовал:
этот код, когда я пытаюсь
На сервере sql1
SELECT p.Content_Number AS PartNumber, p.Content_Name AS PartName, SUM(IF(p.ID_Packing IN (SELECT pck0.ID_Packing FROM tb_packing pck0 WHERE pck0.ID_Project_Item = 1), p.Quantity, 0)) AS Quantity FROM tb_packing_plan p WHERE p.ID_Packing IN (SELECT pck.ID_Packing FROM tb_packing pck WHERE pck.ID_Project_Item = 1) GROUP BY p.Content_Number ORDER BY p.Content_Number
SQL2
SELECT DISTINCT p.Content_Number AS PartNumber, p.Content_Name AS PartName, SUM(IF(r.ID_Packing IN (SELECT pck1.ID_Packing FROM tb_packing pck1 WHERE pck1.ID_Project_Item = 1), r.Quantity, 0)) AS Quantity2 FROM tb_packing_plan p LEFT JOIN tb_packing_real r ON r.Content_Number = p.Content_Number WHERE p.ID_Packing IN (SELECT pck.ID_Packing FROM tb_packing pck WHERE pck.ID_Project_Item = 1) GROUP BY p.ID_Packing ,p.Content_Number ORDER BY p.Content_Number
SQL 3
SELECT DISTINCT p.Content_Number AS PartNumber, p.Content_Name AS PartName, SUM(IF(p.ID_Packing IN (SELECT pck.ID_Packing FROM tb_packing pck WHERE pck.ID_Project_Item = 1), p.Quantity, 0)) AS Quantity, SUM(IF(r.ID_Packing IN (SELECT pck1.ID_Packing FROM tb_packing pck1 WHERE pck1.ID_Project_Item = 1), r.Quantity, 0)) AS Quantity2 FROM tb_packing_plan p LEFT JOIN tb_packing_real r ON r.Content_Number = p.Content_Number WHERE p.ID_Packing IN (SELECT pck.ID_Packing FROM tb_packing pck WHERE pck.ID_Project_Item = 1) GROUP BY p.ID_Packing ,p.Content_Number ORDER BY p.Content_Number
Вот моя база данных и таблица данных dummping
CREATE TABLE `tb_project_item` ( `ID_Project_Item` int(11) NOT NULL, `Item_Code` varchar(50) NOT NULL, `Item_Name` varchar(255) NOT NULL, `Quantity` int(11) NOT NULL, `Spesication` text NOT NULL ); CREATE TABLE `tb_packing` ( `ID_Packing` int(11) NOT NULL, `ID_Project_Item` varchar(15) NOT NULL, `Packing_Code` varchar(50) NOT NULL, `Packing_Name` varchar(255) NOT NULL, `Packing_Quantity` int(11) NOT NULL, `Delivery_Date` date NOT NULL, `Status_Packing` varchar(25) NOT NULL, `PathName_Packing` varchar(255) NOT NULL ); CREATE TABLE `tb_packing_plan` ( `ID_Packing_Plan` int(11) NOT NULL, `ID_Packing` varchar(15) NOT NULL, `Content_Number` varchar(50) NOT NULL, `Content_Name` varchar(255) NOT NULL, `Content_Type` varchar(50) NOT NULL, `Quantity` int(11) NOT NULL ); CREATE TABLE `tb_packing_real` ( `ID_Packing_Real` int(11) NOT NULL, `ID_Packing` varchar(15) NOT NULL, `Content_Number` varchar(50) NOT NULL, `Quantity` int(11) NOT NULL, `Input_Date` date NOT NULL, `User` varchar(50) NOT NULL ); INSERT INTO `tb_project_item` (`ID_Project_Item`, `Item_Code`, `Item_Name`, `Quantity`, `Spesication`) VALUES (1, 'DP001', 'Dummy Project 001', 1, ''), (2, 'DP002', 'Dummy Project 002', 1, ''); INSERT INTO `tb_packing` (`ID_Packing`, `ID_Project_Item`, `Packing_Code`, `Packing_Name`, `Packing_Quantity`, `Delivery_Date`, `Status_Packing`, `PathName_Packing`) VALUES (1, '1', '111', 'DP001 Lorem Ipsum 001', 1, '2018-07-28', '', ''), (2, '1', '222', 'DP001 Lorem Ipsum 002', 1, '2018-07-27', '', ''), (3, '1', '333', 'DP001 Lorem Ipsum 001C', 1, '2018-07-27', '', ''), (4, '1', '444', 'DP001 Lorem Ipsum 002A', 1, '2018-07-26', '', ''), (5, '1', '555', 'DP001 Lorem Ipsum 003', 1, '2018-07-27', '', ''), (6, '2', 'DPLI001', 'DP002 Lorem Ipsum 001', 1, '2018-10-28', '', ''), (7, '2', 'DPLI002', 'DP002 Lorem Ipsum 002', 1, '2018-11-28', '', ''), (8, '2', 'DPLI003', 'DP002 Lorem Ipsum 003', 1, '2018-12-28', '', ''); INSERT INTO `tb_packing_plan` (`ID_Packing_Plan`, `ID_Packing`, `Content_Number`, `Content_Name`, `Content_Type`, `Quantity`) VALUES (1, '1', '1212121212', 'Lorem Ipsum Dolor Sit Amet 12', 'Manufacturing', 4), (2, '1', '1313131313', 'Lorem Ipsum Dolor Sit Amet 13', 'Manufacturing', 2), (3, '1', '1414141414', 'Lorem Ipsum Dolor Sit Amet 14', 'Manufacturing', 6), (4, '1', '1515151515', 'Lorem Ipsum Dolor Sit Amet 15', 'Manufacturing', 2), (5, '2', '2626262626', 'Lorem Ipsum Dolor Sit Amet 26', 'Manufacturing', 9), (6, '2', '2727272727', 'Lorem Ipsum Dolor Sit Amet 27', 'Manufacturing', 12), (7, '3', '3131313131', 'Lorem Ipsum Dolor Sit Amet 31', 'Manufacturing', 5), (8, '3', '3232323232', 'Lorem Ipsum Dolor Sit Amet 32', 'Manufacturing', 4), (9, '3', '3535353535', 'Lorem Ipsum Dolor Sit Amet 35', 'Manufacturing', 5), (10, '3', '3636363636', 'Lorem Ipsum Dolor Sit Amet 36', 'Manufacturing', 2), (11, '3', '3737373737', 'Lorem Ipsum Dolor Sit Amet 37', 'Manufacturing', 5), (12, '3', '3838383838', 'Lorem Ipsum Dolor Sit Amet 38', 'Manufacturing', 8), (13, '4', '4141414141', 'Lorem Ipsum Dolor Sit Amet 41', 'Manufacturing', 7), (14, '4', '1212121212', 'Lorem Ipsum Dolor Sit Amet 12', 'Manufacturing', 3), (15, '4', '4343434343', 'Lorem Ipsum Dolor Sit Amet 43', 'Manufacturing', 3), (16, '4', '4545454545', 'Lorem Ipsum Dolor Sit Amet 45', 'Manufacturing', 3), (17, '4', '4646464646', 'Lorem Ipsum Dolor Sit Amet 46', 'Manufacturing', 3), (18, '3', 'KND02S-D0001-0907-A7072', 'Manufacturing Part KND02S', 'Manufacturing', 5), (19, '6', '1212121212', 'Lorem Ipsum Dolor Sit Amet 012', '', 4), (20, '6', '1313131313', 'Lorem Ipsum Dolor Sit Amet 013', '', 4), (21, '6', '1414141414', 'Lorem Ipsum Dolor Sit Amet 014', '', 5), (22, '6', '1515151515', 'Lorem Ipsum Dolor Sit Amet 015', '', 5), (23, '6', 'KND02S-D0001-0907-A7072', 'Lorem Ipsum Dolor Sit Amet 0KND', '', 6), (24, '7', '2323232323', 'Lorem Ipsum Dolor Sit Amet 023', '', 6), (25, '7', '2525252525', 'Lorem Ipsum Dolor Sit Amet 025', '', 4), (26, '7', '2626262626', 'Lorem Ipsum Dolor Sit Amet 026', '', 4), (27, '7', '2727272727', 'Lorem Ipsum Dolor Sit Amet 027', '', 8), (28, '7', '2828282828', 'Lorem Ipsum Dolor Sit Amet 028', '', 8), (29, '8', '3434343434', 'Lorem Ipsum Dolor Sit Amet 034', '', 8), (30, '8', '3535353535', 'Lorem Ipsum Dolor Sit Amet 035', '', 9), (31, '8', '3636363636', 'Lorem Ipsum Dolor Sit Amet 036', '', 2), (32, '8', '3737373737', 'Lorem Ipsum Dolor Sit Amet 037', '', 3), (33, '8', '3838383838', 'Lorem Ipsum Dolor Sit Amet 038', '', 4), (34, '8', '3939393939', 'Lorem Ipsum Dolor Sit Amet 039', '', 2), (35, '8', '3030303030', 'Lorem Ipsum Dolor Sit Amet 030', '', 3), (36, '8', '3131313131', 'Lorem Ipsum Dolor Sit Amet 031', '', 4), (37, '8', '3232323232', 'Lorem Ipsum Dolor Sit Amet 032', '', 3), (38, '5', '1212121212', 'Lorem Ipsum Dolor Sit Amet 12', 'Manufacturing', 5); INSERT INTO `tb_packing_real` (`ID_Packing_Real`, `ID_Packing`, `Content_Number`, `Quantity`, `Input_Date`, `User`) VALUES (1, '1', '1212121212', 2, '2018-08-09', ''), (2, '1', '1313131313', 2, '2018-08-14', ''), (3, '1', '1212121212', 1, '2018-08-16', ''), (4, '1', '1414141414', 1, '2018-08-16', ''), (5, '1', '1414141414', 2, '2018-08-17', ''), (6, '1', '1414141414', 2, '2018-08-13', ''), (7, '4', '1212121212', 3, '2018-08-30', ''), (8, '2', '2626262626', 2, '2018-08-11', ''), (9, '2', '2727272727', 3, '2018-08-15', ''), (10, '2', '2626262626', 4, '2018-08-22', ''), (11, '2', '2727272727', 6, '2018-08-21', ''), (12, '3', '3232323232', 4, '2018-08-22', ''), (13, '3', '3535353535', 2, '2018-08-15', ''), (14, '3', '3535353535', 1, '2018-08-21', ''), (15, '3', '3838383838', 3, '2018-08-29', ''), (16, '1', '3535353535', 2, '2018-08-21', ''), (17, '1', '2727272727', 2, '2018-08-22', ''), (21, '1', 'KND02S-D0001-0907-A7072', 1, '2018-08-08', ''), (22, '1', 'KND02S-D0001-0907-A7072', 1, '2018-08-08', ''), (23, '1', 'KND02S-D0001-0907-A7072', 1, '2018-08-08', ''), (24, '6', 'KND02S-D0001-0907-A7072', 2, '2018-08-09', ''), (25, '3', 'KND02S-D0001-0907-A7072', 2, '2018-08-10', '');
Результат SQL 1
1212121212 Lorem Ipsum Dolor Sit Amet 12 12 1313131313 Lorem Ipsum Dolor Sit Amet 13 2 1414141414 Lorem Ipsum Dolor Sit Amet 14 6 1515151515 Lorem Ipsum Dolor Sit Amet 15 2 2626262626 Lorem Ipsum Dolor Sit Amet 26 9 2727272727 Lorem Ipsum Dolor Sit Amet 27 12 3131313131 Lorem Ipsum Dolor Sit Amet 31 5 3232323232 Lorem Ipsum Dolor Sit Amet 32 4 3535353535 Lorem Ipsum Dolor Sit Amet 35 5 3636363636 Lorem Ipsum Dolor Sit Amet 36 2 3737373737 Lorem Ipsum Dolor Sit Amet 37 5 3838383838 Lorem Ipsum Dolor Sit Amet 38 8 4141414141 Lorem Ipsum Dolor Sit Amet 41 7 4343434343 Lorem Ipsum Dolor Sit Amet 43 3 4545454545 Lorem Ipsum Dolor Sit Amet 45 3 4646464646 Lorem Ipsum Dolor Sit Amet 46 3 KND02S-D0001-0907-A7072 Manufacturing Part KND02S 5
Результат SQL 2
1212121212 Lorem Ipsum Dolor Sit Amet 12 6 1313131313 Lorem Ipsum Dolor Sit Amet 13 2 1414141414 Lorem Ipsum Dolor Sit Amet 14 5 1515151515 Lorem Ipsum Dolor Sit Amet 15 0 2626262626 Lorem Ipsum Dolor Sit Amet 26 6 2727272727 Lorem Ipsum Dolor Sit Amet 27 11 3131313131 Lorem Ipsum Dolor Sit Amet 31 0 3232323232 Lorem Ipsum Dolor Sit Amet 32 4 3535353535 Lorem Ipsum Dolor Sit Amet 35 5 3636363636 Lorem Ipsum Dolor Sit Amet 36 0 3737373737 Lorem Ipsum Dolor Sit Amet 37 0 3838383838 Lorem Ipsum Dolor Sit Amet 38 3 4141414141 Lorem Ipsum Dolor Sit Amet 41 0 4343434343 Lorem Ipsum Dolor Sit Amet 43 0 4545454545 Lorem Ipsum Dolor Sit Amet 45 0 4646464646 Lorem Ipsum Dolor Sit Amet 46 0 KND02S-D0001-0907-A7072 Manufacturing Part KND02S 5
Результат SQL 3
1212121212 Lorem Ipsum Dolor Sit Amet 12 15 6 1212121212 Lorem Ipsum Dolor Sit Amet 12 12 6 1212121212 Lorem Ipsum Dolor Sit Amet 12 9 6 1313131313 Lorem Ipsum Dolor Sit Amet 13 2 2 1414141414 Lorem Ipsum Dolor Sit Amet 14 18 5 1515151515 Lorem Ipsum Dolor Sit Amet 15 2 0 2626262626 Lorem Ipsum Dolor Sit Amet 26 18 6 2727272727 Lorem Ipsum Dolor Sit Amet 27 36 11 3131313131 Lorem Ipsum Dolor Sit Amet 31 5 0 3232323232 Lorem Ipsum Dolor Sit Amet 32 4 4 3535353535 Lorem Ipsum Dolor Sit Amet 35 15 5 3636363636 Lorem Ipsum Dolor Sit Amet 36 2 0 3737373737 Lorem Ipsum Dolor Sit Amet 37 5 0 3838383838 Lorem Ipsum Dolor Sit Amet 38 8 3 4141414141 Lorem Ipsum Dolor Sit Amet 41 7 0 4343434343 Lorem Ipsum Dolor Sit Amet 43 3 0 4545454545 Lorem Ipsum Dolor Sit Amet 45 3 0 4646464646 Lorem Ipsum Dolor Sit Amet 46 3 0 KND02S-D0001-0907-A7072 Manufacturing Part KND02S 25 5
Результат, который я хочу
1212121212 Lorem Ipsum Dolor Sit Amet 12 12 6 1313131313 Lorem Ipsum Dolor Sit Amet 13 2 2 1414141414 Lorem Ipsum Dolor Sit Amet 14 6 5 1515151515 Lorem Ipsum Dolor Sit Amet 15 2 0 2626262626 Lorem Ipsum Dolor Sit Amet 26 9 6 2727272727 Lorem Ipsum Dolor Sit Amet 27 12 11 3131313131 Lorem Ipsum Dolor Sit Amet 31 5 0 3232323232 Lorem Ipsum Dolor Sit Amet 32 4 4 3535353535 Lorem Ipsum Dolor Sit Amet 35 5 5 3636363636 Lorem Ipsum Dolor Sit Amet 36 2 0 3737373737 Lorem Ipsum Dolor Sit Amet 37 5 0 3838383838 Lorem Ipsum Dolor Sit Amet 38 8 3 4141414141 Lorem Ipsum Dolor Sit Amet 41 7 0 4343434343 Lorem Ipsum Dolor Sit Amet 43 3 0 4545454545 Lorem Ipsum Dolor Sit Amet 45 3 0 4646464646 Lorem Ipsum Dolor Sit Amet 46 3 0 KND02S-D0001-0907-A7072 Manufacturing Part KND02S 5 5