GROUP BY dbo.Key_item.Style,dbo.Key_item.Key_item_name

order by Style

其结果如图如示：

３、提出数据的二级汇总明细，其代码如下：

SELECT　MAX(dbo.Key_item.Key_item_name) as Item_Name,

SUM(dbo.H_MORVE.QTY_RECVD1) as Quantity,

      dbo.Key_item.Style as Style

FROM dbo.Key_item LEFT OUTER JOIN

      dbo.H_MORVE ON dbo.Key_item.Key_item = dbo.H_MORVE.ITEM

GROUP BY dbo.Key_item.Style

ORDER BY dbo.Key_item.Style asc

其结果如图所示：

       总体的设计就如以上所示，但通过UNION联接的时候出现了新的问题。通过以下例子，就可以看出UNION与UNION ALL的区别

方案一，其代码如下：

SELECT dbo.Key_item.Key_item_name AS Item_Name,

      dbo.H_MORVE.QTY_RECVD1 AS Quantity, dbo.Key_item.Style AS Style

FROM dbo.Key_item LEFT OUTER JOIN

      dbo.H_MORVE ON dbo.Key_item.Key_item = dbo.H_MORVE.ITEM

WHERE (dbo.Key_item.Key_item <> 1)

UNION (

SELECT dbo.Key_item.Key_item_name AS Item_Name,

      SUM(dbo.H_MORVE.QTY_RECVD1) AS Quantity,MAX(dbo.Key_item.Style) AS Style

FROM dbo.Key_item LEFT OUTER JOIN

      dbo.H_MORVE ON dbo.Key_item.Key_item = dbo.H_MORVE.ITEM

WHERE (dbo.Key_item.Key_item <> 1)

GROUP BY dbo.Key_item.Style,dbo.Key_item.Key_item_name

UNION

SELECT MAX(dbo.Key_item.Key_item_name) as Item_Name, SUM(dbo.H_MORVE.QTY_RECVD1) as Quantity,

      dbo.Key_item.Style as Style

FROM dbo.Key_item LEFT OUTER JOIN

      dbo.H_MORVE ON dbo.Key_item.Key_item = dbo.H_MORVE.ITEM

GROUP BY dbo.Key_item.Style)

ORDER BY dbo.Key_item.Style asc

GO

其结果如下：

上一页  [1] [2] [3]  下一页