计算来自多个表的不同用户ID [英] Counting distinct user id's from multiple tables

问题描述

我大约有5个表，这些表的公共字段为用户ID"，我想在所有这些表中计算不同的用户ID.某些用户ID可能出现在这些表中的大约2个中.但是我想将5个表中不同用户ID的UNION数在一起.我可以用ff代码在一张表中计算不同的用户ID.我希望能够从所有表中计数.

I have about 5 tables with common field "userid" I want to count distinct userids across all these tables. Certain user ids may occur in about 2 of these tables. But I want to count the UNION of distinct user ids in the 5 tables together. I am able to count distinct userids in one table with the ff code. I want to be able count from all tables.

//just count the nuber of distict userids in table1
    $query2 = ("SELECT COUNT(DISTINCT userid) FROM table1"); 
    $result2 = mysql_query($query2) or die(mysql_error()); 
    $row2 = mysql_fetch_row($result2); 
    echo "Number of users in table 1  = ".$row2[0] ."<br />";


**Table 1**
Id    userid    name   adresss

**Table 2**
Id  Title   Sex   userid

**Table 3**
Id   userid   amount

**Table 4**
Id  price  promotion  userid   productid

**Table  5**
Id  userid   category   tax   weight

推荐答案

使用UNION

与UNION ALL相比，标准UNION将从结果集中删除重复的值.您的声明看起来像

A standard UNION, in contrast with a UNION ALL, will remove duplicate values from the resultset. Your statement would look like

SELECT   COUNT(*)
FROM     (
           SELECT UserID FROM Table1
           UNION SELECT UserID FROM Table2
           UNION SELECT UserID FROM Table3
           UNION SELECT UserID FROM Table4
           UNION SELECT UserID FROM Table5
         ) AS UserIDs

来自w3schools

UNION运算符默认情况下仅选择不同的值.允许 重复的值，请使用UNION ALL.

the UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

这篇关于计算来自多个表的不同用户ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！