mysql - mysql中的多重求和

Question

尝试在单个查询中获取结果，但无法获取正确的订单库存总和，如果存在废品，则会显示总和两次。

请参阅下面的查询

SELECT DISTINCT cd.id,i.*,c.category_name, SUM(CASE WHEN s.stock_type = 'f' THEN s.stock END) AS instock, SUM(CASE WHEN s.stock_type = 'w' THEN s.stock END) AS weststock, SUM(cd.qty) AS orderstock, IF(SUM(CASE WHEN s.stock_type = 'f' THEN s.stock END) IS NULL,0, SUM(CASE WHEN s.stock_type = 'f' THEN s.stock END)) - IF(SUM(CASE WHEN s.stock_type = 'w' THEN s.stock END) IS NULL,0, SUM(CASE WHEN s.stock_type = 'w' THEN s.stock END)) - IF(SUM(cd.qty) IS NULL,0, SUM(cd.qty)) AS total
FROM item AS i
JOIN categories AS c ON i.category_id = c.id
LEFT JOIN stock AS s ON i.id=s.item_id
LEFT JOIN manage_godown AS mg ON mg.id = s.godown_id
LEFT JOIN cart_detail AS cd ON i.id=cd.item_id AND cd.cart_id IN (
SELECT ca.id
FROM cart AS ca
WHERE ca.status ='o')
WHERE mg.id=8
GROUP BY i.id
ORDER BY i.id DESC

Table Structure
----------------------


--
-- Table structure for table `admin`
--

CREATE TABLE IF NOT EXISTS `admin` (
  `id` double NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `mobile_no` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `status` enum('a','d') NOT NULL COMMENT 'a= active,d=deactive',
  `type` enum('a','m') NOT NULL DEFAULT 'm',
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `change_password_key` text NOT NULL,
  `isAsigned` enum('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `area`
--

CREATE TABLE IF NOT EXISTS `area` (
  `id` double NOT NULL AUTO_INCREMENT,
  `area_name` varchar(255) NOT NULL,
  `area_status` enum('a','d') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Table structure for table `cart`
--

CREATE TABLE IF NOT EXISTS `cart` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` double NOT NULL,
  `date` datetime NOT NULL,
  `status` enum('c','o') NOT NULL DEFAULT 'c',
  `ship_status` enum('y','n') NOT NULL DEFAULT 'n',
  `shiping_address` text NOT NULL,
  `order_date` datetime NOT NULL,
  `area_id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `contact_number` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `shipping_name` varchar(255) NOT NULL,
  `order_delivery_status` enum('p','d','c') NOT NULL COMMENT 'p=pending,d=deliverd,c=cancle',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `cart_detail`
--

CREATE TABLE IF NOT EXISTS `cart_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cart_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `qty` double NOT NULL,
  `amount` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` double NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  `isDefault` enum('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `item`
--

CREATE TABLE IF NOT EXISTS `item` (
  `id` double NOT NULL AUTO_INCREMENT,
  `category_id` double NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_image` text NOT NULL,
  `price` double NOT NULL,
  `isPopular` enum('Y','N') NOT NULL,
  `item_status` enum('a','d') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

-- --------------------------------------------------------

--
-- Table structure for table `manage_godown`
--

CREATE TABLE IF NOT EXISTS `manage_godown` (
  `id` double NOT NULL AUTO_INCREMENT,
  `godown_name` varchar(150) NOT NULL,
  `address` text NOT NULL,
  `contact_number` varchar(50) NOT NULL,
  `contact_person` varchar(255) NOT NULL,
  `area_id` text NOT NULL,
  `user_id` varchar(100) NOT NULL,
  `godown_status` enum('a','d') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `area`
--

INSERT INTO `area` (`id`, `area_name`, `area_status`) VALUES
(1, 'C.G. Road', 'a'),
(2, 'S.G. Highway', 'a'),
(3, 'Bopal', 'a'),
(4, 'New Wadaj', 'a'),
(5, 'Ranip', 'a'),
(6, 'Bapunagar', 'a'),
(7, 'Gurukul RD', 'a'),
(8, 'Ghatlodia', 'a'),
(9, 'Nehru Nagar', 'a'),
(10, 'Old Wadaj', 'a'),
(11, 'Paldi', 'a'),
(12, 'NaranPura', 'a'),
(13, 'Anand Nagar', 'd'),
(14, 'Shahpur', 'a');

--
-- Dumping data for table `cart`
--

INSERT INTO `cart` (`id`, `user_id`, `date`, `status`, `ship_status`, `shiping_address`, `order_date`, `area_id`, `user_name`, `email`, `contact_number`, `address`, `shipping_name`, `order_delivery_status`) VALUES
(1, 1, '2015-03-09 20:30:36', 'o', 'n', 'roam', '2015-03-09 21:14:45', 7, 'account1', 'account1@superrito.com', '123456', 'roam', 'account1', 'p'),
(2, 1425936687, '2015-03-09 21:31:27', 'c', 'n', '', '0000-00-00 00:00:00', 0, '', '', '', '', '', 'p'),
(3, 1426012764, '2015-03-10 18:39:24', 'c', 'n', '', '0000-00-00 00:00:00', 0, '', '', '', '', '', 'p');

--
-- Dumping data for table `cart_detail`
--

INSERT INTO `cart_detail` (`id`, `cart_id`, `item_id`, `qty`, `amount`) VALUES
(1, 1, 1, 3, 80),
(2, 2, 1, 0.25, 80),
(3, 3, 1, 0.5, 80),
(4, 3, 2, 0.25, 45);

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `category_name`, `isDefault`) VALUES
(1, 'vegitables', 'y'),
(2, 'fruits', 'y');

--
-- Dumping data for table `item`
--

INSERT INTO `item` (`id`, `category_id`, `item_name`, `item_image`, `price`, `isPopular`, `item_status`) VALUES
(1, 1, 'Tomato', '210fc803a958749e7b2a55c32c744f13.png', 80, 'Y', 'a'),
(2, 1, 'Potato', 'c244be2eab10bc46465d5b36448ba68b.jpg', 45, 'N', 'a'),
(3, 2, 'Cabbige', '05423c579cc99709923273c5222c4661.jpg', 120, 'Y', 'a');

--
-- Dumping data for table `manage_godown`
--

INSERT INTO `manage_godown` (`id`, `godown_name`, `address`, `contact_number`, `contact_person`, `area_id`, `user_id`, `godown_status`) VALUES
(8, 'Gurukul', 'Gurukul RD', '12457894', 'Salim', '7', '16', 'a'),
(9, 'Godown2', 'Godown2', '123456798', 'Samln', '12', '17', 'a');

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `email`, `user_name`, `address`, `shiping_address`, `mobile_no`, `password`, `isVeryfied`, `created_date`, `email_verification`, `change_password_key`, `area_id`, `pincode`, `user_status`) VALUES
(1, 'account1@superrito.com', 'account1', 'roam', 'roam', '123456', '4b111bc4e9e96cd1d18d0359fdb94629', 'n', '2015-03-09 08:53:00', '', '', 0, 0, 'a'),
(2, 'tripathi_hhh@yahoo.com', 'hitesh tripathi', '', '', '9033913397', 'e10adc3949ba59abbe56e057f20f883e', 'n', '2015-03-10 06:31:23', '17a962a2eee74445747a3e194da6c556', 'be45b6a9362c65217ec88821b648a67f', 0, 0, 'a');

我只下了番茄 3KG 的订单，但是我添加了浪费的元素 3 次，所以上述查询的总和在订单库存中给出了 12KG 的结果

Answer 1

使用 COALESCE 而不是 IF ... IS NULL 使其更具可读性...我正在等待您对 @EdwinKrause 问题的回答以提供帮助其余的。

SELECT DISTINCT cd.id,i.*,c.category_name, 
  SUM(CASE WHEN s.stock_type = 'f' THEN s.stock END) AS instock, 
  SUM(CASE WHEN s.stock_type = 'w' THEN s.stock END) AS weststock, 
  SUM(cd.qty) AS orderstock, 
  COALESCE(  SUM(CASE WHEN s.stock_type = 'f' THEN s.stock END), 0) - 
    COALESCE(SUM(CASE WHEN s.stock_type = 'w' THEN s.stock END), 0) - 
    COALESCE(SUM(cd.qty), 0) AS total
FROM item AS i
JOIN categories AS c ON i.category_id = c.id
LEFT JOIN stock AS s ON i.id=s.item_id
LEFT JOIN manage_godown AS mg ON mg.id = s.godown_id
LEFT JOIN cart_detail AS cd ON i.id=cd.item_id AND cd.cart_id IN (
SELECT ca.id
FROM cart AS ca
WHERE ca.status ='o')
WHERE mg.id=2
GROUP BY i.id
ORDER BY i.id DESC