USE [DB_YH_ERP_GW_V2_NewReq]
GO
/****** Object: StoredProcedure [dbo].[sp_FactoryAllocationFind] Script Date: 05/09/2016 08:45:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_FactoryAllocationFind]
--[业务日期]
@btime datetime,
@etime datetime,
--[调出工厂]
@warehouseOut varchar(50),
--[调入工厂]
@warehouseIn varchar(50),
--[调拨单号]
@sn varchar(50),
--[调拨入库单号]
@snIn varchar(50),
--[调拨取消单号]
@snCancel varchar(50),
--[说明]
@remark varchar(200),
--[包裹信息]
@package varchar(200),
--[状态]
@status varchar(50),
--[审批状态]
@auditStatus varchar(50),
--[在途未处理]
@intransit bit
AS
BEGIN
declare @sqltable table(F_SN varchar(50)) --这个用了临时表
if(@snIn IS NOT NULL) or (@intransit IS NOT NULL)
insert into @sqltable(F_SN)(select F_AllocationSN from dbo.T_Warehouse_FactoryAllocationIn
where F_SN = @snIn or F_InTransitStatus = @intransit) --临时表的存储
else if(@snCancel IS NOT NULL)
insert into @sqltable(F_SN)(select F_AllocationSN from dbo.T_Warehouse_FactoryAllocationCancel
where F_SN = @snCancel)
else if(@package IS NOT NULL)
insert into @sqltable(F_SN)(select F_SN from dbo.T_Warehouse_FactoryAllocation
where F_ID = (
select F_AllocationID from dbo.T_Warehouse_FactoryAllocationDetail
where CHARINDEX(@package,F_Version)>0 or CHARINDEX(@package,F_PackageBarcode)>0 or CHARINDEX(@package,F_ProductName)>0))
else
insert into @sqltable(F_SN)(select F_SN from dbo.T_Warehouse_FactoryAllocation
where (ISNULL(@btime,'')='' or F_ServiceTime>=@btime)
and (ISNULL(@etime,'')='' or F_ServiceTime< DATEADD(dd,1,@etime))
and (ISNULL(@warehouseOut,'')='' or CHARINDEX(@warehouseOut, F_OutFactoryName)>0)
and (ISNULL(@warehouseIn,'')='' or CHARINDEX(@warehouseIn, F_InFactoryName)>0)
and (ISNULL(@sn,'')='' or CHARINDEX(@sn,F_SN)>0)
and (ISNULL(@remark,'')='' or CHARINDEX(@remark,F_Remark)>0)
and (ISNULL(@status,'')='' or CHARINDEX(@status,F_Status)>0)
and (ISNULL(@auditStatus,'')='' or CHARINDEX(@auditStatus,F_Status)>0))
select *
from(
select F_ID as ID, '调出' as 类型, F_SN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, F_OutFactoryName as 调出工厂, F_OutWarehouseName as 调出仓库,
F_InFactoryName as 调入工厂,F_InWarehouseName as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocation
union
select F_ID as ID, '调入' as 类型, F_AllocationSN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, '' as 调出工厂, '' as 调出仓库,
'' as 调入工厂,'' as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocationIn
union
select F_ID as ID, '取消' as 类型, F_AllocationSN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, '' as 调出工厂, '' as 调出仓库,
'' as 调入工厂,'' as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocationCancel
) as t
where t.调拨单号= ANY(select * from @sqltable) --临时表的使用
order by t.调拨单号,t.类型
GO
/****** Object: StoredProcedure [dbo].[sp_FactoryAllocationFind] Script Date: 05/09/2016 08:45:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_FactoryAllocationFind]
--[业务日期]
@btime datetime,
@etime datetime,
--[调出工厂]
@warehouseOut varchar(50),
--[调入工厂]
@warehouseIn varchar(50),
--[调拨单号]
@sn varchar(50),
--[调拨入库单号]
@snIn varchar(50),
--[调拨取消单号]
@snCancel varchar(50),
--[说明]
@remark varchar(200),
--[包裹信息]
@package varchar(200),
--[状态]
@status varchar(50),
--[审批状态]
@auditStatus varchar(50),
--[在途未处理]
@intransit bit
AS
BEGIN
declare @sqltable table(F_SN varchar(50)) --这个用了临时表
if(@snIn IS NOT NULL) or (@intransit IS NOT NULL)
insert into @sqltable(F_SN)(select F_AllocationSN from dbo.T_Warehouse_FactoryAllocationIn
where F_SN = @snIn or F_InTransitStatus = @intransit) --临时表的存储
else if(@snCancel IS NOT NULL)
insert into @sqltable(F_SN)(select F_AllocationSN from dbo.T_Warehouse_FactoryAllocationCancel
where F_SN = @snCancel)
else if(@package IS NOT NULL)
insert into @sqltable(F_SN)(select F_SN from dbo.T_Warehouse_FactoryAllocation
where F_ID = (
select F_AllocationID from dbo.T_Warehouse_FactoryAllocationDetail
where CHARINDEX(@package,F_Version)>0 or CHARINDEX(@package,F_PackageBarcode)>0 or CHARINDEX(@package,F_ProductName)>0))
else
insert into @sqltable(F_SN)(select F_SN from dbo.T_Warehouse_FactoryAllocation
where (ISNULL(@btime,'')='' or F_ServiceTime>=@btime)
and (ISNULL(@etime,'')='' or F_ServiceTime< DATEADD(dd,1,@etime))
and (ISNULL(@warehouseOut,'')='' or CHARINDEX(@warehouseOut, F_OutFactoryName)>0)
and (ISNULL(@warehouseIn,'')='' or CHARINDEX(@warehouseIn, F_InFactoryName)>0)
and (ISNULL(@sn,'')='' or CHARINDEX(@sn,F_SN)>0)
and (ISNULL(@remark,'')='' or CHARINDEX(@remark,F_Remark)>0)
and (ISNULL(@status,'')='' or CHARINDEX(@status,F_Status)>0)
and (ISNULL(@auditStatus,'')='' or CHARINDEX(@auditStatus,F_Status)>0))
select *
from(
select F_ID as ID, '调出' as 类型, F_SN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, F_OutFactoryName as 调出工厂, F_OutWarehouseName as 调出仓库,
F_InFactoryName as 调入工厂,F_InWarehouseName as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocation
union
select F_ID as ID, '调入' as 类型, F_AllocationSN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, '' as 调出工厂, '' as 调出仓库,
'' as 调入工厂,'' as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocationIn
union
select F_ID as ID, '取消' as 类型, F_AllocationSN as 调拨单号, F_SN as 单号, F_ServiceTime as 业务日期, '' as 调出工厂, '' as 调出仓库,
'' as 调入工厂,'' as 调入仓库, F_CreationTime as 创建时间, F_Creator as 创建人, F_Status as 状态,
F_AuditStatus as 审批状态
from dbo.T_Warehouse_FactoryAllocationCancel
) as t
where t.调拨单号= ANY(select * from @sqltable) --临时表的使用
order by t.调拨单号,t.类型
END
//-------------------------------------------
将一列的值合并成一个值
select * ,STUFF(
(
SELECT ',' + tbc.C2
FROM (select tb1.A1, tb1.C1, tb2.C2
from Table_1 as tb1 left join Table_2 as tb2
on tb1.b1 = tb2.A2) as tbc
WHERE charindex(tbc.C1,tb3.B3)>0
FOR XML PATH('')), 1, 1, ''
) AS NAME
FROM Table_3 as tb3
------------------------
SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
) B
可以看到StuList列里面的数据都会多出一个逗号,这时随外层的语句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby 就是来去掉逗号,并赋予有意义的列明!