SELECT * from ripreport_FabricAccessoriesNumberChangeOrders
按“changeFabricPeriod”分组显示，其他项逗号分隔显示
SELECT fancob.changeFabricPeriod,
stuff(autoid,len(autoid),len(autoid),'') as autoid,
stuff(orderNo,len(orderNo),len(orderNo),'') as orderNo,
stuff(changeFabricType,len(changeFabricType),len(changeFabricType),'') as changeFabricType,
stuff(createrAutoid,len(createrAutoid),len(createrAutoid),'') as createrAutoid,
stuff(creater,len(creater),len(creater),'') as creater,
stuff(createDate,len(createDate),len(createDate),'') as createDate,
stuff(auditorAutoid,len(auditorAutoid),len(auditorAutoid),'') as auditorAutoid,
stuff(auditor,len(auditor),len(auditor),'') as auditor,
stuff(auditDate,len(auditDate),len(auditDate),'') as auditDate,
stuff(status,len(status),len(status),'') as status,
stuff(flag,len(flag),len(flag),'') as flag from (
select fanco.changeFabricPeriod,(select distinct cast(autoid AS VARCHAR(20))+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as autoid,
(select distinct orderNo+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as orderNo,
(select distinct changeFabricType+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as changeFabricType,
(select distinct cast(createrAutoid as varchar(20))+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as createrAutoid,
(select distinct creater+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as creater,
(select distinct createDate+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as createDate,
(select distinct cast(auditorAutoid as varchar(20))+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as auditorAutoid,
(select distinct auditor+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as auditor,
(select distinct CASE WHEN isnull(auditDate,'')='' THEN +'' ELSE auditDate+',' END FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as auditDate,
(select distinct cast(status as varchar(3))+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as status,
(select distinct cast(flag as varchar(3))+',' FROM ripreport_FabricAccessoriesNumberChangeOrders where changeFabricPeriod=fanco.changeFabricPeriod FOR XML PATH('')) as flag
FROM ripreport_FabricAccessoriesNumberChangeOrders fanco group BY changeFabricPeriod
) fancob
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
字符串判断链接条件
select pfsces.executeStandard,rsd.standard,rsd.autoid,rsd.referenceStandardAutoid FROM ripreport_ProductFactSheetChildExecuteStandard pfsces
left JOIN ripreport_ProductFactSheet pfs on pfsces.productFactSheetAutoid=pfs.autoid
LEFT JOIN ripreport_ReferenceStandardDetail rsd ON
CHARINDEX(REPLACE (pfsces.executeStandard,' ',''),REPLACE (rsd.standard,' ',''))>0
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT stuff(t.autoid,len(t.autoid),len(t.autoid),'') as autoid,
stuff(t.referenceStandardAutoid,len(t.referenceStandardAutoid),len(t.referenceStandardAutoid),'') as referenceStandardAutoid,
stuff(t.standard,len(t.standard),len(t.standard),'') as standard
from (
SELECT
(select rsd.standard+',' FROM ripreport_ProductFactSheetChildExecuteStandard pfsces
left JOIN ripreport_ProductFactSheet pfs on pfsces.productFactSheetAutoid=pfs.autoid
LEFT JOIN ripreport_ReferenceStandardDetail rsd ON
CHARINDEX(REPLACE (pfsces.executeStandard,' ',''),REPLACE (rsd.standard,' ',''))>0
WHERE pfsces.flag=0 AND pfs.flag=0 AND rsd.flag=0 AND pfs.styleno='5214006C' FOR XML
PATH('') ) as standard,
(select cast(rsd.autoid AS VARCHAR(10))+','
FROM ripreport_ProductFactSheetChildExecuteStandard pfsces
left JOIN ripreport_ProductFactSheet pfs on pfsces.productFactSheetAutoid=pfs.autoid
LEFT JOIN ripreport_ReferenceStandardDetail rsd ON
CHARINDEX(REPLACE (pfsces.executeStandard,' ',''),REPLACE (rsd.standard,' ',''))>0
WHERE pfsces.flag=0 AND pfs.flag=0 AND rsd.flag=0 AND pfs.styleno='5214006C' FOR XML
PATH('') ) as autoid,
(select cast(rsd.referenceStandardAutoid AS VARCHAR(10))+','
FROM ripreport_ProductFactSheetChildExecuteStandard pfsces
left JOIN ripreport_ProductFactSheet pfs on pfsces.productFactSheetAutoid=pfs.autoid
LEFT JOIN ripreport_ReferenceStandardDetail rsd ON
CHARINDEX(REPLACE (pfsces.executeStandard,' ',''),REPLACE (rsd.standard,' ',''))>0
WHERE pfsces.flag=0 AND pfs.flag=0 AND rsd.flag=0 AND pfs.styleno='5214006C' FOR XML
PATH('') ) as referenceStandardAutoid ) t
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL SERVER 中的截取替换问题
SELECT dsd.materialsName
, gcm.greyClothName
, fan.greyClothNum
, dsd.materialsNumber
, fan.wlType
, UPPER(ds.designStyle) as designStyle
,replace(substring(isnull(convert(NVARCHAR(10),ds.sjDate,120),
isnull(convert(NVARCHAR(10),dsr.planningDate,120),null)),0,8),'-','') as month,
convert(NVARCHAR(10),ds.sjDate,120) as sjdate,
isnull(convert(NVARCHAR(10),dsr.planningDate,120),null) as planningDate,
substring(isnull(convert(NVARCHAR(10),ds.sjDate,120),
isnull(convert(NVARCHAR(10),dsr.planningDate,120),null)),0,8) as replcae
FROM
ripreport_LikeClothingDevelopApplyDetail dsd
LEFT JOIN ripreport_LikeClothingDevelopApply ds
ON ds.autoid = dsd.likeClothingDevelopApplyAutoid
LEFT JOIN ripreport_FabricAccessoriesNumber fan
on fan.materialsNumber=dsd.materialsNumber
LEFT join ripreport_GreyClothMaintenance gcm on gcm.greyClothNum =fan.greyClothNum
LEFT JOIN ripreport_DesignStyleRecords dsr
ON ds.designstylerecordsAutoid = dsr.autoid
WHERE
1=1 AND gcm.flag = 0 and
dsd.materialsNumber IS NOT NULL AND dsd.materialsNumber!=''
AND fan.greyClothNum IS NOT NULL AND fan.greyClothNum !=''
AND dsd.flag=0 and dsr.flag = 0
AND dsd.cancelState=0 AND ds.status NOT IN(6,7) AND
replace(substring(isnull(convert(NVARCHAR(10),ds.sjDate,120),
isnull(convert(NVARCHAR(10),dsr.planningDate,120),null)),0,8),'-','') between '201201' and '201601'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
简单标量值函数定义
CREATE FUNCTION getCompanyName
(
@companyAutyoid nvarchar(50)
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @companyName nvarchar(50);
SELECT @companyName=@companyName from ripreport_CompanyInformation where autoid=@companyAutyoid
-- Return the result of the function
RETURN @companyName
END
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
使用别名update语句
UPDATE ripreport_SewWorkRecord SET sewFactor=(SELECT TOP 1 llll.sewFactor from ripreport_LikeClothingBarcodeGenerate llll WHERE llll.flag=0 AND llll.likeClothingDevelopApplyAutoid=likeClothingDevelopApplyAutoid
ORDER BY llll.autoid DESC
)
GO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
备份表
SELECT * INTO dbo.bak_20140901_ripreport_LayoutVisaAvailableDatePlan FROM ripreport_LayoutVisaAvailableDatePlan
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查询条件判断
select s.fabricAccessoriesNumberAutoid,s.materialNumber,f.autoid,f.category,s.project from
ripreport_SewWorkRecordMaterialDetail s left join ripreport_FabricAccessoriesNumber f
on s.fabricAccessoriesNumberAutoid=f.autoid where f.category!=(CASE WHEN len(s.project)>2 then substring(s.project,len(s.project)-1,len(s.project))
ELSE s.project END)
批量插入不存在的数据
INSERT INTO ripreport_ShootUploadTable(layoutVisaAvailableDatePlanAutoid,shootUploadStatus,shootUploadCount,
delflag,designStyle,productno,color,firstCategory,secondCategory,
thirdCategory,season,flag)
SELECT lvadp.autoid as layoutVisaAvailableDatePlanAutoid,'未拍照' as shootUploadStatus,0 as shootUploadCount,
lvadp.delflag as delflag,lvadp.designStyle as designStyle,lvadp.productno as productno,lvadp.color as color,lvadp.firstCategory as firstCategory,
lvadp.secondCategory as secondCategory,lvadp.thirdCategory as thirdCategory,lvadp.season as season,lvadp.flag as flag
FROM ripreport_LayoutVisaAvailableDatePlan lvadp where lvadp.autoid NOT IN (select sut.layoutVisaAvailableDatePlanAutoid from ripreport_ShootUploadTable sut )
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
case when统计
SELECT
sum(CASE WHEN t.cdate=#planTime# THEN t.planCost ELSE 0 END)-
sum(CASE WHEN t.planTime=#planTime# THEN t.planMoney ELSE 0 END) as planMoneyTotal,
sum(CASE WHEN t.cdate>=#dateFirst# AND t.cdate<=#dateEnd# THEN t.planCost ELSE 0 END)-
sum(CASE WHEN t.planTime>=#dateFirst# AND t.planTime<=#dateEnd# THEN t.planMoney ELSE 0 END) as payMoneyTotal,
0 as noPlanMoneyTotal
from (
select convert(VARCHAR(10),v.planTime,120) as planTime, isnull(v.planMoney,0) as planMoney,
'' as cdate,0 as planCost
from V_AllPayForFinancePlan v WHERE v.companyAutoid=#companyAutoid# AND v.planPayStatus='1'
UNION ALL
SELECT '' as planTime,0 as planMoney,cc.date as cdate,isnull(cc.planCost,0) as planCost
FROM ripreport_CapitalPlanChild cc LEFT JOIN ripreport_CapitalPlan c ON cc.capitalPlanAutoid=c.autoid
WHERE c.companyAutoid=#companyAutoid# AND cc.flag=0 AND c.flag=0
) t
玩转时间
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
取出指定日期段为一年中的第几周，跨年未测试
select '第'+convert(varchar(2),(datepart(week,dateadd(day,1 ,cast(cc.date as date))))-31)+'周' 周期,cc.* from
ripreport_CapitalPlanChild cc WHERE cc.date>='2014-08-04' AND cc.date<='2014-10-28' ORDER BY cc.date
指定某一天为一周的开始查周
SELECT (datediff(week,'2014-08-04',dateadd(day,1 ,cast(cc.date as date))))+1 as 周期,cc.* FROM ripreport_CapitalPlanChild cc WHERE cc.date>='2014-08-04' AND cc.date<='2014-10-28' ORDER BY cc.date
任意一天为一周的开始统计第几周
SELECT (datediff(day,'2014-08-04',cc.date)/7)+1 as 周期,cc.* FROM ripreport_CapitalPlanChild cc WHERE cc.date>='2014-08-04' AND cc.date<='2014-10-28' ORDER BY cc.date
升级数据删除重复数据
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UPDATE s SET s.flag=1 FROM ripreport_ShootUploadTable s WHERE s.autoid=(
SELECT max(ssss.autoid) from ripreport_ShootUploadTable ssss
GROUP BY ssss.layoutVisaAvailableDatePlanAutoid HAVING count(ssss.layoutVisaAvailableDatePlanAutoid)>=2 AND
s.layoutVisaAvailableDatePlanAutoid=ssss.layoutVisaAvailableDatePlanAutoid )
GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
摘录：当前n行的记录之和
select xh, price,
(case when totalprice is null then price else totalprice end ) as totalprice
from
(select a.xh, (select sum(price) from t_product b where b.xh < a.xh) as totalprice , a.price
from t_product a) x
标量值函数处理逻辑-----------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[getProductStatus](
@autoid int
)
returns nvarchar(100)
begin
declare @productStatus nvarchar(100);
SELECT @productStatus = (SELECT CASE
WHEN isnull(p.wcdate, '') != '' THEN
'wcdate'
ELSE
(CASE
WHEN isnull(p.likeClothingTryOnDate, '') != '' THEN
'likeClothingTryOnDate'
ELSE
(CASE
WHEN isnull(p.factOnlineDate, '') != '' THEN
'factOnlineDate'
ELSE
(CASE
WHEN isnull(p.kcdate, '') != '' THEN
'kcdate'
ELSE
(CASE
WHEN isnull(p.factMaterialArriveFactoryDate, '') != '' THEN
'factMaterialArriveFactoryDate'
ELSE
(CASE
WHEN isnull(p.checkBomDate, '') != '' THEN
'checkBomDate'
ELSE
'other'
END)
END)
END)
END)
END)
END
FROM
ripreport_productSchedule p
WHERE
p.autoid = @autoid);
return @productStatus;
END
-PARTITION BY 用法------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select row_number() over (order by tab.autoid DESC) as rowid,tab.* FROM (
SELECT row_number()OVER (PARTITION BY ps.designStyle,ps.colour,pi.firstCategory ORDER BY ps.autoid desc) as rid,ps.autoid,
(SELECT cast(p.autoid AS VARCHAR)+',' FROM ripreport_productschedule p
left join ripreport_ProductInformation pif on p.styleno = pif.productno and pif.flag=0
where p.designStyle=ps.designStyle AND p.colour=ps.colour AND isnull(pif.firstCategory,'')=isnull(pi.firstCategory,'') and p.flag=0 FOR XML PATH('')) as productscheduleAutoidStr,
lapu.photoUploadAutoid,ps.xdtype,ps.planRequirementShelvesDate,ps.designStyle,
pi.firstCategory,ps.colour,ps.isPresell,i.path as img,ps.bumenStr,ps.gduserName,ps.photographyButtonChangeRemark,ps.planPhotographyButtonNumnber,ps.approvalPhotographyButtonNumnberDate,
pu.status,pu.operatorsReUploadStatus,t.likeClothingStatus,t.borrowLikeClothingPerson,t.position
FROM ripreport_productschedule ps
left join ripreport_ProductInformation pi on ps.styleno = pi.productno and pi.flag=0
LEFT JOIN ripreport_layoutAndPhotoUpload lapu on (lapu.businessId=ps.autoid AND lapu.dataSource='productSchedule')
LEFT JOIN ripreport_PhotoUpload pu ON lapu.photoUploadAutoid=pu.autoid
LEFT JOIN ripreport_Img i ON lapu.imgAutoid=i.autoid
left JOIN (
select lcm.likeClothingBarcode,lcm.likeClothingStatus,lcm.position,
lcm.designStyle,lcm.brand,lcm.styleno,
lcm.color,lcmd.giveLikeClothingPerson,lcmd.giveLikeClothingTime,lcmd.borrowLikeClothingPerson,lcmd.borrowLikeClothingTime
from ripreport_LikeClothingBarcodeRecords lcm left join (SELECT * FROM ripreport_LikeClothingManagementDetail
where autoid in
(
SELECT max(autoid) FROM ripreport_LikeClothingManagementDetail
group BY likeClothingManagementAutoid
)) lcmd
on lcm.autoid = lcmd.likeClothingManagementAutoid
where lcm.flag=0 AND lcm.likeClothingType='摄影扣样衣'
) t ON (ps.designStyle=t.designStyle AND ps.colour=t.color AND pi.firstCategory=t.brand)
WHERE ps.flag=0 AND ps.xdtype IN ('首单')
)
tab WHERE tab.rid=1