MySQL根据两个时间戳列获取结果集

Question

Hope someone can help me with this one, is really staring myself blind on it.

I have a table which stores products that users have selected and in that table I store ordertime and pickedup and pickuptime. So structur is index(PRIMARY INT), ordernr(INT), ordertime(TIMESTAMP), productid(INT), pickedup(TINYINT), pickeduptime(TIMESTAMP).

Now I would love to get a result set for a graph, that says, Date, count of ordered products and count of products pickedup on, group and ordered by dates. My problem now is that I get almost right numbers, but the pickedup are counted into the ordered and I cant seem to get my head around this.

SELECT (case when pickedup = '1' then DATE(order_detail.pickeduptime) else DATE(order_detail.ordertime) end) AS x,COUNT(productid) AS y,SUM(case when pickedup = '1' then 1 else 0 end) AS z FROM order_detail WHERE productid = '127' AND YEAR(`ordertime`) = YEAR(NOW()) GROUP BY x ORDER BY x asc

Any help would be much appriciated. :-)

UPDATED:

1   index   int(11)         AUTO_INCREMENT  Primary
2   ordernr int(11)         
3   productid   int(11)     
4   price   int(11)     
5   ordertime   timestamp   CURRENT_TIMESTAMP
6   pickedup    tinyint(4)  0
7   pickeduptime    timestamp   0000-00-00 00:00:00

How table looks.

Answer 1

The first thing that might help is proper formatting of the SQL:

SELECT 
  (CASE 
     WHEN pickedup = '1' THEN DATE(pickeduptime) 
     ELSE DATE(ordertime) 
   END) AS x,
  COUNT(productid) AS y,
  SUM(CASE 
        WHEN pickedup = '1' THEN 1 
        ELSE 0 
      END) AS z 
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW()) 
GROUP BY x 
ORDER BY x ASC

Now I need to find out what you're trying to do. Here is where the real problems start. It is unclear whether you reuse rows in your table for the pickup or not. I'm sorry, I cannot help you. You SQL is very weird though. For something as simple as this you would not need to use two CASE commands.

I also cannot make sense of your SQL, grouping by a date that comes from two columns?

After careful reading I think I start to understand what you want. You want to see how many products are ordered and picked up on each date. I would not try to do that with one SQL command, but rather use two. Two lines, two SQL commands. :-)

First a simple one: How many products where ordered, grouped and sorted by date:

SELECT 
  DATE(ordertime) as orderdate,
  COUNT(productid) AS quantity
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW()) 
GROUP BY orderdate 
ORDER BY orderdate ASC

Now the ordered products that are not yet picked up:

SELECT 
  DATE(ordertime) as orderdate,
  COUNT(productid) AS quantity
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW()) AND
  pickedup != '1'
GROUP BY orderdate 
ORDER BY orderdate ASC

and now the products that have been picked up, still sorted on order date:

SELECT 
  DATE(ordertime) as orderdate,
  COUNT(productid) AS quantity
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW()) AND
  pickedup = '1'
GROUP BY orderdate 
ORDER BY orderdate ASC

And here's the same sorted on pickup date:

SELECT 
  DATE(pickeduptime) as pickedupdate,
  COUNT(productid) AS quantity
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW()) AND
  pickedup = '1'
GROUP BY pickedupdate
ORDER BY pickedupdate ASC

Now you can use any of these to draw a line in your graph.

If you insist on one query you could use this one:

SELECT 
  DATE(ordertime) as orderdate,
  SUM(pickedup) AS pickedup_quantity,
  SUM(1-pickedup) AS not_pickedup_quantity
FROM 
  order_detail 
WHERE 
  productid = '127' AND 
  YEAR(ordertime) = YEAR(NOW())  
GROUP BY orderdate 
ORDER BY orderdate ASC

Note that everything has to be sorted by one date.