Question

我想一提两个不同的情况。

SELECT COUNT(*) AS count FROM table_name WHERE name = ?

以及

SELECT COUNT(*) as count FROM table_name WHERE address = ? AND port = ?

I need to have a count for rows that have a certain address 以及 certain port, 以及 a SEPARATE count for rows that have a certain name.

我知道我可以这样做。

SELECT (COUNT*) as count FROM table_name WHERE (address = ? AND port = ?) OR name = ?

However that is a single count, 以及 I need them to be separate so I can display a more accurate message to the user.

我怎么做? 希望!

Answer 1

What about simply:

SELECT 
    SUM(IF(name = ?, 1, 0)) AS name_count,
    SUM(IF(address = ? AND port = ?, 1, 0)) AS addr_count
FROM 
    table_name

Answer 2

SELECT SUM(CASE WHEN Name = ? THEN 1 ELSE 0 END) as name_match
       , SUM(CASE WHEN Address = ? AND Port = ? THEN 1 ELSE 0 END) as address_match
FROM table_name
WHERE (address = ? AND port = ?) OR name = ?

Answer 3

SELECT 
  COUNT( CASE WHEN n1 =  J  THEN 1 END ) AS t1,
  COUNT( CASE WHEN n2 =  C  THEN 1 END ) AS t2,
  COUNT( CASE WHEN n3 =  K  THEN 1 END ) AS t3 
FROM test

利用COUNT(CASE...),即使两者的条件不同,也可从单张桌上计算两栏(例如:从一栏中计算J,从一栏中计算C,等等)。

表:测试

+----+----+----+----+
| id | n1 | n2 | n3 |
|----+----+----+----+
|  1 | J  | C  | K  |
|----+----+----+----+
|  1 | J  | C  | F  |
|----+----+----+----+
|  1 | J  | K  | C  |
|----+----+----+----+
|  1 | K  | K  | C  |
|----+----+----+----+

Result:

+----+----+----+
| t1 | t2 | t3 |
|----+----+----+
|  3 | 2  | 1  |
|----+----+----+

Answer 4

Might be easiest only to do a Union:

SELECT COUNT(*) AS count FROM table_name WHERE name = ? 
    GROUP BY name
UNION ALL
SELECT COUNT(*) as count FROM table_name WHERE address = ? AND port = ?
    GROUP BY address, port

友情链接