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

问题描述：

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.

希望有人可以帮助我解决这个问题，真的盯着自己。 p> \ n

我有一个存储用户选择的产品的表，在该表中我存储了时间，拾取和取货时间。所以结构是索引（PRIMARY INT），ordernr（INT），ordertime（TIMESTAMP），productid（INT），拾取（TINYINT），拾取时间（TIMESTAMP）。 p>

现在我想要获取图表的结果集，即日期，订购产品的数量以及按日期分组和分组的产品数量。我现在的问题是我得到了几乎正确的数字，但是拾取数据被计入有序数字中，我似乎无法理解这一点。 p>

  SELECT（拾取时的情况=  '1'然后DATE（order_detail.pickeduptime）否则DATE（order_detail.ordertime）结束）AS x，COUNT（productid）AS y，SUM（拾取时的情况='1'然后1其他0结束）AS z FROM order_detail WHERE productid  ='127'和YEAR（`ordertime`）= YEAR（NOW（））GROUP BY x ORDER BY x asc 
  code>  pre> 
 
 任何帮助都会非常适合。  ： - ） p> 
 
 
 UPDATED： p> 
 
 
  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 
  code>  pre> 
 
  表格如何显示。 p> 
  div>

答

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.

答

Not sure what's your problem exactly, but I think you've got a syntax error : forgot to put a "CASE" after the "END", as in MySQL Reference

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

相关推荐