mysql - SQL select count for more than one like

Question

如果它是单计数条件，我知道如何让它工作。如何让它适用于多种条件？

SELECT count(TableName.DeviceName where DeviceName like 'AR%' ) as DEVICE_Type_A,
       count(TableName.DeviceName where DeviceName like 'R%' ) as DEVICE_Type_B,
       count(TableName.DeviceName where DeviceName like 'P%' ) as DEVICE_Type_C,
       count(TableName.DeviceName where DeviceName like 'AM%' ) as DEVICE_Type_D,
  FROM DB.TableName TableName
 WHERE TableName.DURATIONMIN > '180'

Answer 1

你应该使用 case 语句!

SELECT count(case when DeviceName like 'AR%' then 1 end) as DEVICE_Type_A,
       count(case when DeviceName like 'R%' then 1 end) as DEVICE_Type_B,
       count(case when DeviceName like 'P%' then 1 end) as DEVICE_Type_C,
       count(case when DeviceName like 'AM%' then 1 end) as DEVICE_Type_D
FROM DB.TableName TableName
WHERE TableName.DURATIONMIN > '180' 

我把计数留在里面了。我个人认为“总和”更清楚:

SELECT sum(case when DeviceName like 'AR%' then 1 else 0 end) as DEVICE_Type_A,
       sum(case when DeviceName like 'R%' then 1 else 0 end) as DEVICE_Type_B,
       sum(case when DeviceName like 'P%' then 1 else 0 end) as DEVICE_Type_C,
       sum(case when DeviceName like 'AM%' then 1 else 0 end) as DEVICE_Type_D
FROM DB.TableName TableName
WHERE TableName.DURATIONMIN > '180'