oracle 需要使用复合主键计算不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29913755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need to count distinct values with a composite primary key
提问by Daniel Morris
I have to generate a report that displays the name of different locations and the number of times unique course offerings are offered in these locations. The report needs to only indicate the city and the no. of unique course offerings and be sorted by the highest number of unique offerings. The problem I have is the offering table has a composite primary key (offering begin date and course code)
我必须生成一份报告，显示不同地点的名称以及在这些地点提供独特课程的次数。报告只需要注明城市和编号。的独特课程，并按最高数量的独特课程排序。我的问题是课程表有一个复合主键（课程开始日期和课程代码）
I run this SQL query and it comes up with an error as you can't count multiple columns. But I am not sure how to split the count.
我运行这个 SQL 查询，它出现了一个错误，因为你不能计算多个列。但我不确定如何拆分计数。
SELECT
offlocation AS city, COUNT(distinct offbegindate,crscode) as no. of unique course offerings
FROM
offering
GROUP BY offlocation
ORDER BY COUNT(distinct offbegindate,crscode) as no. of unique course offerings DESC;
Any help will be greatly appreciated.
任何帮助将不胜感激。
回答by jarlh
Just minor adjustments, "Row types" for count distinct:
只是细微的调整，计数不同的“行类型”：
SELECT
offlocation AS city,
COUNT(distinct (offbegindate,crscode)) as no_of_unique_course_offerings
FROM
offering
GROUP BY offlocation
ORDER BY no_of_unique_course_offerings DESC;
回答by Janis Baiza
You can try to concatenate values:
您可以尝试连接值：
SELECT
offlocation AS city, COUNT(distinct offbegindate || '-' || crscode) as no. of unique course offerings
FROM
offering
GROUP BY offlocation
ORDER BY COUNT(distinct offbegindate,crscode) as no. of unique course offerings DESC;
回答by uhs
Hope this would work:
希望这会奏效：
select offlocation AS city , count(*) as cnt from
(
select distinct offlocation,offbegindate,crscode from offering
) v
group by city
order by cnt desc;
回答by C.Nuwan
select count( distinct CONCAT( offbegindate, '-' , crscode ) ) from cgtask.tsk_proj_cost;
从 cgtask.tsk_proj_cost 中选择 count( distinct CONCAT( offbegindate, '-' , crscode ) );
