如何计算SQL联合中的不同值？

发布于 2024-10-15 05:35:10 字数 382 浏览 2 评论 0原文

我可以从两个不同的列中选择不同的值，但不知道如何计算它们。我的猜测是我应该使用别名，但无法弄清楚如何正确编写语句。

$sql =  "SELECT DISTINCT author FROM comics WHERE author NOT IN 
                ( SELECT email FROM bans ) UNION
                SELECT DISTINCT email FROM users WHERE email NOT IN 
                ( SELECT email FROM bans ) ";

Edit1：我知道我可以在 php 中使用 mysql_num_rows() ，但我认为这需要太多处理。

原文

I can select distinct values from two different columns, but do not know how to count them.
My guess is that i should use alias but cant figure out how to write statement correctly.

$sql =  "SELECT DISTINCT author FROM comics WHERE author NOT IN 
                ( SELECT email FROM bans ) UNION
                SELECT DISTINCT email FROM users WHERE email NOT IN 
                ( SELECT email FROM bans ) ";

Edit1: i know that i can use mysql_num_rows() in php, but i think that takes too much processing.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

触ぅ动初心 2024-10-22 05:35:10

您可以将查询包装在子查询中：

select  count(distinct author)
from    (
        SELECT  author
        FROM    comics 
        WHERE   author NOT IN ( SELECT email FROM bans ) 
        UNION ALL
        SELECT  email 
        FROM    users 
        WHERE   email NOT IN ( SELECT email FROM bans )
        ) as SubQueryAlias

查询中有两个不同的查询，并且联合会过滤掉重复项。我删除了所有三个（非独特联合是union all），并使用count(distinctauthor)将独特性移至外部查询。

You could wrap the query in a subquery:

select  count(distinct author)
from    (
        SELECT  author
        FROM    comics 
        WHERE   author NOT IN ( SELECT email FROM bans ) 
        UNION ALL
        SELECT  email 
        FROM    users 
        WHERE   email NOT IN ( SELECT email FROM bans )
        ) as SubQueryAlias

There were two distincts in your query, and union filters out duplicates. I removed all three (the non-distinct union is union all) and moved the distinctness to the outer query with count(distinct author).

回复收藏 0 原文