我有这样的疑问：
select *, (CAST (ie_usage_count as float)/total_count)*100 as percent_ie from(
SELECT DISTINCT CAST (account_id AS bigint),
count(case when
user_agent LIKE '%MSIE 7%'
AND user_agent NOT LIKE '%Trident%'
then 1 end) as ie_usage_count,
count(*) as total_usage
FROM acc_logs
WHERE account_id NOT LIKE 'Account ID '
group by account_id
ORDER BY account_id )
where not ie_usage_count = 0
这给了我一个包含帐户ID的表，以及与每个帐户ID相关联的ie使用计数、总使用量和百分比
account_id | ie_usage_count | total_usage | percent_ie
I have another query
select name, account_id
from accounts
这给了我与每个帐户相关联的人的名字。
姓名账户id|
我想有一个单一的查询，包括名称，帐户id，ie用法计数，总用法和百分比。
名称|帐户| id |即使用量|计数|总使用量|百分比|即
有什么想法吗？
最佳答案
您的第一个查询更容易写成：
select CAST(account_id AS bigint),
SUM(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 1 else 0
end) as ie_usage_count,
count(*) as total_usage,
AVG(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 100.0 else 0
end) as percent_ie
from acc_logs
where account_id NOT LIKE 'Account ID '
group by account_id
having SUM(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 1 else 0
end) <> 0;
只需将其加入即可获得该名称：
select CAST(al.account_id AS bigint), a.name,
SUM(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 1 else 0
end) as ie_usage_count,
count(*) as total_usage,
AVG(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 100.0 else 0
end) as percent_ie
from acc_logs al join
accounts a
on al.account_id = a.account_id
where al.account_id NOT LIKE 'Account ID '
group by al.account_id, a.name
having SUM(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%'
then 1 else 0
end) <> 0;
关于sql - 如何在Postgresql中加入这些表？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25087031/