我要说得更清楚一点,因为我意识到它并不像它应该的那样清楚。
表一
ID, COMPANY_ID, OPPORTUNITY, DATE_CREATE
表二
ID,ASSIGNED_BY_ID
我正在尝试做的是为每个ASSIGNED_ID添加机会中的所有值。 表 1 中的 COMPANY_ID 与表 2 中的 ID 相同。
所以例如:
表一
COMPANY_ID | OPPORTUNITY | DATE_CREATE
1000 | 50 | 2013/09/19
1000 | 100 | 2013/09/18
1000 | 200 | 2013/09/18
1005 | 100 | 2013/09/18
1005 | 200 | 2013/09/18
表二
ID | ASSIGNED_BY_ID
1000 | 4
1000 | 4
1000 | 4
1005 | 2
1005 | 2
所以我想要一个 SELECT 语句来提供这些结果:
ASSIGNED_BY_ID | OPPORTUNITY
4 | 350
2 | 300
我希望每个ASSIGNED_BY_ID单独选择语句。
这怎么可能?
试试这个。
SELECT t1.ID,SUM(OPPORTUNITY) AS total
FROM table1 t1
INNER JOIN table2 t2 ON t1.COMPANY_ID=t2.ID
WHERE t1.DATE_CREATE BETWEEN '$fromdate' AND '$todate' AND t2.ASSIGNED_BY_ID=1
GROUP BY t1.ID
更新
SELECT t2.ASSIGNED_BY_ID,SUM(OPPORTUNITY) AS total
FROM table1 t1
INNER JOIN table2 t2 ON t1.COMPANY_ID=t2.ID
WHERE t1.DATE_CREATE BETWEEN '$fromdate' AND '$todate'
GROUP BY t2.ASSIGNED_BY_ID
请使用这个
SELECT SUM(a.OPPORTUNITY) AS total, a.ID FROM table1 a inner join table2 b on a.COMPANY_ID = b.ID WHERE a.DATE_CREATE BETWEEN '$fromdate' AND '$todate' AND b.ASSIGNED_BY_ID=1 group by a.ID;