SQL Server 经典案例

WITH [ta] ([商品编号], [批次号], [库存数量]) AS (
SELECT '', '', 200 UNION ALL
SELECT '', '', 50 UNION ALL
SELECT '', '', 30 UNION ALL
SELECT '', '', 200
),[tb] ([商品编号], [订货数量]) AS (
SELECT '', 60 UNION ALL
SELECT '', 20
)
SELECT a.*, 出货数量=a.库存数量-CASE WHEN SUM(c.库存数量)-b.订货数量<0 THEN 0 ELSE SUM(c.库存数量)-b.订货数量 END
FROM ta a
JOIN tb b ON a.商品编号=b.商品编号
JOIN ta c ON a.商品编号=c.商品编号 AND c.批次号<=a.批次号
GROUP BY a.商品编号, a.批次号, a.库存数量, b.订货数量
HAVING a.库存数量>SUM(c.库存数量)-b.订货数量;

CREATE TABLE 订单表
(
订单号 CHAR(8),
下单日期 DATE,
产品 CHAR(15),
订单数量 INT,
订单余数 INT
);
GO
INSERT INTO 订单表 VALUES
('MD180090','2018-04-03','BH14-084-099-05',600,248),
('MD180091','2018-04-03','BH14-084-099-18',700,295),
('MD180147','2018-06-02','BH14-084-099-18',400,400),
('MD180148','2018-06-02','BH14-084-099-05',500,500);
GOCREATE TABLE 销售表
(
销售日期 DATE,
销售单号 CHAR(10),
产品 CHAR(15),
销售数量 INT
);
GO
INSERT INTO 销售表 VALUES
('2018-07-09','','BH14-084-099-05',112),
('2018-07-09','','BH14-084-099-18',102),
('2018-07-09','','BH14-084-099-05',112),
('2018-07-09','','BH14-084-099-18',102),
('2018-07-09','','BH14-084-099-05',186),
('2018-07-09','','BH14-084-099-18',170),
('2018-07-09','','BH14-084-099-05',92),
('2018-07-09','','BH14-084-099-18',84),
('2018-07-09','','BH14-084-099-05',118),
('2018-07-09','','BH14-084-099-18',107);
GO
----------------------------------------
--订单表是公司的所有订单，因生产日期不同，生产成本可能不同
--销售表是5家经销店的销售记录，销售单价可能不同
--现在需要将各个销售单号归到订单号中去，以计算利润
--运行结果参考如下：
----------------------------------------
/*
销售日期 订单号 销售单号 产品 数量
2018-7-9 MD180090 4212345704 BH14-084-099-05 112
2018-7-9 MD180090 4212345710 BH14-084-099-05 112
2018-7-9 MD180090 4212345716 BH14-084-099-05 24
2018-7-9 MD180091 4212345704 BH14-084-099-18 102
2018-7-9 MD180091 4212345710 BH14-084-099-18 102
2018-7-9 MD180091 4212345716 BH14-084-099-18 91
2018-7-9 MD180147 4212345716 BH14-084-099-18 79
2018-7-9 MD180147 4212345722 BH14-084-099-18 84
2018-7-9 MD180147 4212345728 BH14-084-099-18 107
2018-7-9 MD180148 4212345716 BH14-084-099-05 162
2018-7-9 MD180148 4212345722 BH14-084-099-05 92
2018-7-9 MD180148 4212345728 BH14-084-099-05 118
*/IF object_id('tempdb..#ordersDis') is not null
Begin
DROP TABLE #ordersDis
End
if object_id('tempdb..#salesTmp') is not null
Begin
DROP TABLE #salesTmp
EndSELECT t2.[销售日期],t1.[订单号],t2.[销售单号],t1.[产品],t1.[订单数量] [数量]
INTO #ordersDis
FROM [订单表] t1 inner Join [销售表] t2 on t1.[产品] = t2.[产品] and 1 > 2 SELECT [销售日期],[销售单号],[产品],[销售数量] INTO #salesTmp FROM [销售表];DECLARE @orderNO NVARCHAR(32);
DECLARE @orderGoods NVARCHAR(32);
DECLARE @orderLastGoods NVARCHAR(32);
DECLARE @orderQty INT;
DECLARE @orderDate DATE;DECLARE @saleDate DATE;
DECLARE @saleNO NVARCHAR(32);
DECLARE @saleGoods NVARCHAR(32);
DECLARE @saleQty INT;set @orderQty = 0;
set @saleQty = 0;
set @orderLastGoods = '';DECLARE orderCursor CURSOR FOR SELECT [订单号],[产品],[订单余数],[下单日期] FROM [订单表] order by [下单日期] ASC;
OPEN orderCursor
FETCH NEXT FROM orderCursor INTO @orderNO,@orderGoods,@orderQty,@orderDate
WHILE (@@fetch_status=0 Or @orderQty > 0)
Begin
Begin
DECLARE saleCursor CURSOR FOR SELECT [销售日期],[销售单号],[产品],[销售数量] FROM #salesTmp
WHERE [产品]= @orderGoods and [销售数量] > 0 ORDER BY [销售日期] ASC;
SET @orderLastGoods = @orderGoods; OPEN saleCursor
FETCH NEXT FROM saleCursor into @saleDate,@saleNO,@saleGoods,@saleQty
DECLARE @hasSaleDataTag INT;
SET @hasSaleDataTag = 0; while (@@fetch_status=0)
Begin
SET @hasSaleDataTag = 1;
If @saleQty > 0 and @orderQty > 0
Begin
if @orderQty >= @saleQty
Begin
Insert into #ordersDis([销售日期],[订单号],[销售单号],[产品],[数量]) values(@saleDate,@orderNO,@saleNO,@orderGoods,@saleQty);
set @orderQty = @orderQty - @saleQty;
set @saleQty = 0;
--可添加更新减少订单表订单余量的语句，或是更新销售单已分配处理销售数量的语句
UPDATE #salesTmp SET [销售数量] = @saleQty WHERE [销售单号] = @saleNO and [产品] = @orderGoods;
End
else if @orderQty < @saleQty
Begin
Insert into #ordersDis([销售日期],[订单号],[销售单号],[产品],[数量]) values(@saleDate,@orderNO,@saleNO,@orderGoods,@orderQty);
set @saleQty = @saleQty - @orderQty;
set @orderQty = 0;
--可添加更新减少订单表订单余量的语句，或是更新销售单已分配处理销售数量的语句
UPDATE #salesTmp SET [销售数量] = @saleQty WHERE [销售单号] = @saleNO and [产品] = @orderGoods;
End
End
if @saleQty <= 0
FETCH NEXT FROM saleCursor INTO @saleDate,@saleNO,@saleGoods,@saleQty
if @orderQty <= 0
break;
End IF @hasSaleDataTag = 0
BEGIN
SET @orderQty = 0;
END CLOSE saleCursor
DEALLOCATE saleCursor
End If @orderQty <= 0
Begin
FETCH NEXT FROM orderCursor INTO @orderNO,@orderGoods,@orderQty,@orderDate;
End
End
CLOSE orderCursor
DEALLOCATE orderCursor SELECT [销售日期],[订单号],[销售单号],[产品],[数量] FROM #ordersDis;

--测试数据
if not object_id('outtable') is null
drop table outtable
Go
Create table outtable([bill] nvarchar(20),[goodscode] varchar(100),mount int ,prodate date)
Insert outtable
select '单号1','',100,'2018/5/1'
union all
select '单号1','',200,'2018/5/1'
union all
select '单号2','',300,'2018/5/1'
union all
select '单号2','',200,'2018/5/1'
union all
select '单号3','',150,'2018/5/1'
union all
select '单号3','',200,'2018/5/1'
union all
select '单号4','',250,'2018/5/1'
union all
select '单号4','',350,'2018/5/1'
select * from outtableif not object_id('intable') is null
drop table intable
Go
Create table intable([goodscode] varchar(100),mount int ,prodate date)
Insert intable
select '',200,'2018/5/1'
union all
select '',300,'2018/4/1'
union all
select '',700,'2018/5/1'
union all
select '',50,'2018/4/1'
union all
select '',200,'2018/5/1'
union all
select '',300,'2018/4/1' select * from intable
--想要结果if not object_id('outtable1') is null
drop table outtable
Go
Create table outtable1([bill] nvarchar(20),[goodscode] varchar(100),mount int ,prodate date)
Insert outtable1
select '单号1','',100,'2018/5/1'
union all
select '单号1','',200,'2018/5/1'
union all
select '单号2','',300,'2018/4/1'
union all
select '单号2','',200,'2018/5/1'
union all
select '单号3','',100,'2018/5/1'
union all
select '单号3','',50,'2018/4/1'
union all
select '单号3','',200,'2018/5/1'
union all
select '单号4','',250,'2018/4/1'
union all
select '单号4','',300,'2018/5/1'
union all
select '单号4','',50,'2018/4/1'
select * from outtable1--方法一
SELECT a.bill
, a.goodscode
, CASE WHEN a.sumMount>b.sumMount THEN b.sumMount
ELSE a.sumMount
END-CASE WHEN a.sumMount-a.mount>b.sumMount-b.mount THEN a.sumMount-a.mount
ELSE b.sumMount-b.mount
END AS mount
, b.prodate
FROM
(SELECT *
,(SELECT SUM(mount) FROM outtable WHERE [goodscode]=o.[goodscode] AND [bill]<=o.[bill]) AS sumMount
FROM outtable AS o) AS a
INNER JOIN
(SELECT *
, (SELECT SUM(mount) FROM intable WHERE [goodscode]=i.[goodscode] AND prodate<=i.prodate) AS sumMount
FROM intable AS i) AS b ON a.goodscode=b.goodscode
WHERE a.sumMount>b.sumMount-b.mount
AND b.sumMount>a.sumMount-a.mount
ORDER BY 1;--方法二
IF OBJECT_ID('outtable1') IS NOT NULL DROP TABLE outtable1;
WITH
DATA AS(
SELECT * ,
mount as 剩余,
ROW_NUMBER()OVER(PARTITION BY goodscode, prodate ORDER BY mount) as _row_id
FROM outtable
),
RE AS(
SELECT DATA.*, DATA.mount as 累计
FROM DATA
WHERE _row_id = 1
UNION ALL
SELECT DATA.*, DATA.mount+RE.累计 as 累计
FROM DATA, RE
WHERE DATA.goodscode = RE.goodscode AND DATA.prodate=RE.prodate
AND DATA._row_id = RE._row_id+1
)
SELECT * INTO outtable1 FROM RE
OPTION(MAXRECURSION 0)
;
-- [goodscode] + prodate 汇总范围内的 Bill
UPDATE DATA
SET _row_id = -DATA._row_id,
剩余 = DATA.累计 - A.mount
FROM outtable1 DATA, intable A
WHERE DATA.goodscode = A.goodscode AND DATA.prodate = A.prodate
AND DATA.累计 - DATA.剩余 < A.mount
;
-- -- [goodscode] + prodate 范围内，未包含足够明细的汇总数据
IF OBJECT_ID('tempdb..#intable') IS NOT NULL DROP TABLE #intable;
WITH
DATA AS(
SELECT A.goodscode, A.prodate, A.mount - ISNULL(DATA.累计-DATA.剩余, 0) as mount
FROM intable A
LEFT JOIN(
SELECT *
FROM outtable1 DATA
WHERE _row_id < 0
AND NOT EXISTS(
SELECT * FROM outtable1 A
WHERE DATA.goodscode = A.goodscode AND DATA.prodate = A.prodate
AND A._row_id < DATA._row_id
)
) DATA
ON DATA.goodscode = A.goodscode AND DATA.prodate = A.prodate
)
SELECT *, ROW_NUMBER()OVER(PARTITION BY goodscode ORDER BY prodate) as _row_id
INTO #intable
FROM DATA
WHERE mount > 0
;
-- 针对每个未包含足够明细的汇总项的明细数据调整
DECLARE @row_id int = 1;
WHILE EXISTS( SELECT * FROM #intable WHERE _row_id = @row_id )
BEGIN;
-- 已经分配给某汇总项，但分配有剩余的 bill 拆分
INSERT INTO outtable1( bill, goodscode, mount, prodate, 剩余, _row_id )
SELECT bill, goodscode, 剩余 as mount, prodate, 剩余, -_row_id
FROM outtable1 DATA
WHERE 剩余 > 0 AND _row_id < 0
AND NOT EXISTS(
SELECT * FROM outtable1 A
WHERE DATA.goodscode = A.goodscode AND DATA.prodate = A.prodate
AND A._row_id < DATA._row_id
)
;
-- 未分配给汇总项的 bill 排序累计信息
WITH
DATA AS(
SELECT goodscode, prodate, _row_id,
mount,
ROW_NUMBER()OVER(PARTITION BY goodscode ORDER BY mount DESC, prodate) as _row_id_new
FROM outtable1
WHERE _row_id > 0
),
RE AS(
SELECT DATA.*, DATA.mount as 累计
FROM DATA
WHERE _row_id_new = 1
UNION ALL
SELECT DATA.*, DATA.mount+RE.累计 as 累计
FROM DATA, RE
WHERE DATA.goodscode = RE.goodscode AND DATA.prodate=RE.prodate
AND DATA._row_id_new = RE._row_id_new+1
)
UPDATE DATA
SET _row_id = A._row_id_new,
累计 = A.累计
FROM outtable1 DATA, RE A
WHERE DATA.goodscode = A.goodscode AND DATA.prodate = A.prodate AND DATA._row_id = A._row_id
OPTION(MAXRECURSION 0)
;
-- 明细数据分配给汇总项
UPDATE DATA
SET _row_id = -DATA._row_id,
剩余 = DATA.累计 - A.mount,
prodate = A.prodate
FROM outtable1 DATA, #intable A
WHERE DATA._row_id > 0
AND A._row_id = @row_id
AND DATA.goodscode = A.goodscode
AND DATA.累计 - DATA.剩余 < A.mount
;
SET @row_id = @row_id + 1;
END;
-- 更新拆分的 mount
UPDATE outtable1 SET mount = mount - 剩余 WHERE 剩余 > 0;
-- 显示最终处理结果
SELECT bill, goodscode, mount, prodate FROM outtable1;-- 总总验证修正后的数据
SELECT goodscode, prodate, SUM(mount) as mount
FROM outtable1
GROUP BY goodscode, prodate
ORDER BY 1, 2, 3
;
SELECT goodscode, prodate, mount FROM intable ORDER BY 1, 2, 3;

CREATE TABLE #t ([Id] [INT] IDENTITY(1, 1) NOT NULL,
[Title] [NVARCHAR](50),
[CreationTime] [DATETIME] DEFAULT(GETDATE()),
[OrderIndex] [INT], );
GOINSERT INTO #t(Title, OrderIndex)VALUES('文章A', 0);
INSERT INTO #t(Title, OrderIndex)VALUES('文章B', 0);
INSERT INTO #t(Title, OrderIndex)VALUES('文章C', 2);
INSERT INTO #t(Title, OrderIndex)VALUES('文章D', 0);
INSERT INTO #t(Title, OrderIndex)VALUES('文章E', 6);
INSERT INTO #t(Title, OrderIndex)VALUES('文章F', 3);
INSERT INTO #t(Title, OrderIndex)VALUES('文章G', 0);
INSERT INTO #t(Title, OrderIndex)VALUES('文章H', 4);
INSERT INTO #t(Title, OrderIndex)VALUES('文章I', 0);
SELECT * FROM #t
--方法（1）
SELECT rowId, Id, Title, CreationTime, OrderIndex
FROM (SELECT rowId=Id, rn=ROW_NUMBER() OVER (ORDER BY Id)
FROM #t
WHERE Id NOT IN(SELECT OrderIndex FROM #t))A
INNER JOIN(SELECT *, rn=ROW_NUMBER() OVER (ORDER BY Id)FROM #t WHERE OrderIndex=0)B ON A.rn=B.rn
UNION ALL
SELECT OrderIndex, * FROM #t WHERE OrderIndex>0
ORDER BY 1;
--方法（2）
SELECT DISTINCT number, #t.*
INTO #basic
FROM master..spt_values a
LEFT JOIN #t ON number=OrderIndex
WHERE number BETWEEN 1 AND(SELECT MAX(OrderIndex)FROM #t);
SELECT ISNULL(X1.Id, X2.Id) AS ID, ISNULL(X1.Title, X2.Title) AS title, ISNULL(X1.CreationTime, X2.CreationTime) AS CreationTime, ISNULL(X1.OrderIndex, X2.OrderIndex) AS orderIndex
FROM (SELECT tmp=(SELECT SUM(CASE WHEN Id IS NULL THEN 1 ELSE 0 END)
FROM #basic
WHERE number<=A.number AND Id IS NULL), *
FROM #basic AS A)X1
FULL JOIN(SELECT tmp=(SELECT SUM(CASE WHEN OrderIndex=0 THEN 1 ELSE 0 END)FROM #t WHERE Id<=A.Id), *
FROM #t AS A
WHERE OrderIndex=0)X2 ON X1.tmp=X2.tmp AND X1.Id IS NULL
ORDER BY CASE WHEN X1.number IS NOT NULL THEN 1 ELSE 2 END, X1.number, X2.Id;

ALTER PROCEDURE [dbo].[pro_GetBOMList]
-- 存储过程参数
@PROID varchar(100)
AS
BEGIN --检查临时表
If object_id('tempdb..##BOMINFO') is not null Drop Table ##BOMINFO --创建临时表
Create table ##BOMINFO
(
BOM_NO varchar(100) not null, --BOM号
PRD_NO varchar(100) PRIMARY KEY, --品号
PRDNAME varchar(100), --品名
MIN_PURCHASE INT default(0), --最小采购量
PRICE [numeric](18,5) default(0), --单价
PRICESubtotal [numeric](18,5) default(0), --单个部品金额 采购量*单价
SUP_COUNT INT default(0), --供应商/家
ID_NO varchar(100), --子件ID
LAYER INT,
) --递归读取BOM表（With前面有语句需要用分好隔开）
;With TBOM as
(
Select BOM_NO,PRD_NO,[NAME],ID_NO,QTY from DB_TD02.DB_TD02.dbo.TF_BOM WHERE BOM_NO >=@PROID+'->'
union all
Select B.BOM_NO,B.PRD_NO,B.NAME,B.ID_NO,B.QTY from TBOM
inner join DB_TD02.DB_TD02.dbo.TF_BOM B on TBOM.ID_NO=B.BOM_NO
) --Select * from TBOM (测试数据)
--根据型号查询BOM记录复制到临时表(层级)
Insert into ##BOMINFO(BOM_NO,PRD_NO,PRDNAME,ID_NO) Select B.BOM_NO,B.PRD_NO,B.NAME,B.ID_NO from TBOM B -----------------------------------------------------------------------------------------------------
-------------------------------游标执行对递归后TBOM的其他计算处理-------------------------------------
-----------------------------------------------------------------------------------------------------
--申明变量(用户游标复制操作)
declare @PRD_NO varchar(100)
declare @PRDNAME varchar(100)
declare @BomCusCount int --货品对应供应商总数
declare @PRICE numeric(18,8) --部品单价
declare @MIN_PURCHASE int --最小采购量
declare @Layer int --层级
declare @KND varchar(1) --大类代号
declare @IDX1 varchar(10) --中类代号(包装类、喷油件、电镀件...)
declare @SUP1 varchar(12) --主供应商
declare @MAXDAYS numeric(18,8) --最长前置期
declare @MAXDAYSVALUE numeric(18,8) --存放最终前置值
Select @MAXDAYSVALUE=0 --初始化最长前置期
Select @MAXDAYS=0 --初始化最长前置期 --声明一个游标cur_BOMINFO，select语句中参数的个数必须要和从游标取出的变量名相同
declare cur_BOMINFO cursor for select PRD_NO from ##BOMINFO
--打开游标
Open cur_BOMINFO
--读取游标 一条记录插入变量
Fetch next from cur_BOMINFO into @PRD_NO
While(@@fetch_status = 0)
Begin
--根据品号查找对应供应商个数
Select @BomCusCount=COUNT(C.CUS_NO) from DB_TD02.DB_TD02.dbo.PRDT_CUS C WHERE C.PRD_NO =@PRD_NO
--修改前Select top 1 @KND=KND,@IDX1=IDX1,@SUP1=SUP1 from DB_TD02.DB_TD02.dbo.PRDT P WHERE P.PRD_NO =@PRD_NO
--根据品号供应商信息更新
Update ##BOMINFO set SUP_COUNT=SUP_COUNT+@BomCusCount where PRD_NO=@PRD_NO --查找货品查找单价
Select top 1 @PRICE=isnull(UP_DEF.UP,0.0000) from DB_TD02.DB_TD02.dbo.UP_DEF UP_DEF WHERE PRD_NO=@PRD_NO
--根据品号查找单价信息更新
Update ##BOMINFO set PRICE=@PRICE where PRD_NO=@PRD_NO --根据品号查找最小采购量
Select @MIN_PURCHASE=isnull(QTY_MIN,0),@MAXDAYS=NEED_DAYS from DB_TD02.DB_TD02.dbo.PRDT WHERE PRD_NO=@PRD_NO
--根据品号查找最先采购量信息更新
Update ##BOMINFO set MIN_PURCHASE=@MIN_PURCHASE where PRD_NO=@PRD_NO --判断最长前置期
IF @MAXDAYS>@MAXDAYSVALUE
begin
Set @MAXDAYSVALUE=@MAXDAYSVALUE
end --继续....
Fetch next from cur_BOMINFO into @PRD_NO
End
--关闭游标
Close cur_BOMINFO
--删除游标
Deallocate cur_BOMINFO --查询结果
--print @MAXDAYSVALUE
--Select * from ##BOMINFO
END

--> 生成测试数据表:BOM
If not object_id('[BOM]') is null
    Drop table [BOM]
Go
Create table [BOM]([BILLID] int,[MGOODSID] nvarchar(4),[MQTY] decimal(18,2),[DGOODSID] nvarchar(4),[ITEMNO] decimal(18,2),[DQTY] decimal(18,2),[DPRICE] decimal(18,2))
Insert BOM
Select 584,'A001',100.00,'B001',1.00,100.00,0.0 union all
Select 584,'A001',100.00,'C001',2,100,1.00 union all
Select 584,'A001',100.00,'D001',3,100,1 union all
Select 588,'B001',100.00,'E001',1,100,1 union all
Select 588,'B001',100.00,'F001',2,100,0.0 union all
Select 560,'F001',100.00,'G001',1,100,1 union all
Select 560,'F001',100.00,'J001',2,100,1
Go
--Select * from BOM
/*
BILLID MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE
------ -------- ---- -------- ------ ---- ------
584 A001 100 B001 1 100 0
584 A001 100 C001 2 100 1
584 A001 100 D001 3 100 1
588 B001 100 E001 1 100 1
588 B001 100 F001 2 100 0
560 F001 100 G001 1 100 1
560 F001 100 J001 2 100 1
*/-->SQL查询如下:
If not object_id('[Fn_BOM]') is null
    Drop function [Fn_BOM]
Go
Create Function Fn_BOM(@MGoodsID varchar(20))
returns @t table(MGOODSID varchar(20),MQTY dec(18,2),DGOODSID varchar(20),ITEMNO dec(18,2),DQTY dec(18,2),DPRICE dec(18,2),DAMT dec(18,2),lvl int)
as
begin
    declare @lvl int
    set @lvl=0
    insert @t select MGOODSID,MQTY,DGOODSID,ITEMNO,DQTY,DPRICE,NULL,@lvl from BOM where MGoodsID=@MGoodsID
    while @@rowcount>0
        begin
            set @lvl=@lvl+1
            insert @t
            select a.MGOODSID,a.MQTY,a.DGOODSID,a.ITEMNO,a.DQTY,a.DPRICE,a.DQTY*a.DPRICE,@lvl
            from BOM a,@t b
            where a.MGoodsID=b.DGoodsID
                and b.lvl=@lvl-1
        end
    declare @i int
    select @i=max(lvl) from @t where dprice =0
    while @i>=0
        begin
            update a set
                dprice=(select sum(damt) from @t where a.DGOODSID=MGOODSID)/dqty,
                damt=(select sum(damt) from @t where a.DGOODSID=MGOODSID)
            from @t a
            where lvl=@i
                and dprice =0
            set @i=@i-1
        end
    update @t set DAMT=DQTY*DPRICE where DAMT is null
    return
end
go
select * from fn_bom('a001')
/*
MGOODSID MQTY DGOODSID ITEMNO DQTY DPRICE DAMT lvl
-------- ---- -------- ------ ---- ------ ---- ---
A001 100 B001 1 100 3 300 0
A001 100 C001 2 100 1 100 0
A001 100 D001 3 100 1 100 0
B001 100 E001 1 100 1 100 1
B001 100 F001 2 100 2 200 1
F001 100 G001 1 100 1 100 2
F001 100 J001 2 100 1 100 2
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
    Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,sum(DAMT) DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
group by MGOODSID,MQTY
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DAMT
-------- ---- ----
B001 100 300
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DAMT
-------- ---- ----
A001 100 500
*/--计算成本存储过程
If not object_id('[Sp_BOM]') is null
    Drop proc [Sp_BOM]
Go
Create proc Sp_BOM(@GOODSID varchar(20))
as
select MGOODSID,MQTY,DGOODSID ,DQTY,DPRICE,DAMT
from fn_bom(@GOODSID)
where MGOODSID=@GOODSID
GO--调用过程
exec [Sp_BOM] 'B001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------- ---- -------- ---- ------ ----
B001 100 E001 100 1 100
B001 100 F001 100 2 200
*/
exec [Sp_BOM] 'A001'
/*
MGOODSID MQTY DGOODSID DQTY DPRICE DAMT
-------- ---- -------- ---- ------ ----
A001 100 B001 100 3 300
A001 100 C001 100 1 100
A001 100 D001 100 1 100
*/

--方法一
CREATE TABLE #Department(Nr VARCHAR(50),Name NVARCHAR(50));
INSERT INTO #Department
( Nr, Name )
VALUES ( '',N'人事部'),( '',N'销售部'),( '',N'财务部')CREATE TABLE #Person(DeparNr VARCHAR(50),Nr VARCHAR(50),Name NVARCHAR(50),Age INT);INSERT INTO #Person
( DeparNr, Nr, Name, Age )
VALUES ( '', '', N'张三', 18 ),
( '', '', N'李四', 25 ),
( '', '', N'王五', 27 ),
( '', '', N'赵六', 31 ),
( '', '', N'钱七', 25 ),
( '', '', N'孙八', 25 );WITH CTE AS
(
SELECT CONVERT(VARCHAR(50),'18~20') AS DisplayName,18 AS MinAge,20 AS MaxAge
UNION ALL
SELECT CONVERT(VARCHAR(50),RTRIM(CTE.MaxAge) + '~' + RTRIM(CTE.MaxAge+10)) ,CTE.MaxAge,CTE.MaxAge+10
FROM CTE
WHERE CTE.MaxAge < 60
),CTE2 AS(
SELECT *
FROM #Department
FULL JOIN CTE ON 1=1)SELECT a.Name,a.DisplayName,STUFF(b.Col.query('Employee/text()').value('.','nvarchar(100)'),1,1,'')
FROM CTE2 a
CROSS APPLY(SELECT ',' + Name AS Employee FROM #Person WHERE a.Nr = DeparNr AND Age >= a.MinAge AND Age < a.MaxAge FOR XML PATH(''),TYPE) b(Col)
WHERE b.Col IS NOT NULL--方法二
;WITH Staff AS
(
SELECT 部门编号
,CASE WHEN 年龄 BETWEEN 18 AND 20 THEN '18~20'
WHEN 年龄 BETWEEN 21 AND 30 THEN '20~30'
ELSE 'other'
END AS 年龄段
,姓名
FROM 员工表
)
SELECT a.部门名称
,b.年龄段
,COUNT(*) AS 人数
,姓名=STUFF((SELECT ','+姓名 FROM Staff WHERE 部门编号=b.部门编号 AND 年龄段=b.年龄段 FOR XML PATH('') ),1,1,'')
FROM 部门表 AS a
INNER JOIN Staff AS b ON a.部门编号 = b.部门编号
GROUP BY a.部门名称
,b.年龄段
,b.部门编号;--注: 虽然两个方法结果不太一样,但可以借鉴思路

WITH cte AS (
SELECT 1 AS Val UNION ALL SELECT Val+1 FROM cte WHERE Val<9
),tmp AS (
SELECT * FROM (SELECT A.Val X,B.Val Y,IIF(B.Val>A.Val,'',CONCAT(B.Val,'×',A.Val,'＝',A.Val*B.Val)) AS XY FROM cte A,cte B) D PIVOT(MIN(XY) FOR Y IN ([],[],[],[],[],[],[],[],[])) P
)
SELECT * FROM tmp

WITH Station(Name1,Name2,Value) AS (
SELECT '南昌', '北京', 1.0 UNION ALL
SELECT '南昌', '北京', 2.0 UNION ALL
SELECT '北京', '南昌', 3.0 UNION ALL
SELECT '南昌', '上海', 4.0 UNION ALL
SELECT '上海', '南昌', 5.0 UNION ALL
SELECT '北京', '上海', 6.0 UNION ALL
SELECT '上海', '北京', 7.0 UNION ALL
SELECT '南京', '北京', 8.0 UNION ALL
SELECT '南昌', '南京', 9.0 UNION ALL
SELECT '南昌', '南京', 10.0
),t1 AS (
SELECT *, List=Station.Name1+'-'+Station.Name2+' / '+Station.Name2+'-'+Station.Name1
FROM Station
),t2 AS (
SELECT a.Name1, a.Name2, a.Value, b.List
FROM t1 a
OUTER APPLY(SELECT TOP 1 t1.List
FROM t1
WHERE t1.List LIKE '%'+a.Name1+'-'+a.Name2+'%')b
)
SELECT t2.List 路线, SUM(t2.Value) 合计 FROM t2 GROUP BY t2.List