Sql基本学习一
1.把一个库A的表Product复制到库B表Product
View Code
INSERT INTO [库名A].[dbo].[Product]
( [ProCode] ,
[ProName] ,
[ProPinyin] ,
[ProChemicalName] ,
[ProType] ,
[ProFormulation] ,
[ProStandard] ,
[ProBatch]
--.......等
,
[ProDeliverType] ,
[ProIsPredict] ,
[ProIsInquiry]
)
SELECT [ProCode] ,
[ProName] ,
[ProPinyin] ,
[ProChemicalName] ,
[ProType] ,
[ProFormulation] ,
[ProStandard] ,
[ProBatch]
--.......等
,
[ProDeliverType] ,
[ProIsPredict] ,
[ProIsInquiry]
FROM [库名B].[dbo].[Product]
2.多条合并成一条
View Code
CREATE VIEW [dbo].[VNewCurrentOrder]
AS
WITH OrderDeductList
AS ( SELECT A.OrderDeductID ,
A.OrderProductID ,
A.DeductUser ,
A.DeductFactor ,
A.DeductMoney ,
A.BackUserID ,
A.BackUserName ,
A.BackPrice ,
A.BackScotFactor ,
A.ScotMoney ,
A.OvertopBackMoney ,
A.FactSaleMoney ,
B.OrderDeductID AS OrderDeductID2 ,
B.BackUserID2 ,
B.BackUserName2 ,
B.BackPrice2 ,
B.BackScotFactor2 ,
B.ScotMoney2 ,
B.OvertopBackMoney2 ,
B.FactSaleMoney2
FROM ( SELECT OD.OrderDeductID ,
OD.OrderProductID ,
OD.DeductUser ,
OD.DeductFactor ,
OD.DeductMoney ,
OD.BackUserID ,
BackUserName = ( CASE WHEN OD.DeductType = 0
THEN ( SELECT
SUA.EmployeeName
FROM
dbo.SysUserAccount SUA
WITH ( NOLOCK )
WHERE
OD.BackUserID = SUA.UserID
)
WHEN OD.DeductType = 4
THEN ''
ELSE ( SELECT
C.CusName + '('
+ CL.LinkmanName
+ ')'
FROM
dbo.Customer C
WITH ( NOLOCK )
INNER JOIN dbo.CustomerLinkman CL
WITH ( NOLOCK ) ON C.CustomerID = CL.CustomerID
AND CL.LinkmanIsMain = 1
WHERE
C.IsSupplier = 0
AND C.CusDelState = 1
AND OD.BackUserID = C.CustomerID
)
END ) ,
BackPrice = ( CASE WHEN OD.DeductType = 4
THEN NULL
ELSE OD.BackPrice
END ) ,
BackScotFactor = ( CASE WHEN OD.DeductType = 4
THEN NULL
ELSE OD.BackScotFactor
END ) ,
ScotMoney = ( CASE WHEN OD.DeductType = 4
THEN NULL
ELSE OD.ScotMoney
END ) ,
OvertopBackMoney = ( CASE WHEN OD.DeductType = 4
THEN NULL
ELSE OD.OvertopBackMoney
END ) ,
OD.FactSaleMoney
FROM dbo.OrderDeduct OD WITH ( NOLOCK )
WHERE OD.DeductType IN ( 0, 1, 4 )
) A
LEFT JOIN ( SELECT OD.OrderDeductID ,
OD.OrderProductID ,
BackUserID2 = OD.BackUserID ,
BackUserName2 = ( SELECT
C.CusName + '('
+ CL.LinkmanName
+ ')'
FROM
dbo.Customer C
WITH ( NOLOCK )
INNER JOIN dbo.CustomerLinkman CL
WITH ( NOLOCK ) ON C.CustomerID = CL.CustomerID
AND CL.LinkmanIsMain = 1
WHERE
C.IsSupplier = 0
AND C.CusDelState = 1
AND OD.BackUserID = C.CustomerID
) ,
BackPrice2 = OD.BackPrice ,
BackScotFactor2 = OD.BackScotFactor ,
ScotMoney2 = OD.ScotMoney ,
OvertopBackMoney2 = OD.OvertopBackMoney ,
FactSaleMoney2 = OD.FactSaleMoney
FROM dbo.OrderDeduct OD WITH ( NOLOCK )
WHERE OD.DeductType = 2
) B ON A.OrderProductID = B.OrderProductID
),
DeliveryDeductList
AS ( SELECT A.DeliveryDeductID ,
A.DeliveryProductID ,
A.DeductUser ,
A.DeductFactor ,
A.DeductMoney ,
A.BackUserID ,
A.BackUserName ,
A.BackPrice ,
A.BackScotFactor ,
A.ScotMoney ,
A.OvertopBackMoney ,
A.FactSaleMoney ,
B.DeliveryDeductID AS DeliveryDeductID2 ,
B.BackUserID2 ,
B.BackUserName2 ,
B.BackPrice2 ,
B.BackScotFactor2 ,
B.ScotMoney2 ,
B.OvertopBackMoney2 ,
B.FactSaleMoney2
FROM ( SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
DD.DeductUser ,
DD.DeductFactor ,
DD.DeductMoney ,
DD.BackUserID ,
BackUserName = ( CASE WHEN DD.DeductType = 0
THEN ( SELECT
SUA.EmployeeName
FROM
dbo.SysUserAccount SUA
WITH ( NOLOCK )
WHERE
DD.BackUserID = SUA.UserID
)
WHEN DD.DeductType = 4
THEN ''
ELSE ( SELECT
C.CusName + '('
+ CL.LinkmanName
+ ')'
FROM
dbo.Customer C
WITH ( NOLOCK )
INNER JOIN dbo.CustomerLinkman CL
WITH ( NOLOCK ) ON C.CustomerID = CL.CustomerID
AND CL.LinkmanIsMain = 1
WHERE
C.IsSupplier = 0
AND C.CusDelState = 1
AND DD.BackUserID = C.CustomerID
)
END ) ,
BackPrice = ( CASE WHEN DD.DeductType = 4
THEN NULL
ELSE DD.BackPrice
END ) ,
BackScotFactor = ( CASE WHEN DD.DeductType = 4
THEN NULL
ELSE DD.BackScotFactor
END ) ,
ScotMoney = ( CASE WHEN DD.DeductType = 4
THEN NULL
ELSE DD.ScotMoney
END ) ,
OvertopBackMoney = ( CASE WHEN DD.DeductType = 4
THEN NULL
ELSE DD.OvertopBackMoney
END ) ,
DD.FactSaleMoney
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.BusinessType = 0
AND DD.BusinessType = 0
AND DD.DeductType IN ( 0, 1, 4 )
) A
LEFT JOIN ( SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
BackUserID2 = DD.BackUserID ,
BackUserName2 = ( SELECT
C.CusName + '('
+ CL.LinkmanName
+ ')'
FROM
dbo.Customer C
WITH ( NOLOCK )
INNER JOIN dbo.CustomerLinkman CL
WITH ( NOLOCK ) ON C.CustomerID = CL.CustomerID
AND CL.LinkmanIsMain = 1
WHERE
C.IsSupplier = 0
AND C.CusDelState = 1
AND DD.BackUserID = C.CustomerID
) ,
BackPrice2 = DD.BackPrice ,
BackScotFactor2 = DD.BackScotFactor ,
ScotMoney2 = DD.ScotMoney ,
OvertopBackMoney2 = DD.OvertopBackMoney ,
FactSaleMoney2 = DD.FactSaleMoney
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.BusinessType = 0
AND DD.BusinessType = 0
AND DD.DeductType = 2
) B ON A.DeliveryProductID = B.DeliveryProductID
)
SELECT *
FROM OrderDeductList ;
3.单条分成多条
View Code
CREATE VIEW [dbo].[VDeductReportDetail]
AS
WITH DeductDetailList
AS ( SELECT A.DeliveryDeductID ,
A.DeliveryProductID ,
A.UserID ,
A.CustomerID ,
A.ProductID ,
A.DeductType ,
A.DepID ,
A.DeductUser ,
A.SaleNum ,
A.SalePrice ,
A.LowestPrice ,
A.DeductFactor ,
A.DeductMoney ,
A.ScotMoney ,
A.BusinessType ,
A.RedState ,
A.DeliveryDate ,
A.InDate ,
BizType ,
BackUserID
FROM ( SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
DD.UserID ,
DD.CustomerID ,
DD.ProductID ,
DD.DeductType ,
DD.DepID ,
DD.DeductUser ,
DD.SaleNum ,
DD.SalePrice ,
DD.LowestPrice ,
DD.DeductFactor ,
DD.DeductMoney ,
ScotMoney = 0 ,
DD.BusinessType ,
DD.RedState ,
DD.DeliveryDate ,
DD.InDate ,
BizType = 0 ,
DD.BackUserID
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.DeductType IN ( 0, 1 )
UNION
SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
DD.UserID ,
DD.CustomerID ,
DD.ProductID ,
DD.DeductType ,
DD.DepID ,
DD.DeductUser ,
DD.SaleNum ,
DD.SalePrice ,
DD.LowestPrice ,
DeductFactor = DD.BackScotFactor ,
DeductMoney = DD.OvertopBackMoney ,
DD.ScotMoney ,
DD.BusinessType ,
DD.RedState ,
DD.DeliveryDate ,
DD.InDate ,
BizType = ( CASE WHEN DD.DeductType = 0
THEN 1
WHEN DD.DeductType = 1
THEN 2
END ) ,
DD.BackUserID
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.DeductType IN ( 0, 1 )
) A
UNION
SELECT B.DeliveryDeductID ,
B.DeliveryProductID ,
B.UserID ,
B.CustomerID ,
B.ProductID ,
B.DeductType ,
B.DepID ,
B.DeductUser ,
B.SaleNum ,
B.SalePrice ,
B.LowestPrice ,
B.DeductFactor ,
B.DeductMoney ,
B.ScotMoney ,
B.BusinessType ,
B.RedState ,
B.DeliveryDate ,
B.InDate ,
BizType ,
BackUserID
FROM ( SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
DD.UserID ,
DD.CustomerID ,
DD.ProductID ,
DD.DeductType ,
DD.DepID ,
DD.DeductUser ,
DD.SaleNum ,
DD.SalePrice ,
DD.LowestPrice ,
DeductFactor = DD.BackScotFactor ,
DeductMoney = DD.OvertopBackMoney ,
DD.ScotMoney ,
DD.BusinessType ,
DD.RedState ,
DD.DeliveryDate ,
DD.InDate ,
BizType = 2 ,
DD.BackUserID
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.DeductType = 2
) B
UNION
SELECT D.DeliveryDeductID ,
D.DeliveryProductID ,
D.UserID ,
D.CustomerID ,
D.ProductID ,
D.DeductType ,
D.DepID ,
D.DeductUser ,
D.SaleNum ,
D.SalePrice ,
D.LowestPrice ,
D.DeductFactor ,
D.DeductMoney ,
D.ScotMoney ,
D.BusinessType ,
D.RedState ,
D.DeliveryDate ,
D.InDate ,
BizType ,
BackUserID
FROM ( SELECT DD.DeliveryDeductID ,
DD.DeliveryProductID ,
DD.UserID ,
DD.CustomerID ,
DD.ProductID ,
DD.DeductType ,
DD.DepID ,
DD.DeductUser ,
DD.SaleNum ,
DD.SalePrice ,
DD.LowestPrice ,
DD.DeductFactor ,
DD.DeductMoney ,
DD.ScotMoney ,
DD.BusinessType ,
DD.RedState ,
DD.DeliveryDate ,
DD.InDate ,
BizType = 0 ,
DD.BackUserID
FROM dbo.DeliveryDeduct DD WITH ( NOLOCK )
WHERE DD.DeductType = 4
) D
)
SELECT DDL.DeliveryDeductID ,
DDL.DeliveryProductID ,
DDL.DeductType ,
DDL.DeductUser ,
DDL.BizType ,
CASE DDL.BizType
WHEN 0 THEN '办事处提成'
WHEN 1 THEN '返办事处'
WHEN 2 THEN '返代理商'
END AS DeductTypeName ,
DDL.LowestPrice ,
DDL.DeductFactor ,
DDL.DeductMoney ,
DDL.ScotMoney ,
DDL.UserID ,
DDL.BackUserID ,
DDL.BusinessType ,
CASE DDL.BusinessType
WHEN 0 THEN '发货'
WHEN 1 THEN '退货'
WHEN 2 THEN '报损'
END AS BusinessTypeName ,
DDL.RedState ,
CASE DDL.RedState
WHEN 0 THEN '未冲红'
ELSE '已冲红'
END AS RedStateName ,
DDL.ProductID ,
VPIV.ProName ,
VPIV.ProFormulation ,
VPIV.ProStandard ,
VPIV.FactoryName ,
DDL.CustomerID ,
C.CusName ,
CL.LinkmanName ,
C.CusName + '(' + CL.LinkmanName + ')' AS CustomerName ,
SUA.EmployeeName AS DeductUserName ,
SD.DepName ,
SD.FatherIDPath ,
DDL.SaleNum ,
DDL.SalePrice ,
DeliveryMoney = DDL.SaleNum * DDL.SalePrice ,
DDL.DeliveryDate ,
DDL.InDate
FROM DeductDetailList DDL WITH ( NOLOCK )
INNER JOIN Customer C WITH ( NOLOCK ) ON DDL.CustomerID = C.CustomerID
INNER JOIN dbo.CustomerLinkman CL WITH ( NOLOCK ) ON C.CustomerID = CL.CustomerID
AND CL.LinkmanIsMain = 1
INNER JOIN dbo.VProductInfoView VPIV WITH ( NOLOCK ) ON DDL.ProductID = VPIV.ProductID
LEFT JOIN dbo.SysUserAccount SUA WITH ( NOLOCK ) ON DDL.DeductUser = SUA.UserID
LEFT JOIN dbo.SysDepartment SD WITH ( NOLOCK ) ON DDL.DepID = SD.DepID
WHERE C.CusDelState = 1
AND C.IsSupplier = 0 ;
4.循环更新
View Code
UPDATE A
SET CusUser = B.UserID
FROM dbo.Customer A ,
( SELECT CU.CustomerID ,
CU.UserID
FROM dbo.CustomerUser CU
WHERE CU.CustomerID IN ( SELECT CustomerID
FROM dbo.Customer C
WHERE CusUser = 0
AND CussState = 1 )
) B
WHERE A.CustomerID = B.CustomerID
AND A.CusUser = 0
AND A.CussState = 1
