近几天使用到多张表合成一张表、时间转化笔记。

原创 2015年11月20日 17:05:35
SELECT 
	*
FROM 	
	(
		SELECT o.OrderNo, o.OrderDate,o.CheckReason FROM Orders o
		WHERE OrderDate BETWEEN '2015-09-01 14:33:29.802' AND '2015-11-07 14:33:29.802' 
		AND OrderStatus = '-30' 
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName FROM
			(
				SELECT s.OrderNo,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-09-01 14:33:29.802'	
				AND '2015-11-07 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-09-01 14:33:29.802'	
				AND '2015-11-07 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-09-01 14:33:29.802'	
				AND '2015-11-07 14:33:29.802'  
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-09-01 14:33:29.802'	
				AND '2015-11-07 14:33:29.802' 
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName="',','),'"/>',''),1,1,'')
	)N

SELECT * FROM (
SELECT *
FROM 	
	(
		SELECT o.OrderDate, o.DeliveryDate,o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent 
		FROM Orders o
		WHERE OrderDate BETWEEN '2015-11-16 14:33:29.802' AND '2015-11-18 14:33:29.802'  AND DeliveryDate <='2015-11-23 14:33:29.802'
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName,面料,米数 FROM
			(
				SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802'  
				UNION ALL
				SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N''

SELECT * FROM (
SELECT *
FROM 	
	(
		SELECT o.OrderNo, o.CreateTime,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent 
		FROM Orders o
		WHERE OrderDate BETWEEN '2015-11-16 14:33:29.802' AND '2015-11-18 14:33:29.802'  AND DeliveryDate <='2015-11-23 14:33:29.802'
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName,面料,米数 FROM
			(
				SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802'  
				UNION ALL
				SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N''


SELECT * FROM (
SELECT *
FROM 	
	(
		SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent 
		FROM Orders o
		WHERE OrderDate BETWEEN '{0}' AND '{1}' AND DeliveryDate <= '{2}'
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName,面料,米数,工艺 FROM
			(
				SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '{3}'
				AND '{4}'
				UNION ALL
				SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '{5}'
				AND '{6}'
				UNION ALL
				SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '{7}'
				AND '{8}'
				UNION ALL
				SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '{9}'	
				AND '{10}'
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N''



select s.OrderNo,
       h.Fabric,
	   h.MaterialUnit, 
	   case s.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent , 
	   h.Technology,
	   h.Quantity 
from Orders s join OrdersCoats h on h.OrderID =s.OrderID 
where s.OrderDate between '2015-11-16 14:33:29.802'and '2015-11-18 14:33:29.802'  and s.OrderStatus ='90' and s.DeliveryDate <='2015-11-24 14:33:29.802' and s.OrderNo <>N'' and h.Technology <>N'' 
order by s.DeliveryDate desc 


SELECT * FROM (SELECT * FROM (SELECT o.OrderDate, o.DeliveryDate,o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent  FROM Orders o	WHERE OrderDate BETWEEN '2015/10/1 11:41:01' AND '2015/11/18 11:41:01'  AND DeliveryDate <= '2015-11-18 11:43:02.734' 	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/10/1 11:41:01'	AND '2015/11/18 11:41:01'UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/10/1 11:41:01'	AND '2015/11/18 11:41:01'	UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/10/1 11:41:01'	AND '2015/11/18 11:41:01' 	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/10/1 11:41:01'	AND '2015/11/18 11:41:01'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO	),'<t2 PName=',','),'/>',''),1,1,''))N	)t3  WHERE t3.PName is not null and t3.PName <>N'' 

select s.OrderNo, h.Fabric, h.MaterialUnit,  case s.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent ,  h.Technology,h.Quantity from Orders s join OrdersCoats h on h.OrderID =s.OrderID  where s.OrderDate between '{0}'and '{1}'  and s.OrderStatus ='90' and s.DeliveryDate <='{2}' and s.OrderNo <>N'' and h.Technology <>N'' order by s.DeliveryDate desc  

insert into ShirtSize(ShirtOrderNo,ShirtCustomerName,ShirtCustomerSource,ShirtCustomerHeight,ShirtCustomerWeight,ShirtClothesLength,ShirtShoulder,ShirtOutsideSleeve,ShirtBust,Abdomen,NeckLine)values('" + Convert.ToString(txtShirtOrderNo.Text) + "','" + Convert.ToString(txtShirtCustomerName.Text) + "','" + Convert.ToString(txtShirtCustomerSource.Text) + "','" + Convert.ToString(txtShirtCustomerHeight.Text) + "','" + Convert.ToString(txtShirtCustomerWeight.Text) + "','" + Convert.ToString(txtShirtClothesLength.Text) + "','" + Convert.ToString(txtShirtShoulder.Text) + "','" + Convert.ToString(txtShirtOutsideSleeve.Text) + "','" + Convert.ToString(txtShirtBust.Text) + "','" + Convert.ToString(txtAbdomen.Text) + "','" + Convert.ToString(txtNeckLine.Text) + "')


select s.OrderDate,
       s.DeliveryDate,
	   s.OrderNo,
       case s.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent,
	   c.ProductName,
	   c.Quantity ,
	   c.Fabric,
	   c.MaterialUnit 
from Orders s join OrdersCoats c on s.OrderID =c.OrderID  
where   s.OrderDate between '2015-11-01 14:33:29.802'and '2015-11-07 14:33:29.802' 
        and s.DeliveryDate <='2015-11-07 14:33:29.802' and c.ProductName <>N'' 
		and s.OrderNo <>N''
order by s.DeliveryDate desc 

SELECT * FROM (SELECT * FROM (SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent FROM Orders o WHERE OrderDate BETWEEN '2015/11/16 15:06:43' AND '2015/11/16 15:06:43' and OrderStatus ='90'	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数,工艺 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID AND OrderDate BETWEEN '2015/11/16 15:06:43' AND '2015/11/16 15:06:43'	UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/11/16 15:06:43'	AND '2015/11/16 15:06:43'UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/11/16 15:06:43'	AND '2015/11/16 15:06:43'	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015/11/16 15:06:43'	AND '2015/11/16 15:06:43'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO ),'<t2 PName=',','),'/>',''),1,1,'')	)N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' 

SELECT * FROM (SELECT * FROM (SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent FROM Orders o WHERE OrderDate BETWEEN '2015-11-17 00:00:00' AND '2015-11-17 23:59:59' and OrderStatus ='90'	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数,工艺 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID AND OrderDate BETWEEN '2015-11-17 00:00:00' AND '2015-11-17 23:59:59'	UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-17 00:00:00'	AND '2015-11-17 23:59:59'UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-17 00:00:00'	AND '2015-11-17 23:59:59'	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-17 00:00:00'	AND '2015-11-17 23:59:59'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO ),'<t2 PName=',','),'/>',''),1,1,'')	)N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' 

select *
from Orders
where OrderNo='ZQ001001'
select *
from OrdersSuits
where OrderID ='188'


DELETE FROM Orders WHERE OrderNo='ZF930006'  





select 
distinct
o.OrderID, 
o.TranNo, 
o.OrderNo, 
c.CustomerName, 
o.Amount, 
e.EmployeeName, 
o.OrderDate, 
o.DeliveryDate, 
o.OrderStatus
from  
orders o,
customer c,
OAAccount a,
Employee e ,
OrderStatusRecord osr
where 
o.isdelete=0
and c.id=o.customerid
and o.Creater=a.AccountID 
and a.UserID=e.EmployeeID 
and o.OrderID=osr.OrderID
and osr.OrderStatus=110
and osr.NewStatus=90
and osr.CreateTime between '2015/9/20 0:00:00' and '2015/11/22 0:00:00'

SELECT * FROM (
SELECT *
FROM 	
	(
		SELECT o.OrderNo ,convert(char(10),o.CreateTime,120) as CreateTime,r.Remark,DateDiff( DAY,r.CreateTime,o.DeliveryDate)AS Day  
		FROM Orders o  join  OrderStatusRecord r on r.OrderID=o.OrderID
		WHERE  o.OrderDate BETWEEN '2015-11-16 14:33:29.802' AND '2015-11-18 14:33:29.802'  
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName FROM
			(
				SELECT s.OrderNo,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802'  
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '2015-11-16 14:33:29.802'	
				AND '2015-11-18 14:33:29.802' 
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' order by t3.Day desc


SELECT * FROM (
SELECT *
FROM 	
	(
		SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent 
		FROM Orders o
		WHERE OrderDate BETWEEN '{0}' AND '{1}' AND DeliveryDate <= '{2}'
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName,面料,米数,工艺 FROM
			(
				SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '{3}'
				AND '{4}'
				UNION ALL
				SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '{5}'
				AND '{6}'
				UNION ALL
				SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '{7}'
				AND '{8}'
				UNION ALL
				SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '{9}'	
				AND '{10}'
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N''

SELECT * FROM (

SELECT *
FROM 	
	(
		SELECT o.OrderNo ,o.CreateTime ,r.Remark,DateDiff( DAY,r.CreateTime,o.DeliveryDate)AS Day  
		FROM Orders o  join  OrderStatusRecord r on r.OrderID=o.OrderID
		WHERE  o.OrderDate BETWEEN '{0}' AND '{1}' 
	) t1 OUTER APPLY(
		SELECT PName = STUFF(REPLACE(REPLACE((
			SELECT PName FROM
			(
				SELECT s.OrderNo,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID
				AND OrderDate BETWEEN '{2}'
				AND '{3}'
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID
				AND OrderDate BETWEEN '{4}'
				AND '{5}'
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID
				AND OrderDate BETWEEN '{6}'
				AND '{7}'
				UNION ALL
				SELECT s.OrderNo,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID
				AND OrderDate BETWEEN '{8}'
				AND '{9}'
			) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO
		),'<t2 PName=',','),'/>',''),1,1,'')
	)N
	)t3 
WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N''

SELECT * FROM (SELECT * FROM (SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent FROM Orders o WHERE OrderDate BETWEEN '2015-11-15 00:00:00' AND '2015-11-19 23:59:59' and OrderStatus ='90'	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数,工艺 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID AND OrderDate BETWEEN '2015-11-15 00:00:00' AND '2015-11-19 23:59:59'	UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-19 23:59:59'UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-19 23:59:59'	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-19 23:59:59'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO ),'<t2 PName=',','),'/>',''),1,1,'')	)N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N''  order by t3.IsCustomerUrgent desc

select o.OrderNo ,o.CreateTime ,r.Remark,DateDiff( DAY,r.CreateTime,o.DeliveryDate)  

from   Orders o  join  OrderStatusRecord r on r.OrderID=o.OrderID

select *
from Orders 
where OrderStatus =50 and CreateTime BETWEEN '2015-11-20 00:00:00'	AND '2015-11-20 23:59:59'

select convert(char(10),CreateTime,120) 
from Orders 

SELECT * FROM (SELECT * FROM (SELECT convert(char(10),o.OrderDate,120) as OrderDate , convert(char(10),o.DeliveryDate,120) as DeliveryDate ,o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent  FROM Orders o	WHERE OrderDate BETWEEN '2015-11-15 00:00:00' AND '2015-11-20 23:59:59'  AND DeliveryDate <= '2015-11-20 16:15:47.357' 	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-20 23:59:59'UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-20 23:59:59'	UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-20 23:59:59' 	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-15 00:00:00'	AND '2015-11-20 23:59:59'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO	),'<t2 PName=',','),'/>',''),1,1,''))N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' 


SELECT * FROM (SELECT * FROM (SELECT o.OrderNo,case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent FROM Orders o WHERE OrderDate BETWEEN '2015-11-19 00:00:00' AND '2015-11-19 23:59:59' and OrderStatus ='90'	) t1 OUTER APPLY(	SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName,面料,米数,工艺 FROM	(	SELECT s.OrderNo,u.Fabric 面料 ,u.MaterialUnit 米数,Technology 工艺, CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID AND OrderDate BETWEEN '2015-11-19 00:00:00' AND '2015-11-19 23:59:59'	UNION ALL	SELECT s.OrderNo,p.Fabric 面料,p.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-19 00:00:00'	AND '2015-11-19 23:59:59'UNION ALL	SELECT s.OrderNo,v.Fabric 面料,v.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-19 00:00:00'	AND '2015-11-19 23:59:59'	UNION ALL	SELECT s.OrderNo,c.Fabric 面料,c.MaterialUnit 米数,Technology 工艺,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-19 00:00:00'	AND '2015-11-19 23:59:59'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO ),'<t2 PName=',','),'/>',''),1,1,'')	)N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' order by t3.IsCustomerUrgent desc 

SELECT * FROM ( SELECT * FROM (	SELECT o.OrderNo , convert(char(10),o.CreateTime,120) as OrderDate  ,r.Remark,DateDiff( DAY,r.CreateTime,o.DeliveryDate)AS Day   FROM Orders o  join  OrderStatusRecord r on r.OrderID=o.OrderID	WHERE  o.OrderDate BETWEEN '2015-11-18 00:00:00' AND '2015-11-20 23:59:59'	) t1 OUTER APPLY( SELECT PName = STUFF(REPLACE(REPLACE((	SELECT PName FROM(	SELECT s.OrderNo,CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName FROM Orders s, OrdersSuits u WHERE u.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-18 00:00:00'	AND '2015-11-20 23:59:59'	UNION ALL	SELECT s.OrderNo,CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName FROM Orders s, OrdersPants p WHERE p.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-18 00:00:00'	AND '2015-11-20 23:59:59'	UNION ALL	SELECT s.OrderNo,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName FROM Orders s, OrdersVest v WHERE v.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-18 00:00:00'	AND '2015-11-20 23:59:59'	UNION ALL	SELECT s.OrderNo,CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName FROM Orders s, OrdersCoats c WHERE c.OrderID = s.OrderID	AND OrderDate BETWEEN '2015-11-18 00:00:00'	AND '2015-11-20 23:59:59'	) t2 WHERE t2.OrderNo=t1.OrderNo FOR XML AUTO	),'<t2 PName=',','),'/>',''),1,1,'')	)N	)t3  WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N''order by t3.Day desc 

select convert(char(10),s.OrderDate,120) as OrderDate ,s.OrderNo,c.Monogram,c.Quantity  from Orders s join OrdersCoats c on c.OrderID =s.OrderID   where s.OrderDate between '2015-10-01 00:00:00'and '2015-11-20 23:59:59' and c.Monogram is not null  and c.Monogram <>N'' and  c.ProductName ='上衣' order by s.OrderDate  desc


版权声明:本文为博主原创文章,未经博主允许不得转载。

pdf多页合成一张pdf图片

//使用架包pdfbox-1.71  fontbox-1.71 public String[]  yPic(String pdfPath)  {//strs是传过来的pdf文件路径 Str...
  • yue974104520
  • yue974104520
  • 2016年12月27日 15:48
  • 291

Iori的工具箱 之 C# 实现多个图片合为一个Tiff

Iori的工具箱 之 C# 实现多个图片合为一个Tiff
  • csharp25
  • csharp25
  • 2014年04月11日 11:33
  • 1554

基于ENVI的图像融合+波段合成

最近研究了图像融合的相关内容,学会了一种在ENVI软件上实现的融合多光谱图像和全色图像(高分辨图像)的方法,现在总结流程如下:  1、打开ENVI软件,这里所用的是ENVI5.1版本    2、...
  • qq_35040828
  • qq_35040828
  • 2016年12月28日 21:28
  • 6737

关于时间的一些转化

#define FIELD_BASE_SECOND_AGO                           @"%d秒前" #define FIELD_BASE_MINUTE_AGO      ...
  • boyan92626
  • boyan92626
  • 2017年07月03日 18:46
  • 45

将秒数转化为日期、时、分、秒

1.说明 笔者最近在开发过程中,需要进行时间上的处理的地方比较多,有时候没有处理好导致出现各种的错误。这里主要是讲一下,如何时将秒数的时间转化为"yyyy-MM-dd HH:mm:ss"的格式,例如...
  • owen_william
  • owen_william
  • 2016年12月04日 17:30
  • 3128

确定性多项式时间

确定性多项式时间内能够解决的问题,解决某一类问题,如果肯定有一个上限,而且上限可以表示成一个确定性多项式的形式,当然无所谓说确定性多项式时间到底n的次数是多大了,只要不是随着n呈现指数增长,那就是确定...
  • sunyang8901562
  • sunyang8901562
  • 2014年12月11日 20:04
  • 751

Android中的录音与音频合成

IOS的框架层有许多有趣的特性,使得它可以将多条音轨合并成一条音轨。你可以使用 Audio Unit 和它提供的方法 但是你想要一个类似的功能在Android上?Android的音频框架层并不支持这...
  • u013676055
  • u013676055
  • 2015年12月31日 12:27
  • 4244

与时间相关的工具类,如时间挫转换成时间,两个时间相比较大小,取每个月的最后一天等问题...

现在app基本上都会用到时间选择器,而有时候传递给后台的会是string类型的时间,做一些逻辑处理时候还会比较两个时间的大小,或者取到某个月的最后一天或者第一天等,聪明的程序员经常会把这样转换代码封装...
  • baidu_32015283
  • baidu_32015283
  • 2017年12月12日 16:08
  • 116

年月日时间和64位时间的使用及相互转换

年月日时间和64位时间的使用,以及它们之间的相互转换
  • chenlycly
  • chenlycly
  • 2015年01月12日 20:59
  • 3394

Android图形显示系统——下层显示4:图层合成下(硬件合成器)

硬件合成器-HwComposer使用3D合成,需要大面积的像素混合计算和大量的内存传输(GPU读写GraphicBuffer所需),对GPU和DDR来说是一个巨大的负担。在GPU/DDR重度使用的场景...
  • jxt1234and2010
  • jxt1234and2010
  • 2015年06月08日 03:19
  • 5550
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:近几天使用到多张表合成一张表、时间转化笔记。
举报原因:
原因补充:

(最多只允许输入30个字)