mysql - SQL计算不同列值

Question

我有一个名为spider_status的表，其中有一个status_code和一个locationid。我正在从 Region_select_city 中检索我在下面的代码中加入的所有城市。现在我试图计算表 Spider_status 中按城市分组的 status_code 中的所有唯一值。

SELECT region_select_city.name as stad , COUNT(spider_status.id) as spider_aantal,
CASE WHEN spider_status.status_code = 1 THEN COUNT(spider_status.status_code) ELSE 0  END as LIVE
FROM
spider_status
 JOIN 
location 
ON spider_status.location_id = location.id
 JOIN
location_select_addressid 
ON location.id = location_select_addressid.locationid
JOIN
location_address
ON location_select_addressid.addressid = location_address.id 
JOIN
region_select_city 
ON location_address.city_id = region_select_city.id
JOIN
spider_status_code
ON spider_status.status_code = spider_status_code.id 
WHERE spider_status.current = 1
GROUP BY region_select_city.name 

Answer 1

希望以下代码能帮助您解决问题

SELECT count(DISTINCT s.status_code) 
FROM spider_status AS s 
JOIN location AS l
ON l.locationid = s.locationid
JOIN location_address AS loc_add
ON loca_add.locationid = loc_add.locationid
GROUP BY loc_add.city_id