计数单行中以逗号分隔的唯一id - Count unique id separeted by commas in single row

Count unique id separeted by commas in single row

本文关键字：分隔唯一 id 单行中 | 更新日期: 2023-09-27

我在MySQL中有以下详细信息

   user_id                  follow_user_id                                                    
   1                      2,3,3,3,3
   5                      1,2,3,3,3,3
   6                      1,2,3,3,3,3

我写了下面的代码来获得唯一的代码，如下所示:

SELECT LENGTH( follow_user_id ) - LENGTH( REPLACE( follow_user_id, ',', '' ) ) +1 AS no_of_follow FROM follow WHERE user_id =1;

但是它提供了结果我需要完全唯一的行:即:4

除了DB设计问题之外，您可以在PHP中使用获取行到$result:

count(array_unique(explode(",",$result["follow_user_id")));

$query="SELECT follow_user_id FROM follow WHERE user_id ='".$_POST['user_id']."’";美元$ query_run = mysql_query(查询);(行=作用是美元query_run);

$ count = count (array_unique(爆炸("、",$ row [' follow_user_id '])));

数美元;

这样更好更快;)

count(array_flip(explode(",", $result["follow_user_id")));

或者在SQL中:-

SELECT COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(follow_user_id, ',', units.i + tens.i * 10), ',', -1) AS col1)
FROM sometable
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
WHERE user_id = 1

(最多可容纳100个以逗号分隔的值)。

但是如果有一个正确的规范化的数据库设计，这就容易多了