如何在多表上选择DISTINCT SUM [英] How do I SELECT DISTINCT SUM on multi table

查看：101
本文介绍了如何在多表上选择DISTINCT SUM的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何在具有多个条件的多表中选择DISTINCT SUM（IN（SELECT ..））。我尝试了代码来显示每个表的数据，代码可以显示相应的结果，但是当我将代码组合显示数据2表时结果不合适。



我尝试了什么：



此代码，当我尝试



SQL1

How do i select DISTINCT SUM in multi table with multi condition (IN (SELECT..)). I tried the code to display data per table, the code can display the corresponding results, but when I combine the code to display data 2 tables the results are not appropriate.

What I have tried:

this code when i try

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



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



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数据表



Here's my database and dummping data table

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 Result



SQL 1 Result

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 Result



SQL 2 Result

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 Result



SQL 3 Result

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





Result i want



Result i want

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

推荐答案

-- I think you can try this below query


select PartNumber,PartName , sum(Quantity) as Quantity , sum(Quantity2) as Quantity2 
from (
		SELECT distinct p.Content_Number AS PartNumber, p.Content_Name AS PartName,isnull( p.Quantity,0 )AS Quantity  
		,isnull(r.Quantity,0) as Quantity2
		FROM tb_packing_plan p
		join  tb_packing  as  Q on Q.ID_Packing=p.ID_Packing
		left join (
					select distinct r.ID_Packing , r. Content_Number, sum(r.Quantity) as Quantity from tb_packing_real r 
					join  tb_packing as  Q on Q.ID_Packing=r.ID_Packing
					 WHERE Q.ID_Project_Item = 1
					group by r.ID_Packing , r. Content_Number
					) R on r.Content_Number=p.Content_Number and r.ID_Packing=p.ID_Packing
		WHERE Q.ID_Project_Item = 1
  ) P
GROUP BY p.PartNumber,p.PartName

ORDER BY p.PartNumber;


这篇关于如何在多表上选择DISTINCT SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆