添加驱动表的列值

Question

我需要选择不被多个列、Last_name、First_Name重复的条目。我使用COUNT(First_Name)作为SELECT的一部分来验证结果有多少条目。

SELECT Last_Name, First_Name, COUNT(First_Name) 
FROM table_of_people_and_their_data 
GROUP BY LAST_Name HAVING COUNT(First_Name) = 1;

-提供所有个别条目和作为支票的条目的计数。

SELECT Last_Name, First_Name, COUNT(First_Name) 
FROM table_of_people_and_their_data 
GROUP BY LAST_Name HAVING COUNT(First_Name) > 1;

-给出所有有复本的条目和复本的计数。

都在同一张桌子上。

table_of_people_and_their_data有许多列。只按名字和姓氏排序。

现在，count(table_of_people_and_their_data)生成957行。 中转站、中转站等 给出的准、顺、准、准 共查询479行，共查询479行，共查询479行。957 - 120 = 837

我给出的查询生成了一个表，其中包含三个列: Last_Name、First_Name、COUNT(First_Name)

类似于：

        Last_Name | First_Name | Count
          Jack        Frost        3
         Sally        Soft         4

诸若此类。我想，如果我添加Count列，我可以证明前面的查询，因为计数列的所有结果加起来应该是837。

如何在一个查询中实现这一点；使用上面提供的查询修改如下：

SELECT Last_Name, First_Name, SUM(mycount) 
FROM COUNT(First_Name) mycount FROM table_of_people_and_their_data 
GROUP BY LAST_Name HAVING COUNT(First_Name) > 1;

在使用别名和驱动表方面，我尝试了许多不同的方法。大多数我的错误都是错误1248 (42000)：每个派生表都必须有自己的别名。

谢谢你抽出时间。

Answer 1

就像这样：-

Select   srce,first_name,last_name,Obs,CumulativeTotal
from
(
select   srce,first_name,last_name,Obs,rn,@rt:=@rt+Obs as CumulativeTotal
from
(
SELECT  1 as srce,FIRST_NAME,LAST_NAME,COUNT(*) as Obs, @rn:=@rn + 1 as rn
FROM        TBL_BORROWERS , (select @rn:=0) rb
group   by  first_name, last_name having (count(*) = 1)
) s
,(select @rt:=0) rt
) t

union all
select   srce,'Unique Csutomers', '' ,count(distinct first_name,last_name),count(distinct first_name,last_name) 
from
(
select   2 as srce,first_name,last_name,Obs,rn,@rt:=@rt+Obs as CumulativeTotal
from
(
SELECT  1 as srce,FIRST_NAME,LAST_NAME,COUNT(*) as Obs, @rn:=@rn + 1 as rn
FROM        TBL_BORROWERS , (select @rn:=0) rb
group   by  first_name, last_name having (count(*) = 1)
) s
,(select @rt:=0) rt
) t

union all
Select   srce,first_name,last_name,Obs,CumulativeTotal
from
(
select   srce,first_name,last_name,Obs,rn,@rt1:=@rt1+Obs as CumulativeTotal
from
(
SELECT  3 as srce,FIRST_NAME,LAST_NAME,COUNT(*) as Obs, @rn1:=@rn1 + 1 as rn
FROM        TBL_BORROWERS , (select @rn1:=0) rn
group   by  first_name, last_name having (count(*) > 1)
) s
,(select @rt1:=0) rt1

) t

union all
select   srce,'Duplicate Customers', '' ,count(distinct first_name,last_name),count(distinct first_name,last_name) 
from
(
select   4 as srce,first_name,last_name,Obs,rn,@rt:=@rt+Obs as CumulativeTotal
from
(
SELECT  1 as srce,FIRST_NAME,LAST_NAME,COUNT(*) as Obs, @rn:=@rn + 1 as rn
FROM        TBL_BORROWERS , (select @rn:=0) rb
group   by  first_name, last_name having (count(*) > 1)
) s
,(select @rt:=0) rt
) t


UNION    
SELECT  9 as srce,'Distinct Customers' as first_name,'' as last_name,
            COUNT(DISTINCT FIRST_NAME,LAST_NAME),
            COUNT(DISTINCT FIRST_NAME,LAST_NAME)
FROM        TBL_BORROWERS
UNION    
SELECT  10 as srce,'Total Rows' as first_name,'' as last_name,
            COUNT(*),
            COUNT(*)
FROM        TBL_BORROWERS