选择一些但计算所有MySQL

select count(*) HOW_MANY_TOT,
       sum(case EASYTOSING when 'Yes' then 1
                else 0
            end case) HOW_MANY_EASY
       ARTIST,
       TITLE
  from TRACKS
 group by ARTIST, TITLE
having HOW_MANY_EASY > 0

SELECT
  Artist,
  Title,
  COUNT(*) How_many_tot,
  SUM(Easytosing='yes') how_many_easy
FROM
  tracks
GROUP BY
  Artist, Title
HAVING
  SUM(Easytosing='yes')>0

请看小提琴 here .

我建议您进行两次查询,一次查询数据,一次进行计数。您正在使用 select * 这意味着您要选择整个表,如果您只需要一个数字,这将是大量的开销。

我已经建立了一个网店,在类似的情况下,两个查询的速度要快得多,分配也更容易维护。只需选择1列并删除 order by .

另一种解决方案是对count_value使用子查询。子查询不支持限制,但计数不需要限制。只需尽可能少地选择,无需订购

你必须向你的talbe添加一个唯一的ID(auto_increment,在我的查询中它调用ID),然后你就可以用以下方式获得你想要的信息:

select tall.artist, tall.title, count(distinct tall.ID) as total, count(distinct teasy.ID) as easy from tracks as tall
left join tracks as teasy on teasy.artist=tall.artist and teasy.title=tall.title and teasy.easytosing='Yes'
group by tall.artist, tall.title

您是否尝试过将其作为子查询并添加where子句?

SELECT countTable.*, 
       count(*) as How_many_easy
FROM 
(
    SELECT Artist, 
           Title, 
           MAX(easytosing) as easytosing,
           count(*) as How_many_total 
    from tracks
    group by artist,title 
) countTable ON tracks.Artist
where easytosing='Yes'
group by artist,title 
order by artist asc

注意:如果存在此类记录,则MAX(easytosing)应返回“是”,否则应返回“否”。