版本2005及以上. CREATE TABLE po_dt1 ( po_no VARCHAR(20), item VARCHAR(10), description VARCHAR(20), po_qty INT ) INSERT INTO po_dt1 SELECT 'PO-201012001', 'C003', '猪腰', 100 UNION ALL SELECT 'PO-201012001', 'C004', '白菜', 100 UNION ALL SELECT 'PO-201012001', 'C005', '菜心', 100 CREATE TABLE food ( item VARCHAR(10), description VARCHAR(20), type_code VARCHAR(10) ) INSERT INTO food SELECT 'C003', '猪腰', 'X001' UNION ALL SELECT 'C004', '白菜', 'X002' UNION ALL SELECT 'C005', '菜心', 'X002' CREATE TABLE foodtype ( type_code VARCHAR(10), type_desc VARCHAR(20) ) INSERT INTO foodtype SELECT 'X001', '肉类' UNION ALL SELECT 'X002', '蔬菜类' CREATE TABLE po_item ( po_no VARCHAR(20) ) INSERT INTO po_item SELECT 'PO-201012001' UNION ALL SELECT 'PO-201012002' GO CREATE PROCEDURE dbo.px @str VARCHAR(20) AS BEGIN ;WITH cte AS ( SELECT 类别=d.type_desc,序号=ROW_NUMBER() OVER (PARTITION BY d.type_desc ORDER BY c.item+c.description ) , 规格=c.item+c.description,数量=b.po_qty FROM po_item a LEFT JOIN po_dt1 b ON a.po_no=b.po_no LEFT JOIN food c ON b.item=c.item LEFT JOIN foodtype d ON c.type_code=d.type_code WHERE a.po_no=@str ) SELECT 类别=CASE WHEN 序号=1 THEN 类别 ELSE '' END ,序号,规格,数量 FROM cte END; EXEC px 'PO-201012001' /* 类别 序号 规格 数量 -------------------- -------------------- ------------------------------ ----------- 肉类 1 C003猪腰 100 蔬菜类 1 C004白菜 100 2 C005菜心 100(3 行受影响) */
create table po_dtl(po_no nvarchar(15),item nvarchar(10),[description] nvarchar(10),po_qty int) insert into po_dtl select 'PO-201012001','C003','猪腰',100 insert into po_dtl select 'PO-201012001','C004','白菜',100 insert into po_dtl select 'PO-201012001','C005','菜心',100 create table po_info(item nvarchar(10),[description] nvarchar(10),type_code nvarchar(10)) insert into po_info select 'C003','猪腰','X001' insert into po_info select 'C004','白菜','X002' insert into po_info select 'C005','菜心','X002' create table po_type(type_code nvarchar(10),type_desc nvarchar(10)) insert into po_type select 'X001','肉类' insert into po_type select 'X002','蔬菜类' go declare @po_no nvarchar(15) set @po_no='PO-201012001' select (case when c.ranknum=1 then c.type_desc else '' end)as 类别,c.ranknum as 序号,c.item+c.d as 规格,d.po_qty as 数量 from( select rank() over(partition by b.type_code order by b.item)as ranknum,a.type_desc,b.item,b.[description]as d from po_type a inner join po_info b on a.type_code=b.type_code )c inner join po_dtl d on c.item=d.item go drop table po_dtl,po_info,po_type /* 类别 序号 规格 数量 ---------- -------------------- -------------------- ----------- 肉类 1 C003猪腰 100 蔬菜类 1 C004白菜 100 2 C005菜心 100(3 行受影响)*/
呵呵,要的是存储过程: create table po_dtl(po_no nvarchar(15),item nvarchar(10),[description] nvarchar(10),po_qty int) insert into po_dtl select 'PO-201012001','C003','猪腰',100 insert into po_dtl select 'PO-201012001','C004','白菜',100 insert into po_dtl select 'PO-201012001','C005','菜心',100 create table po_info(item nvarchar(10),[description] nvarchar(10),type_code nvarchar(10)) insert into po_info select 'C003','猪腰','X001' insert into po_info select 'C004','白菜','X002' insert into po_info select 'C005','菜心','X002' create table po_type(type_code nvarchar(10),type_desc nvarchar(10)) insert into po_type select 'X001','肉类' insert into po_type select 'X002','蔬菜类' go create procedure getqty @po_no nvarchar(15) as begin select (case when c.ranknum=1 then c.type_desc else '' end)as 类别,c.ranknum as 序号,c.item+c.d as 规格,d.po_qty as 数量 from( select rank() over(partition by b.type_code order by b.item)as ranknum,a.type_desc,b.item,b.[description]as d from po_type a inner join po_info b on a.type_code=b.type_code )c inner join po_dtl d on c.item=d.item end go exec getqty 'PO-201012001' go drop table po_dtl,po_info,po_type drop procedure getqty /* 类别 序号 规格 数量 ---------- -------------------- -------------------- ----------- 肉类 1 C003猪腰 100 蔬菜类 1 C004白菜 100 2 C005菜心 100(3 行受影响)*/
create table po_dtl ( po_no nvarchar(20), item nvarchar(4), description nvarchar(10), po_qty int )insert into po_dtl values('201012001','C003 ','猪腰',100) insert into po_dtl values('201012001','C004 ','白菜',100) insert into po_dtl values('201012001','C005 ','菜心',100)drop table po_zl create table po_zl ( item nvarchar(4), description nvarchar(10), type_code nvarchar(4) )insert into po_zl values('C003 ','猪腰','X001') insert into po_zl values('C004 ','白菜','X002') insert into po_zl values('C005 ','菜心','X002') create table po_type ( type_code nvarchar(4), type_desc nvarchar(10) )insert into po_type values('X001','肉类') insert into po_type values('X002','蔬菜类')create table #table(type nvarchar(10),xh int,descrip nvarchar(20),qty int)--临时表declare @type nvarchar(10);DECLARE myCursor CURSOR FOR --游标 select type_desc from po_typeOPEN myCursor;FETCH NEXT FROM myCursor into @typeWHILE @@FETCH_STATUS = 0BEGIN print @type; insert into #table select type_desc,ROW_NUMBER() over(order by type_desc) as 序号, po_dtl.description,po_qty from po_dtl,po_zl,po_type where po_dtl.item=po_zl.item and po_zl.type_code=po_type.type_code and type_desc=@type FETCH NEXT FROM myCursor into @type END;close myCursor;DEALLOCATE myCursor;select * from #tabledrop table #table结果如下:肉类 1 猪腰 100 蔬菜类 1 白菜 100 蔬菜类 2 菜心 100
到另一个类别又重新1,2,3,4,5.。下去这样。类别 序号 规格 数量
肉类 1 C003猪腰 100
蔬菜类 1 C004白菜 100
2 C005菜心 100
肉类 1 C003猪腰 100
蔬菜类 1 C004白菜 100
2 C005菜心 100
CREATE TABLE po_dt1
(
po_no VARCHAR(20),
item VARCHAR(10),
description VARCHAR(20),
po_qty INT
)
INSERT INTO po_dt1
SELECT 'PO-201012001', 'C003', '猪腰', 100 UNION ALL SELECT
'PO-201012001', 'C004', '白菜', 100 UNION ALL SELECT
'PO-201012001', 'C005', '菜心', 100
CREATE TABLE food
(
item VARCHAR(10),
description VARCHAR(20),
type_code VARCHAR(10)
)
INSERT INTO food
SELECT
'C003', '猪腰', 'X001' UNION ALL SELECT
'C004', '白菜', 'X002' UNION ALL SELECT
'C005', '菜心', 'X002'
CREATE TABLE foodtype
(
type_code VARCHAR(10),
type_desc VARCHAR(20)
)
INSERT INTO foodtype
SELECT
'X001', '肉类' UNION ALL SELECT
'X002', '蔬菜类'
CREATE TABLE po_item
(
po_no VARCHAR(20)
)
INSERT INTO po_item
SELECT
'PO-201012001' UNION ALL SELECT
'PO-201012002'
GO
CREATE PROCEDURE dbo.px
@str VARCHAR(20)
AS
BEGIN
;WITH cte AS
(
SELECT 类别=d.type_desc,序号=ROW_NUMBER() OVER (PARTITION BY d.type_desc ORDER BY c.item+c.description ) ,
规格=c.item+c.description,数量=b.po_qty
FROM po_item a LEFT JOIN po_dt1 b ON a.po_no=b.po_no LEFT JOIN food c ON b.item=c.item
LEFT JOIN foodtype d ON c.type_code=d.type_code
WHERE a.po_no=@str
)
SELECT 类别=CASE WHEN 序号=1 THEN 类别 ELSE '' END ,序号,规格,数量 FROM cte
END;
EXEC px 'PO-201012001'
/*
类别 序号 规格 数量
-------------------- -------------------- ------------------------------ -----------
肉类 1 C003猪腰 100
蔬菜类 1 C004白菜 100
2 C005菜心 100(3 行受影响)
*/
insert into po_dtl select 'PO-201012001','C003','猪腰',100
insert into po_dtl select 'PO-201012001','C004','白菜',100
insert into po_dtl select 'PO-201012001','C005','菜心',100
create table po_info(item nvarchar(10),[description] nvarchar(10),type_code nvarchar(10))
insert into po_info select 'C003','猪腰','X001'
insert into po_info select 'C004','白菜','X002'
insert into po_info select 'C005','菜心','X002'
create table po_type(type_code nvarchar(10),type_desc nvarchar(10))
insert into po_type select 'X001','肉类'
insert into po_type select 'X002','蔬菜类'
go
declare @po_no nvarchar(15)
set @po_no='PO-201012001'
select (case when c.ranknum=1 then c.type_desc else '' end)as 类别,c.ranknum as 序号,c.item+c.d as 规格,d.po_qty as 数量
from(
select rank() over(partition by b.type_code order by b.item)as ranknum,a.type_desc,b.item,b.[description]as d
from po_type a inner join po_info b on a.type_code=b.type_code
)c inner join po_dtl d on c.item=d.item
go
drop table po_dtl,po_info,po_type
/*
类别 序号 规格 数量
---------- -------------------- -------------------- -----------
肉类 1 C003猪腰 100
蔬菜类 1 C004白菜 100
2 C005菜心 100(3 行受影响)*/
create table po_dtl(po_no nvarchar(15),item nvarchar(10),[description] nvarchar(10),po_qty int)
insert into po_dtl select 'PO-201012001','C003','猪腰',100
insert into po_dtl select 'PO-201012001','C004','白菜',100
insert into po_dtl select 'PO-201012001','C005','菜心',100
create table po_info(item nvarchar(10),[description] nvarchar(10),type_code nvarchar(10))
insert into po_info select 'C003','猪腰','X001'
insert into po_info select 'C004','白菜','X002'
insert into po_info select 'C005','菜心','X002'
create table po_type(type_code nvarchar(10),type_desc nvarchar(10))
insert into po_type select 'X001','肉类'
insert into po_type select 'X002','蔬菜类'
go
create procedure getqty
@po_no nvarchar(15)
as
begin
select (case when c.ranknum=1 then c.type_desc else '' end)as 类别,c.ranknum as 序号,c.item+c.d as 规格,d.po_qty as 数量
from(
select rank() over(partition by b.type_code order by b.item)as ranknum,a.type_desc,b.item,b.[description]as d
from po_type a inner join po_info b on a.type_code=b.type_code
)c inner join po_dtl d on c.item=d.item
end
go
exec getqty 'PO-201012001'
go
drop table po_dtl,po_info,po_type
drop procedure getqty
/*
类别 序号 规格 数量
---------- -------------------- -------------------- -----------
肉类 1 C003猪腰 100
蔬菜类 1 C004白菜 100
2 C005菜心 100(3 行受影响)*/
create table po_dtl
(
po_no nvarchar(20),
item nvarchar(4),
description nvarchar(10),
po_qty int
)insert into po_dtl values('201012001','C003 ','猪腰',100)
insert into po_dtl values('201012001','C004 ','白菜',100)
insert into po_dtl values('201012001','C005 ','菜心',100)drop table po_zl
create table po_zl
(
item nvarchar(4),
description nvarchar(10),
type_code nvarchar(4)
)insert into po_zl values('C003 ','猪腰','X001')
insert into po_zl values('C004 ','白菜','X002')
insert into po_zl values('C005 ','菜心','X002')
create table po_type
(
type_code nvarchar(4),
type_desc nvarchar(10)
)insert into po_type values('X001','肉类')
insert into po_type values('X002','蔬菜类')create table #table(type nvarchar(10),xh int,descrip nvarchar(20),qty int)--临时表declare @type nvarchar(10);DECLARE myCursor CURSOR FOR --游标
select type_desc from po_typeOPEN myCursor;FETCH NEXT FROM myCursor into @typeWHILE @@FETCH_STATUS = 0BEGIN
print @type;
insert into #table select type_desc,ROW_NUMBER() over(order by type_desc) as 序号,
po_dtl.description,po_qty
from po_dtl,po_zl,po_type
where po_dtl.item=po_zl.item
and po_zl.type_code=po_type.type_code
and type_desc=@type
FETCH NEXT FROM myCursor into @type
END;close myCursor;DEALLOCATE myCursor;select * from #tabledrop table #table结果如下:肉类 1 猪腰 100
蔬菜类 1 白菜 100
蔬菜类 2 菜心 100