一个mySQL计数查询返回一个数值？

I have a database set up with a few hundred customers in, all of them are unique. However, some customers are linked together, if they are part of a family. E.g.

我有一个数百个客户的数据库,所有这些都是独一无二的。但是,如果某些客户是家庭成员,他们会联系在一起。例如。

I have a family ID that links these customers, e.g. Mrs A. Jones is customer number 005 and Mr B.Jones is customer number 017 and as they are related, I have assigned them family ID 001.

我有一个家庭ID,链接这些客户,例如A. Jones夫人是客户编号005,B.Jones先生是客户编号017,因为他们是相关的,我已经为他们分配了家庭ID 001。

I've searched online, but no where seems to be that useful at providing me with any assistance into how I could do some sort of mySQL count to echo the number of people WHERE familyID = 001.

我已经在网上搜索了,但似乎没有什么地方可以帮助我提供任何帮助,我可以做一些mySQL计数来回应家庭ID = 001的人数。

Also, is there any way, I could return both seperate customer records, given the mySQL search criteria of familyID = 001.

此外,有什么办法,我可以返回两个单独的客户记录,给定familyID = 001的mySQL搜索条件。

Many thanks,

Tom.

EDIT: Realised I'd put passenger instead of customers, my bad! Also, the names are made up for obvious DPA reasons :)

编辑:意识到我把乘客而不是客户,我的坏!此外,这些名称是出于明显的DPA原因:)

6 个解决方案

#1

You want the COUNT() function

你想要COUNT()函数

SELECT count(*) AS count FROM customers WHERE familyID="001"

You can replace the * with a column name, makes it a little faster.

您可以使用列名替换*,使其更快一些。

#2

$sql='SELECT count(familyID) AS count FROM customers WHERE familyID="001"';

Here you can get number of customers of familyID= 001

在这里,您可以获得familyID = 001的客户数量

But where is this passenger came from? what is the relation with customer?

但是这位乘客来自哪里?与客户的关系是什么?

If it is about customer not passenger then

如果是关于客户而不是乘客那么

 $sql='SELECT * FROM customers WHERE familyID="001"';
    $query=mysql_query($sql);
    $count=mysql_num_rows($query);
    while($fetch_arr=mysql_fetch_array($query)){
      $familyName=$fetch_arr['name'];
      echo  $familyName;
     }

You can get all records from * and from $count u can get number of customers

你可以从*获得所有记录,从$ count你可以得到客户数量

#3

You can use the count Function in such cases .it can be used as.

在这种情况下,你可以使用count函数.it可以用作。

Select count(*) form TABLE NAME where familyID = "001"

#4

If you want the unique records as well as the count use this, with the OVER function:

如果你想要唯一的记录以及计数使用这个,使用OVER函数:

SELECT customers.id, 
    customers.additional_columns, 
    count(1) OVER (PARTITION BY familyID) AS count 
FROM customers

#5

You can do something like

你可以做点什么

$result = mysql_query("SELECT * FROM families where familyID='001'", $link);
$num_rows = mysql_num_rows($result);

source

#6

-1

For your first question, this will do

对于你的第一个问题,这样做

$result = @mysql_query("SELECT * FROM tbl_name WHERE familyID = '001'");
echo mysql_num_rows($result); //This line should display the count

#1