按平台选择和计数不同的活动用户，并比较它们是否存在于其他表中 [英] Select and count distinct active users by platform and compare if they exist in other tables

问题描述

我大约有六（6）个表，每个表都与userid链接。表之一是userinfo。用户信息包含用户详细信息，包括其商店平台（例如magento）

I have about six(6) tables each linked with userid. One of the tables is userinfo. The user info contains user details including their store platform(eg magento)

1. Userinfo包含活动用户和非活动用户（已创建活动用户）我希望在使用magento平台的userinfo中，在其他任何表中都有记录的用户信息中统计不同数量的用户。




3. 当前，我可以使用ff代码在其他五个表中计算出不同的用户数，但希望将其与userinfo表一起加入，以便我可以选择具有平台magento的活动用户。


4. 不添加userinfo表意味着我无法按平台选择用户。



5. 仅使用magento平台在userinfo表中选择用户很容易，但这意味着我可以选择仅注册但不继续在我的应用程序上创建活动的用户。

1. Userinfo contains both active and non-active users (active users have created at least one activity in the other 5 tables).
2. I want to count distinct number of users in the userinfo with platform of magento who have records in any of the other tables.
3. Currently I am able to count distinct number of users in the other five tables with the ff code but want to join this with the userinfo table so I can select active users with platform magento.
4. Without adding the userinfo table means I have no way of selecting users by platform.

5. Selecting users in userinfo table only, with platform of magento will be easy, but that means I may select users who only register but do not go on to create activity on my app.

     $query3 = ("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");
       $result3 = mysql_query($query3) or die(mysql_error()); 
       $row3 = mysql_fetch_row($result3);
       echo "Number of distinct users in all tables  = ".$row3[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

  **userinfo**
  Id  userid   username   password   platform

推荐答案

扩展 UNION 从我的其他建议中选择 ，您可以 JOIN 与 UserInfo 表并获得不同的计数。

Expanding on the UNION subselect from my other suggestion, you can JOIN this with the UserInfo table and get your distinct count.

SELECT   COUNT (DISTINCT ui.UserID))
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 id
         INNER JOIN UserInfo ui ON ui.UserID = id.UserID
WHERE    ui.Platform = 'Magento'

这篇关于按平台选择和计数不同的活动用户，并比较它们是否存在于其他表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！