mysql - 坚持使用复杂的 MySQL 查询语法(将表与自身连接？)

Question

我在包含大约 380 万行的 MySQL 表中有一些语言数据(几乎所有字段都有索引):

+---------+-----------+----------+--------+----------------+----------+--------+---------+---------+
| theWord | lcTheWord | spelling | thePOS | theUSAS        | register | period | variety | theDate |
+---------+-----------+----------+--------+----------------+----------+--------+---------+---------+
| to      | to        | l        | TO     | Z5             | p        |      1 | b       |    1608 |
| direct  | direct    | l        | VVI    | M6             | p        |      1 | b       |    1608 |
| others  | others    | l        | NN2    | A6.1-/Z8       | p        |      1 | b       |    1608 |
| .       | .         | o        | .      | PUNC           | p        |      1 | b       |    1608 |
| Both    | both      | u        | DB2    | N5             | p        |      1 | b       |    1608 |
| his     | his       | l        | APPGE  | Z8m            | p        |      1 | b       |    1608 |
| eyes    | eyes      | l        | NN2    | B1             | p        |      1 | b       |    1608 |
| are     | are       | l        | VBR    | A3+            | p        |      1 | b       |    1608 |
| never   | never     | l        | RR     | T1/Z6          | p        |      1 | b       |    1608 |
| at      | at        | l        | RR21   | N3.8+[i281.2.1 | p        |      1 | b       |    1608 |

所以同一个词可以(而且经常会)多次出现在表格中，有些用“l”代表小写，有些用“u”代表大写。

我现在想比较各个单词在不同时间段(例如 1 与 8)、变体(“b”= 英式英语，“a”= 美式英语)等方面的大小写，方法是创建按大写与小写拼写的比例。我会在某个阶段还想将数据限制为某些词性标签 (thePOS) 或语义标签 (theUSAS)。

不幸的是，我在 SQL 方面的知识非常有限——尽管我已经尝试了很多事情(例如，将表与自身连接并尝试从那里解决问题)，但到目前为止我都失败了。

只是举个例子，说明我一直在尝试的事情:

SELECT l.theWord, count(l.theWord) as freq_low, count(u.theWord) as freq_up
FROM table_name l
INNER JOIN table_name u ON l.lcTheWord = u.lcTheWord
group by l.lcTheWord;

这显然是错误的方法，因为它似乎没有使用必要的索引(而且我花了太长时间才看到它做了什么......)

我意识到这个问题远没有指南建议的那么具体。道歉!但是，我想知道是否有好心人可以给我一些指示，以便我可以从那里继续......？

非常感谢!塞巴斯蒂安

Answer 1

我认为您在这里不需要自联接 - GROUP BY 应该就足够了。您可以计算 'u' 和 'l' 在 spelling 列中的单词数，如下所示:

SELECT
    lcTheWord
,   SUM(CASE spelling WHEN 'u' THEN 1 ELSE 0 END) AS UpperCount
,   SUM(CASE spelling WHEN 'l' THEN 1 ELSE 0 END) AS LowerCount
FROM table_name
GROUP BY lcTheWord