sql - 此 SQL 选择代码是否遵循良好做法？

Question

我正在使用 SQLite，稍后将移植到 MySQL (5)。

我想知道我是否正在做不该做的事情。我故意尝试设计，所以我将与 0 而不是 1 进行比较(我将 hasApproved 更改为 NotApproved 来执行此操作，这没什么大不了的，而且我没有编写任何代码)。有人告诉我我永远不需要写子查询，但我在这里写了。我的 Votes 表只是 id, ip, postid(我不认为我可以将该子查询写成一个连接？)这几乎就是我的全部想法。

我不太关心命名约定，因为表是通过反射创建的，而且到处都是。

select 
    id, 
    name, 
    body, 
    upvotes, 
    downvotes, 
    (select 1 from UpVotes where IPAddr=? AND post=Post.id) as myup, 
    (select 1 from DownVotes where IPAddr=@0 AND post=Post.id) as mydown
from Post 
where 
    flag = '0'
limit ?, ?"

Answer 1

既然你问的是好的做法......你的 Posts 表中出现的“upvotes”和“downvotes”看起来就像你在复制数据库中的数据。这是个问题，因为现在你总是要担心数据是否同步和正确。如果你想知道赞成票的数量然后计算它们，不要将它们也存储在 Post 表中。我不确定你在做什么，但这是一个猜测。

关于您的查询...您可能会使用 JOINed 子查询而不是您拥有它的方式获得更好的性能。使用标量子查询作为列，它们必须为返回的每一行运行一次。如果您要返回一堆行，那可能会对性能造成很大的影响。相反，请尝试:

SELECT
    P.id,
    P.name,
    P.body,
    P.upvotes,
    P.downvotes,
    COALESCE(UV.cnt, 0) AS upvotes2,
    COALESCE(DV.cnt, 0) AS downvotes2
FROM
    dbo.Posts P
LEFT OUTER JOIN (SELECT post_id, COUNT(*) cnt FROM dbo.UpVotes GROUP BY post_id) AS UV ON UV.post_id = P.id
LEFT OUTER JOIN (SELECT post_id, COUNT(*) cnt FROM dbo.DownVotes GROUP BY post_id) AS DV ON DV.post_id = P.id

将它与您自己的查询进行比较，看看它是否会给您带来更好的性能。

编辑:其他几位发帖人提倡使用一张表进行赞成/反对票。他们是绝对正确的。这使得查询更容易，也可能更快:

SELECT
    P.id,
    P.name,
    P.body,
    P.upvotes,
    P.downvotes,
    SUM(CASE WHEN V.vote_type = 'UP' THEN 1 ELSE 0 END) AS upvotes2,
    SUM(CASE WHEN V.vote_type = 'DOWN' THEN 1 ELSE 0 END) AS downvotes2,
FROM
    dbo.Posts P
LEFT OUTER JOIN Votes V ON
    V.post_id = P.id
GROUP BY
    P.id,
    P.name,
    P.body,
    P.upvotes,
    P.downvotes