任何人都可以逐步解释以下代码.... [英] Can Anyone Explain following code step by step please....
问题描述
Can Anyone Explain following code step by step please....
ALTER procedure [dbo].[amazon_Number1Direct_reports](@usname varchar(100),@date varchar(50))
as
begin
if (@date ='''')
begin
DECLARE @sql VARCHAR(2000)
--declare @usname varchar(100)
SET @sql = ''
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM [tbl_amazon1_master_merchant]
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when '''''' + @usname + '''''' then ''''you'''' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when ''''''+ @usname+ '''''' then ''''you'''' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and datename(dd,t1.updated_date)= datename(dd,m.updateddate) and datename(mm,t1.updated_date)= datename(mm,m.updateddate)and datename(yy,t1.updated_date)=datename(yy,m.updateddate) and datename(dd,t1.updated_date)= datename(dd,getdate()) and datename(mm,t1.updated_date)=datename(mm,getdate()) and datename(yy,t1.updated_date)=datename(yy,getdate())
drop table ##temp''
exec (@sql)
end
else
begin
DECLARE @sql1 VARCHAR(2000)
--declare @usname varchar(100)
SET @sql1 = ''
SELECT ID,MPN,productname,updateddate,
MAX(CASE WHEN low = 1 THEN Merchants ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN baseprice ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN merchants ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN baseprice ELSE NULL END) AS HighPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN baseprice ELSE NULL END) AS MyPrice,
MAX(CASE merchants WHEN '''''' + @usname + '''''' THEN bottomlineprice ELSE NULL END) AS MyBottomprice
INTO ##Temp
FROM (
SELECT ID,MPN,productname,updateddate,
merchants,
baseprice,
bottomlineprice,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY ID,MPN,updateddate,productname ORDER BY baseprice asc) AS low
FROM tbl_amazon1_master_merchant
) AS t
GROUP BY ID,MPN,productname,updateddate
SELECT
m.ID,
m.MPN,
t1.product_description as Product_Title,
m.MyPrice,
m.MyBottomprice,
m.LowPrice,
case m.LowPriceMerchant when '''''' + @usname + '''''' then ''''you'''' else m.LowPriceMerchant end as LowPriceMerchant,
m.HighPrice,
case m.HighPriceMerchant when ''''''+ @usname+ '''''' then ''''you'''' else m.HighPriceMerchant end as HighPriceMerchant
FROM ##Temp AS m
INNER JOIN tbl_amazon1_master_product AS t1 ON t1.ID = m.ID and t1.updated_date=m.updateddate and m.updateddate=cast(''''''+@date+'''''' as datetime)
drop table ##temp''
--in (select updateddate from tbl_googleshopping2_master_merchant) and datepart(dd,t1.updated_date)= datepart(dd,cast(''''''+@date+'''''' as datetime)) and datepart(mm,t1.updated_date)=datepart(mm,cast(''''''+@date+'''''' as datetime)) and datepart(yy,t1.updated_date)=datepart(yy,cast(''''''+@date+'''''' as datetime))
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))= datepart(dd,getdate()) and datepart(mm,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(mm,getdate()) and datepart(yy,CAST(FLOOR((CAST(t1.Updated_Date as varchar))) AS DATETIME))=datepart(yy,getdate())
--INNER JOIN [tbl_Pricegrabber_master_product] as t1 on t1.MobID = m.MobID and datepart(dd,t1.Updated_Date)= datepart(dd,getdate()) and datepart(mm,t1.Updated_Date)=datepart(mm,getdate()) and datepart(yy,t1.Updated_Date)=datepart(yy,getdate())
exec (@sql1)
end
end
推荐答案
您知道逐行解释代码的工作量吗?
每一行都需要一段说明!例如:
Do you have any idea how much work explaining code line by line is?
Every single line needs a paragraph of explanation! For example:
ALTER procedure [dbo].[amazon_Number1Direct_reports](@usname varchar(100),@date varchar(50))
在当前数据库中更改名为amazon_Number1Direct_reports的现有存储过程.新的过程定义应包含两个参数:第一个称为"@usname",并且期望可变长度的字符串不超过100个字符.第二个称为"@date",期望可变长度的字符串不超过50个字符.
您能想象我们需要花多长时间一行一行地解释一个非常短的代码片段吗?
不,这不会发生.如果您有特定的问题,请提出一个问题.但是先想想-您是否想坐下45分钟并在没有充分理由的情况下逐行输入说明?
Change an existing stored procedure called amazon_Number1Direct_reports in the current database. The new procedure definition should take two parameters: the first is called "@usname" and expects a variable length character string of no more than 100 characters. The second is called "@date", and expects a variable length character string of no more than 50 characters.
Can you imagine how long it would take us to explain even a very short code fragment like your example, line by line?
No. It is not going to happen. If you have a specific problem, then ask a question about it. But think first - would you want to sit down for 45 minutes and type up a line-by-line description for no good reason?
这篇关于任何人都可以逐步解释以下代码....的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!