mysql - 当其中一个值是 SELECT 语句的结果时，如何使用 COUNT(DISTINCT...)？

Question

我有一个返回行的 SQL 语句:

SELECT DISTINCT wp_w2bw2c_event.venue_id, 
    (SELECT MIN(begin_date)     
        FROM wp_w2bw2c_event_detail 
        WHERE wp_w2bw2c_event_detail.event_id = wp_w2bw2c_event.id) 
    as begin_date, 
    wp_w2bw2c_event.id as event_id 
    FROM wp_w2bw2c_event 
    INNER JOIN wp_w2bw2c_venue 
    ON wp_w2bw2c_venue.id = wp_w2bw2c_event.venue_id 
    INNER JOIN wp_w2bw2c_event_detail 
    ON wp_w2bw2c_event_detail.event_id = wp_w2bw2c_event.id 
    WHERE wp_w2bw2c_venue.venue_name LIKE '%ironworks%' 
    OR artist_name LIKE '%ironworks%' 
    OR event_title LIKE '%ironworks%' 
    OR event_detail_title LIKE '%ironworks%' 
    ORDER BY wp_w2bw2c_event.venue_id, begin_date, event_id 

如果我尝试使用 COUNT 函数仅计算行数，则会收到 SQL 数据库错误

SELECT COUNT(DISTINCT wp_w2bw2c_event.venue_id, 
    (SELECT MIN(begin_date)     
        FROM wp_w2bw2c_event_detail 
        WHERE wp_w2bw2c_event_detail.event_id = wp_w2bw2c_event.id) 
    as begin_date, 
    wp_w2bw2c_event.id as event_id) 
    FROM wp_w2bw2c_event 
    INNER JOIN wp_w2bw2c_venue 
    ON wp_w2bw2c_venue.id = wp_w2bw2c_event.venue_id 
    INNER JOIN wp_w2bw2c_event_detail 
    ON wp_w2bw2c_event_detail.event_id = wp_w2bw2c_event.id 
    WHERE wp_w2bw2c_venue.venue_name LIKE '%ironworks%' 
    OR artist_name LIKE '%ironworks%' 
    OR event_title LIKE '%ironworks%' 
    OR event_detail_title LIKE '%ironworks%' 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as begin_date, 
    wp_w2bw2c_event.id as event_id 
    FROM wp_w2bw2c_event 
    INNE' at line 5

如何更改 SQL 以便 COUNT(DISTINCT... 处理 SELECT 的结果？

Answer 1

您的查询返回不同的行。因此，您所要做的就是计算该查询的结果:

select count(*) from (your query) q;