输入
+------------+----------+-----------+-------+-------------+
| cardNo | userName | tablename | hours | date |
+------------+----------+-----------+-------+-------------+
| 1 | a | a | 12 | 12-06-2015 |
| 1 | a | a | 5 | 11-06-2015 |
| 2 | b | b | 3 | 15-06-2015 |
| 1 | a | a | 8 | 12-06-2015 |
| 2 | b | b | 3 | 21-06-2015 |
| 1 | a | a | 12 | 14-06-2015 |
| 2 | b | b | 10 | 8-06-2015 |
+------------+----------+-----------+-------+-------------+
cardNo是唯一的。我需要显示每张卡的所有详细信息和总小时数,如:
期望输出
+--------+----------+-----------+-------------+
| cardNo | userName | tablename | totalhours |
+--------+----------+-----------+-------------+
| 1 | a | a | 37 |
| 2 | b | b | 16 |
+--------+----------+-----------+-------------+
我的查询
SELECT cardNo,sum(hours)
FROM yourtable
GROUP BY cardNo;
但我需要totalHours>20是"N"。
SELECT cardNo,sum(hours)
FROM yourtable where totalHours>=20
GROUP BY cardNo;
我找不到正确的答案。如果我指定totalHours>1它给出了正确的答案;我能做什么?此外,我还需要用于该查询的PHP获取代码。
使用HAVING
子句:
SELECT cardNo,sum(hours) as TotalHours
FROM yourtable
WHERE date BETWEEN fromdate AND todate
GROUP BY cardNo
HAVING sum(hours) >=20
使用WHERE子句,您可以获得每个cardNo在这些日期之间的总小时数。