如何查询此条件 [英] How Do I Query For This Condition

问题描述

我有选择这些数据的问题

 table1 
 
 id amount businessVolume Dueno advisor_id 
  1   500   50   1   1  
  2   1000   100   2   1  
  3   500   50   15   1  
  4   1000   100   3   1  
  5   500   50   2   2  
 < span class =code-digit> 6   1000   100   3   2  
  7   500   50   13   2  

这是我的表格格式，

i必须加总金额，而bv却有条件求和（金额）对于整个dueno和sum（业务量）其中dueno< = 12 group by advisor_id。



我期待

金额businessvolume advisor_id 
  3000   250   1  
  2000   150   2  

有什么建议吗？

我要把它实现到我的递归查询中

解决方案

< blockquote>

  SELECT  SUM（t1.amount）金额，
（ SELECT  SUM（t2.businessvolume） FROM  table1 t2  WHERE  t2.dueno< = 12 
  AND  t1.advisor_id = t2.advisor_id 
  GROUP   BY  t2.advisor_id）businessvolumn，
 advisor_id  FROM  table1 t1  GROUP   BY  t1.advisor_id 

检查：

  DECLARE   @ tmp  表（id  INT   IDENTITY （ 1 ， 1 ），金额 INT ，businessVolume  INT ，Dueno  INT ，advisor_id  INT ）
 
  INSERT   INTO   @ tmp （金额，businessVolume，Dueno，advisor_id）
  VALUES （ 500 ， 50 ， 1 ， 1 ），（ 1000 ， 100 ， 2 ， 1 ），
（ 500 ， 50 ， 15 ， 1 ），（ 1000 ， 100 ， 3 ， 1 ），
（ 500 ， 50 ， 2 ， 2 ），（ 1000 ， 100 ， 3 ， 2 ），
（< span class =code-digit> 500 ， 50 ， 13 ， 2 ）
 
  SELECT  t1.advisor_id，（ SELECT  SUM（金额） FROM   @ tmp   WHERE  advisor_id = t1.advisor_id） AS  SumOfAm ount，SUM（t1.businessVolume） AS  SumOfBissVol 
  FROM   @ tmp   AS  t1 
  WHERE  Dueno< 13 
  GROUP   BY  advisor_id 

结果：

 adv._id金额Buss.Vol 
 1 3000 250 
 2 2000 150 

[更新]

选择t1.advisor_id，t1.businessvolume，t2.amount来自
（选择总和（businessvolume）为'businessvolume'，advisor_id 
来自table1 
其中Dueno< 12 
 group by advisor_id）t1 
 inner join 
 （选择SUM（金额）为'金额'，顾问_id 
来自table1 
 group by advisor_id）t2 on t1.advisor_id = t2.advisor_id 

我建议你看这里： GROUP BY [ ^ ]



我在这里使用了内连接，但是如果t1没有返回任何行，你可能需要使用外连接。请参阅此处：外部加入 [ ^ ]

I have a problem to select these Data

table1

id amount businessVolume Dueno advisor_id
1   500     50            1     1
2   1000    100           2     1
3   500     50            15    1
4   1000    100           3     1
5   500     50            2     2
6   1000    100           3     2
7   500     50            13    2

this is my Table Format,
i have to sum the amount,and bv but there is a condition to sum ( amount) for whole dueno and to sum (business volume) where dueno <=12 group by advisor_id.

im expecting

amount businessvolume advisor_id
3000    250             1
2000    150             2

any suggestions ?
im going to implement this into my Recursive Query

解决方案

SELECT SUM(t1.amount) amount,
(SELECT SUM(t2.businessvolume) FROM table1 t2 WHERE t2.dueno <=12
   AND t1.advisor_id=t2.advisor_id
   GROUP BY t2.advisor_id) businessvolumn,
advisor_id FROM table1 t1 GROUP BY t1.advisor_id

Check this:

DECLARE @tmp TABLE (id INT IDENTITY(1,1), amount INT, businessVolume INT, Dueno INT, advisor_id INT)

INSERT INTO @tmp (amount, businessVolume, Dueno, advisor_id)
VALUES(500, 50, 1, 1), (1000, 100, 2, 1), 
(500, 50, 15, 1), (1000, 100, 3, 1), 
(500, 50, 2, 2), (1000, 100, 3, 2), 
(500, 50, 13, 2)

SELECT t1.advisor_id, (SELECT SUM(amount) FROM @tmp  WHERE advisor_id = t1.advisor_id) AS SumOfAmount, SUM(t1.businessVolume) AS SumOfBissVol
FROM @tmp AS t1
WHERE Dueno <13
GROUP BY advisor_id 

Result:

adv._id Amount  Buss.Vol
1	3000	250
2	2000	150

[Updated]

select t1.advisor_id, t1.businessvolume, t2.amount from 
(select sum(businessvolume) as 'businessvolume', advisor_id
from table1
where Dueno<12
group by advisor_id) t1
inner join 
(select SUM(amount) as 'amount', advisor_id
from table1
group by advisor_id) t2 on t1.advisor_id=t2.advisor_id

I advice you to see here: GROUP BY[^]

and I used inner join here, but you may need to use outer join if t1 returns no rows. see here: Outer Join[^]

这篇关于如何查询此条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！