I have table:
我有表:
+----------------+
| table |
+----------------+
| u_id | sail_id |
+----------------+
| 1 | 5 |
| 1 | 5 |
| 2 | 5 |
| 2 | 4 |
| 1 | 4 |
+----------------+
How to write sql statement to count different u_id with different sail_id (means no duplicate)?
如何使用不同的sail_id来计算不同的u_id(意味着没有副本)?
Example: if SELECT COUNT(*) FROM table GROUP BY sail_id, result will be 2
示例:如果通过sail_id从表组中选择COUNT(*)，结果将是2
if SELECT COUNT(*) FROM table GROUP BY sail_id, user_id, result will be 1
如果通过sail_id, user_id，从表组中选择COUNT(*)，结果将是1。
I need result to be 4 (because there are 5 rows and only first and second rows have same u_id and sail_id). Maybe I need add somewhere DISTINCT.
我需要结果为4(因为有5行，只有第一行和第二行具有相同的u_id和sail_id)。也许我需要添加一些不同的东西。
1 个解决方案
#1
3
1) You can use COUNT(DISTINCT ...):
1)你可以使用COUNT(明显的…):
SELECT COUNT(DISTINCT u_id,sail_id)
FROM tab;
SqlFiddleDemo
2) You can use subquery with DISTINCT:
2)您可以使用具有不同的子查询:
SELECT COUNT(*)
FROM (SELECT DISTINCT u_id, sail_id
FROM table) AS sub;
LiveDemo
3) You can use subquery with GROUP BY:
3)您可以使用subquery with GROUP BY:
SELECT COUNT(*)
FROM (SELECT u_id, sail_id
FROM table
GROUP BY u_id, sail_id) AS sub;
4) Last possibility is to use:
最后一种可能是:
SELECT COUNT(DISTINCT CONCAT(u_id,',',sail_id))
FROM table;
SqlFiddleDemo
#1
3
1) You can use COUNT(DISTINCT ...):
1)你可以使用COUNT(明显的…):
SELECT COUNT(DISTINCT u_id,sail_id)
FROM tab;
SqlFiddleDemo
2) You can use subquery with DISTINCT:
2)您可以使用具有不同的子查询:
SELECT COUNT(*)
FROM (SELECT DISTINCT u_id, sail_id
FROM table) AS sub;
LiveDemo
3) You can use subquery with GROUP BY:
3)您可以使用subquery with GROUP BY:
SELECT COUNT(*)
FROM (SELECT u_id, sail_id
FROM table
GROUP BY u_id, sail_id) AS sub;
4) Last possibility is to use:
最后一种可能是:
SELECT COUNT(DISTINCT CONCAT(u_id,',',sail_id))
FROM table;
SqlFiddleDemo