Question

Im 目前学习,有一些问题,是如何做以下工作:

表:

Planned Order
    id  Item    Date
    0   1   2011-01-24
    1   1   2011-01-26
    2   1   2011-01-28
    3   2   2011-01-24
    4   3   2011-01-27

Customer Order
    id  Item    Date
    4   2   2011-01-25
    3   2   2011-01-24
    2   2   2011-01-24
    1   1   2011-01-26
    0   1   2011-01-24

我不想提出以下问题:

Item  Category          2011-01-24      2011-01-25   2011-01-26   
1     Customer_Order    1               NULL         1      
1     Planned_Order     1               NULL         1
2     Customer_Order    2               NULL         NULL
2     Planned_Order     1               NULL         NULL
3     Planned_Order     NULL            NULL         NULL

Where the count under the date is how many times the item was ordered that day.

即便有原 code法,还是有可能这样做? 我认识到,可以通过其他语文做到这一点,例如 per,操纵数据,并通过多种数据基获取,但我希望从原始数字中直接这样做。

我找不到一个指挥(raw)来从一栏中转换一个电梯,将其转换成行。我是如何问及上述日期和每一日期的顺序。

Answer 1

在Sql服务器(2005/2008年)中,经营人必须做什么(PIVOT)。

SELECT Item,Category,[2011-01-24], [2011-01-25], [2011-01-26]
FROM
(SELECT Item,  Planned_Order  Category, Date
 FROM   [Planned Order]
 union all
 SELECT Item,  Customer_Order , Date
 FROM   [Customer Order]
 ) AS SourceTable
PIVOT
(
COUNT(*)
FOR Date IN ([2011-01-24], [2011-01-25], [2011-01-26])
) AS PivotTable;

Answer 2

2. 可行的办法:

select item,  Customer_Order  Category,
  COUNT(case when Date= 2011-01-24  then 1 else 0 end) as `2010-01-24`,
  COUNT(case when Date= 2011-01-25  then 1 else 0 end) as `2010-01-25`,
  COUNT(case when Date= 2011-01-26  then 1 else 0 end) as `2010-01-26`
from customer_order
GROUP BY item
union all
select item,  Planned_Order  Category,
  COUNT(case when Date= 2011-01-24  then 1 else 0 end) as `2010-01-24`,
  COUNT(case when Date= 2011-01-25  then 1 else 0 end) as `2010-01-25`,
  COUNT(case when Date= 2011-01-26  then 1 else 0 end) as `2010-01-26`
from planned_order
GROUP BY item
ORDER BY item, Category

The problem is that you need to know all the dates you want to display up front. The only DBMS (I know of) to support a dynamic list of columns is Oracle. Other DBMS will require you to generate dynamic SQL to create the CASE statements, one for each distinct date encountered.

唯一不可运输的部分是,由于使用日期作为外地名称,你需要

`2010-01-24` backticks here for MySQL
"2010-01-24" or [2010-01-24] for SQL Server
"2010-01-24" for Oracle
etc

友情链接