我在数据库中有以下3个表。
-
程序表
程序ID(主键)
开始日期
结束日期
IsCompleted
IsGoalsMet
程序类型_ID -
Programs_Type_Table(不同类型的程序,支持表单中的下拉列表)
程序类型_ID(主键)
程序名称
程序description -
Client_Program_Table
Client_ID(主键)
程序ID(主键)
了解特定程序(程序类型)中有多少客户端的最佳方法是什么?
下面的SQL语句是最好的方法吗,甚至是合理的方法吗?
SELECT Client_ID FROM Client_Program_Table
INNER JOIN Programs_Table
ON Client_Program_Table.Program_ID = Programs_Table.Program_ID
WHERE Programs_Table.Program_type_ID = "x"
其中"x"是我们感兴趣的特定程序的Program_type_ID。
还是以下是更好的方法?
$result = mysql_query("SELECT Program_ID FROM Programs_Table
WHERE Program_type_ID = 'x'");
$row = mysql_fetch_assoc($result);
$ProgramID = $row['Program_ID'];
$result = mysql_query("SELECT * FROM Client_Program_Table
WHERE Program_ID = '$ProgramID'");
mysql_num_rows($result) // returns how many rows of clients we pulled.
提前谢谢你,请原谅我没有经验和犯过的任何错误。
以下是如何做到这一点:
<?php
// always initialize a variable
$number_of_clients = 0;
// escape the string which will go in an SQL query
// to protect yourself from SQL injection
$program_type_id = mysql_real_escape_string('x');
// build a query, which will count how many clients
// belong to that program and put the value on the temporary colum "num_clients"
$query = "SELECT COUNT(*) `num_clients` FROM `Client_Program_Table` `cpt`
INNER JOIN `Programs_Table` `pt`
ON `cpt`.`Program_ID` = `pt`.`Program_ID`
AND `pt`.`Program_type_ID` = '$program_type_id'";
// execute the query
$result = mysql_query($query);
// check if the query executed correctly
// and returned at least a record
if(is_resource($result) && mysql_num_rows($result) > 0){
// turn the query result into an associative array
$row = mysql_fetch_assoc($result);
// get the value of the "num_clients" temporary created column
// and typecast it to an intiger so you can always be safe to use it later on
$number_of_clients = (int) $row['num_clients'];
} else{
// query did not return a record, so we have no clients on that program
$number_of_clients = 0;
}
?>
如果您想知道一个程序中涉及多少客户端,您最好使用COUNT(*)。MySQL(带有MyISAM)和SQL Server有一种快速检索行总数的方法。使用SELECT(*),mysql_num_rows会导致不必要的内存资源和计算时间。对我来说,这是编写所需查询的最快的方法,尽管不是"最干净"的方法:
SELECT
COUNT(*)
FROM
Client_Program_Table
WHERE
Program_ID IN
(
SELECT
Program_ID
FROM
Programs_Table
WHERE
Program_type_ID = 'azerty'
)
为什么?
使用JOIN可以提高查询的可读性,但子查询的计算速度通常更快。
返回特定程序类型(x)中的客户端计数:
SELECT COUNT(cpt.Client_ID), cpt.Program_ID
FROM Client_Program_Table cpt
INNER JOIN Programs_Table pt ON cpt.Program_ID=pt.Program_ID
WHERE pt.Program_type_ID = "x"
GROUP BY cpt.Program_ID