sql分组之后逗号分隔显示其他项

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        XMLPATH('')) as  changeFabricType,
        (select distinct  cast(createrAutoid as varchar(20))+','  FROM  ripreport_FabricAccessoriesNumberChangeOrders  wherechangeFabricPeriod=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    FORXMLPATH('')) as  createDate,
        (select distinct  cast(auditorAutoid as varchar(20))+','  FROM  ripreport_FabricAccessoriesNumberChangeOrders  wherechangeFabricPeriod=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 wherechangeFabricPeriod=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    FORXML 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