mysql - 显示特定日期的数据？

Question

I am trying to make a reporting system where I need to display report for each date.

These is my table schema for selected_items This is stock_list

I am using php in the back-end and java in the front end to display the data. I tried a couple of queries to get the desired output but so far I am not able to get it.These are some of the queries i used.

SELECT
   COALESCE(stock_list.date, selected_items.date) AS date,
   SUM( stock_list.qty ) AS StockSum,
   SUM( stock_list.weight ) AS Stockweight,
   COUNT( selected_items.barcode ) AS BilledItems,
   SUM( selected_items.weight ) AS Billedweight
FROM stock_list join selected_items
ON stock_list.date = selected_items.date
GROUP BY COALESCE(stock_list.date, selected_items.date)
ORDER BY COALESCE(stock_list.date, selected_items.date);

This gives me the first five columns but the output gives me wrong values. Then I also tried Union.

SELECT SUM( qty ) AS StockSum, SUM( weight ) AS Stockweight
FROM  `stock_list` 
WHERE DATE LIKE  '08-Jan-2016'
UNION SELECT COUNT( barcode ) AS BilledItems, SUM( weight ) AS Billedweight
FROM  `selected_items` 
WHERE DATE LIKE  '08-Jan-2016'
UNION SELECT SUM( qty ) AS TotalStock, SUM( weight ) AS TotalWeight
FROM  `stock_list`;

Here I get the correct values for four columns but the problem is the >result is displayed in two columns when I would like it to be in 4 columns. Can anyone guide me please I have figured the java part of it but I am not good at php and mysql. Thank you

Answer 1

不幸的是，当我尝试执行此查询时，SQL Fiddle 崩溃了

SELECT sl.date AS date, B.qtySum AS StockSum, B.weightSum AS Stockweight,
C.barcodeCount AS BilledItems, C.weightSum AS Billedweight
FROM stock_list sl 
JOIN (SELECT SUM(qty) as qtySum, SUM(weight) as weightSum
      FROM STOCK_LIST GROUP BY date) AS B
ON B.date = sl.date
JOIN (SELECT SUM (weight) AS weightSum, COUNT(barcode) AS barcodeCount
      FROM SELECTED_ITEMS GROUP BY date) AS C
ON C.date = sl.date;

As it was tried here .连接的问题是行将被多次连接，因此总和会出错。例如，您有四行从第二个表连接，因此总和应该高四倍。使用子查询，您可以避免这个问题，因为您在加入变量之前对变量进行计数和求和，因此数字应该合适。唉，我无法运行查询，所以我不能 100% 确定它是否有效，但它应该是正确的方法。