if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[item]
GO

CREATE TABLE [dbo].[item] (
[item_id] [int] IDENTITY (1, 1) NOT NULL ,
[org_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[item_number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[item_desc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hs_code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hs_code_eng_desc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hs_code_chs_desc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO 15:01
****************************************************

CREATE TABLE [dbo].[item_wip] (
[wip_id] [int] IDENTITY (1, 1) NOT NULL ,
[org_id] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[tran_date] [datetime] NOT NULL ,
[tran_type] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[assembly_item] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[issued_item] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[item_type] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[job] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[job_status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[issue_qty] [numeric](18, 6) NOT NULL ,
[returns_qty] [numeric](18, 6) NOT NULL ,
[completion_qty] [numeric](18, 6) NOT NULL ,
[scrap_qty] [numeric](18, 6) NOT NULL ,
[uom] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[si] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[bonded] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[b_rm_in_fg] [numeric](18, 6) NOT NULL ,
[nb_rm_in_fg] [numeric](18, 6) NOT NULL
) ON [PRIMARY]
GO
select
issued_item,
item.item_desc
,item.hs_code
,item.hs_code_eng_desc,
Bonded_issue = sum(
  case bonded
    when 'B' then issue_qty
  end
),
Bonded_returns = sum(
  case bonded
    when 'B' then returns_qty
  end
),
Bonded_scrap = sum(
  case bonded
    when 'B' then scrap_qty
  end
),

Nobonded_issue = sum(
  case bonded
    when 'N' then issue_qty
  end
),
Nobonded_returns = sum(
  case bonded
    when 'N' then returns_qty
  end
),
Nobonded_scrap = sum(
  case bonded
    when 'N' then scrap_qty
  end
),

Total_issue = sum(issue_qty),
Total_returns = sum(returns_qty),
Total_scrap = sum(scrap_qty),
Bonded_percent =sum(
  case bonded
    when 'B' then issue_qty
  end
)/sum(issue_qty),
NonBonded_percent =sum(
  case bonded
    when 'N' then issue_qty
  end
)/sum(issue_qty)


from item_wip ,item

where item.item_number=item_wip.issued_item
group by issued_item,item.hs_code
,item.item_desc
,item.hs_code_eng_desc
,item.hs_code
WITH ROLLUP

因为只是按照书上写的，没有考虑这样写能不能行的通
在sql scrip下可以运行

：）

个人意见,仅供参考

你的目的 需求?

讨论什么内容呢？