mysql - 如何在 MySQL 中进行派生查询

Question

我试图进行一个查询，让我可以查看每个作者用法语和英语写了多少本书。好吧，我知道我必须使用两个派生查询来完成此操作，但我不知道如何连接它们。我运行后得到的结果没有任何意义......

可能有一种更简单的方法来做到这一点，但在尝试了几个小时后我无法弄清楚。

SELECT tblBooks.fldAuthor, fldFrench, fldEnglish
FROM tblBooks, (
        SELECT fldAuthor, COUNT(*) AS fldFrench
        FROM tblBooks
        WHERE fldLanguage = 'FR'
        GROUP BY fldAuthor
    ) AS French
Join
    (
        SELECT fldAuthor, COUNT(*) AS fldEnglish
        FROM tblBooks
        WHERE fldLanguage = 'EN'
        GROUP BY fldAuthor
    ) AS English
ON French.fldAuthor = English.fldAuthor;

这是输出:

我不知道是否可以导入我的数据库，但这是基础:

CREATE TABLE tblBooks(
    fldAuthor   CHAR(30),
    fldTitle    CHAR(30),
    fldLanguage CHAR(2));


INSERT INTO tblbooks
VALUES('Markus', 'abc', 'EN');

INSERT INTO tblbooks
VALUES('Santa', 'dfs', 'EN');

INSERT INTO tblbooks
VALUES('Santa', 'bcd', 'EN');

INSERT INTO tblbooks
VALUES('Santa', 'fge', 'FR');

Answer 1

这个例子是在 SQL Server 中完成的，但这个概念也将同样转化为 MySQL。

--create a populated temp table
select *
into #tblBooks
from
(
  values
  ('Book 1', 'Charlie Brown', 'EN'),
  ('Book 2', 'Mickey Mouse', 'EN'),
  ('Livre 3', 'Jacques Clouseau', 'FR'),
  ('Book 4', 'Charlie Brown', 'EN'),
  ('Livre 5', 'Jacques Clouseau', 'FR'),
  ('Book 6', 'Charlie Brown', 'EN'),
  ('Livre 7', 'Pepe le Pew', 'FR'),
  ('Book 8', 'Jacques Clouseau', 'EN'),
  ('Book 9', 'Charlie Brown', 'EN'),
  ('Livre 10', 'Charlie Brown', 'FR')
) d (fldBookName, fldAuthor, fldLanguage);

--look at data in temp table
select * from #tblBooks;

--calculate number of French books and number of English books for each author
select
fldAuthor,
fldFrench = sum(case fldLanguage when 'FR' then 1 else 0 end),
fldEnglish = sum(case fldLanguage when 'EN' then 1 else 0 end)
from #tblBooks
group by
fldAuthor;

这是结果 -
#tblBooks 的内容:

总结:

如果 MySQL 有 IF，也可以用它替换 CASE 语句，以稍微缩短它。

上面的版本是我解决这个问题的方法，因为它非常简洁。然而，这里有一个版本(也是在 SQL Server 中完成的)，它使用派生表来实现与上面所示相同的汇总结果:

select distinct
b.fldAuthor,
fldFrench = isnull(f.fldFrench, 0), --case when f.fldFrench is null then 0 else f.fldFrench end,
fldEnglish = isnull(e.fldEnglish, 0) --case when e.fldEnglish is null then 0 else e.fldEnglish end
from #tblBooks b
left outer join
  (
    select fldAuthor, fldFrench = count(1)
    from #tblBooks
    where fldLanguage = 'FR'
    group by fldAuthor
  ) f on b.fldAuthor = f.fldAuthor
left outer join
  (
    select fldAuthor, fldEnglish = count(1)
    from #tblBooks
    where fldLanguage = 'EN'
    group by fldAuthor
  ) e on b.fldAuthor = e.fldAuthor;

不确定 MySQL 是否有等效的 ISNULL，如果没有，则只需用上面所示的 CASE 语句替换为内联注释。这样做的目的是当没有找到给定语言的给定作者的书籍时显示零而不是 NULL。