如何加入这两个SQL查询？

问题描述：

我正在使用MySQL和MSSql，并试图将这两个查询结合在一起。如何加入这两个SQL查询？

查询1

(SELECT REP.REP_NUM, REP.FIRST_NAME, REP.LAST_NAME 
FROM REP, CUSTOMER)

查询2

(SELECT CUSTOMER.REP_NUM, SUM(CUSTOMER.BALANCE) AS REP_BALANCE 
FROM CUSTOMER 
GROUP BY CUSTOMER.REP_NUM)

我已经看到了你可以把它们作为两个表，并加入他们的行列，但我无法得到它的工作。我试图加入他们的方式，我会得到汇总错误，试图选择代表首字母和姓，而使用余额总和。

在此先感谢！

答

SELECT r.REP_NUM, r.FIRST_NAME, r.LAST_NAME, SUM (c.BALANCE) AS REP_BALANCE 
FROM REP r 
     INNER JOIN CUSTOMER c ON r.REP_NUM = c.REP_NUM 
GROUP BY r.REP_NUM, r.FIRST_NAME, r.LAST_NAME

这非常完美，我只是混淆我的代码一样，但只有一个谷分组分组所有的倍数应该工作，因为我刚刚输出了很多倍数。 –

答

试试这个：

SELECT REP.REP_NUM, REP.FIRST_NAME, REP.LAST_NAME 
FROM REP join(
SELECT CUSTOMER.REP_NUM, SUM(CUSTOMER.BALANCE) AS REP_BALANCE 
FROM CUSTOMER 
GROUP BY CUSTOMER.REP_NUM 
) as B on some_condition...

答

SELECT R.REP_NUM, R.FIRST_NAME, R.LAST_NAME 
FROM REP r 
inner join 
(SELECT c.REP_NUM, SUM(c.BALANCE) AS REP_BALANCE 
FROM CUSTOMER c 
GROUP BY c.REP_NUM) t 
on r.rep_num = t.rep_num

答

尝试

select a.REP_NUM,a.FIRST_NAME,a.LAST_NAME,b.REP_NUM,Sum(b.BALANCE) as REP_BALANCE from REP a as inner join CUSTOMER b on a.REP_NUM=b.REP_NUM group by b.REP_NUM

答

Select New.REP_NUM,New.FIRST_NAME,New.LAST_NAME,CUSTOMER.REP_NUM, 
    SUM(CUSTOMER.BALANCE) AS REP_BALANCE 
    from (SELECT REP.REP_NUM, REP.FIRST_NAME, REP.LAST_NAME 
    FROM REP, CUSTOMER) New 
    inner join CUSTOMER ON CUSTOMER.REP_NUM=New.REP_NUM 
    GROUP BY CUSTOMER.REP_NUM

如何加入这两个SQL查询？

相关推荐