SQL Server:从查询输出中设置变量

Hey all, I am wondering how to set variables to the output of the following query string:

嗨，大家好，我想知道如何将变量设置为以下查询字符串的输出:

SELECT count(*) fees_quantity,
   1.5 fees_price,
   1.5 * count(*) fees_amount,
   round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount,
   round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount
FROM tblHGP HGP,  
   OrderDetails OD, 
   tblInvoices i
  JOIN tblCS cs ON i.INumber = cs.INumber
  JOIN tblECI ac ON i.INumber = ac.INumber 
WHERE cs.SoldTo = HGP.ECard 
AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
AND Country = 'US' 
AND HGP.iNumber = OD.orderdetail

How can I do something like this?

我怎么能做这样的事呢?

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

@FeesQty = fees_quantity
@FeesTotal = fees_amount
@RedepTotal = redep_amount
@Total = total_amount

How can that be done?

这是怎么做到的呢?

Thanks!

谢谢!

David

大卫

1 个解决方案

#1

Like this:

是这样的:

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

SELECT
    @FeesQty = count(*),
    @FeesTotal = 1.5 * count(*),
    @RedepTotal = round(SUM((CONVERT(int,Points) * .1)),0)),
    @Total = round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*)
FROM tblHGP HGP,  
   OrderDetails OD, 
   tblInvoices i
  JOIN tblCS cs ON i.INumber = cs.INumber
  JOIN tblECI ac ON i.INumber = ac.INumber 
WHERE cs.SoldTo = HGP.ECard 
AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
AND Country = 'US' 
AND HGP.iNumber = OD.orderdetail

Or - I suppose - If you didn't want to modify your query:

或者-我想-如果你不想修改你的查询:

DECLARE @FeesQty varchar(3)
DECLARE @FeesTotal varchar(10)
DECLARE @RedepTotal varchar(10)
DECLARE @Total varchar(10)

SELECT
    @FeesQty = t.fees_quantity,
    @FeesTotal = t.fees_amount,
    @RedepTotal = t.redep_amount,
    @Total = t.total_amount
FROM (
    SELECT count(*) fees_quantity,
       1.5 fees_price,
       1.5 * count(*) fees_amount,
       round(SUM((CONVERT(int,Points) * .1)),0)) redep_amount,
       round(SUM((CONVERT(int,Points) * .1)),0)) + 1.5 * count(*) total_amount
    FROM tblHGP HGP,  
       OrderDetails OD, 
       tblInvoices i
      JOIN tblCS cs ON i.INumber = cs.INumber
      JOIN tblECI ac ON i.INumber = ac.INumber 
    WHERE cs.SoldTo = HGP.ECard 
    AND issued BETWEEN '2010-09-01' AND '2010-09-30 23:59:59' 
    AND Country = 'US' 
    AND HGP.iNumber = OD.orderdetail
) t

#1