如何获得输出

问题描述

我有2张桌子，上面有这样的数据

表1:

ID IP帐户
1 172.22.22 SSIN
2 172.34.43 SSIN
3 1832.2.2.3 JJIN

表2:

ID帐户AccountName标志频率
1 SSIN Healthy 1 15
2 SSIN好1 15
3 SSIN不好1 15
4 JJIN Healthy 1 15
5 JJIN好1 15
6 JJIN不好0 15

我想要类似
的输出

输出:

帐户IP AccountName
SSIN 2 3
JJIN 1 2

这里的ip是帐户的计数，帐户名也是帐户的计数，并用flag = 1进行检查.

Hi,

I have 2 table with data like this

Table1:

ID IP Account
1 172.22.22 SSIN
2 172.34.43 SSIN
3 1832.2.2.3 JJIN

Table2:

ID Account AccountName Flag Frequency
1 SSIN Healthy 1 15
2 SSIN Good 1 15
3 SSIN bad 1 15
4 JJIN Healthy 1 15
5 JJIN Good 1 15
6 JJIN bad 0 15

I want the output like


OutPut:

Account IP AccountName
SSIN 2 3
JJIN 1 2

Here the ip is the count of the account, and the accountname is also the count of account and checking with flag=1

推荐答案

SELECT   distinct   table2.Account,count(distinct(table1.IP)) as IP,  count(distinct(table2.AccountName)) as AccountName
FROM         table2 inner JOIN
                      table1 ON table1.Account = table2.Account where table2.Flag=1
group by   table2.Account ORDER BY table2.Account DESC

问候，
朋友

Regards,
Pal

select t1.Account, t1.IP, t2.AccountName from
(select count(*) IP,Account from Table1 group by Account)t1
Inner Join
(select count(*) AccountName, Account from Table2 group by Account)t2
on t1.Account = t2.Account

SELECT DISTINCT t2.Account, 
    (SELECT COUNT(DISTINCT t1.IP) 
     FROM Table1 t1
     WHERE t1.Account = t2.Account) as IPCount,
    (SELECT COUNT(DISTINCT t22.AccountName)
     FROM Table2 t22
     WHERE t22.Account = t2.Account AND t22.Flag = 1) as AccountNameCount       
FROM Table2 t2
ORDER BY t2.Account DESC

最好的问候，

—MRB

Best Regards,

—MRB

这篇关于如何获得输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！