如何做到这一点?
我有这张桌子:
表1
|--ID--|--Stars--|--Name--|
| 1 | 3 | Pe |
| 2 | 5 | Me |
| 3 | 7 | Fe |
|------|---------|--------|
表2
|--ID--|--Teams--|--Age---|--Name--|
| 1 | A | 20 | Pe |
| 2 | A | 20 | Me |
| 3 | A | 20 | Pe |
|------|---------|--------|--------|
我想要表1中的星星的总和,其中Name存在于表2中,Teams=A,Age=20。(3+5=8颗星)
我使用这个,但如何只计算表1中名称与表2中名称匹配的恒星?
$result = mysql_query("SELECT SUM(Stars) AS starsum FROM Table1 WHERE EXISTS (select * from Table2 WHERE Teams = 'A' AND Age = '20')");
$row = mysql_fetch_assoc($result);
$antalstjernerhold = $row['starsum'];
尝试此查询
SELECT SUM(t1.Stars) AS starsum
FROM Table1 t1 INNER JOIN Table2 t2
ON t1.Name=t2.Name AND t2.Teams='A' AND t2.Age=20
您必须使用inner join
才能获得所需的结果。
您可以这样编写查询:
SELECT SUM(Stars) AS starsum
FROM Table1 t1
WHERE
EXISTS (
SELECT *
FROM Table2 t2
WHERE
T2.Name=T1.Name
AND T2.Teams='A'
AND t2.Age=20
)
您可以使用以下代码
$result = mysql_query("
SELECT SUM(Stars) AS starsum
FROM Table1
WHERE Name IN (
SELECT DISTINCT t1.Name
FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name
AND t2.Teams = 'A'
AND t2.Age = 20
)
");
$row = mysql_fetch_assoc($result);
$antalstjernerhold = $row['starsum'];
该查询使用下面的子查询,该子查询将获取属于这两个表的名称,并且具有作为A和Age 20&然后使用这些名称,我们得到恒星的总和
SELECT DISTINCT t1.Name
FROM Table1 t1, Table2 t2
WHERE t1.Name = t2.Name
AND t2.Teams = 'A'
AND t2.Age = 20