SQL
--select * from Products
--EXEC('select * from Products')
DECLARE @topCount INT = 6
DECLARE @SQL nvarchar(max)
SET @SQL =
'
select top ' + CAST(@topCount AS nvarchar(10)) + '
o.CustomerEmail,
sum(p.Price * ol.Quantity) as ''Total Money Spent''
into #tempTopCustomers
from
orders o
join OrderLines ol on ol.OrderId = o.OrderId
join Products p on p.ProductId = ol.ProductId
group by o.CustomerEmail
order by sum(p.Price * ol.Quantity) desc
select
tc.CustomerEmail,
FORMAT(o.OrderId,''000000'') as ''Order Number'',
o.OrderDate,
p.Name as ''Product Name'',
ol.Quantity as ''Quantity Ordered''
from #tempTopCustomers tc
join orders o on tc.CustomerEmail = o.CustomerEmail
join OrderLines ol on ol.OrderId = o.OrderId
join Products p on p.ProductId = ol.ProductId
order by
tc.[Total Money Spent] desc,
o.OrderDate asc
drop table #tempTopCustomers'
EXEC(@SQL)
标签:OrderId,ol,join,080,Dynamic,CustomerEmail,SQL,select
From: https://blog.csdn.net/KevinHuang2088/article/details/143415912