postgresql 指望几个字段的 DISTINCT 仅适用于 MySQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7286266/
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
Count on DISTINCT of several fields work only on MySQL?
提问by aleroot
I need a Query that without any changes work on these three different database server : MySQL, MSSQL, PostgreSQL . In this query i have to to calculate a column with the following expression that work correctly on MySQL :
我需要一个查询，无需任何更改即可在这三个不同的数据库服务器上运行： MySQL, MSSQL, PostgreSQL 。在此查询中，我必须使用以下表达式计算一列，该列可在 MySQL 上正常工作：
COUNT(DISTINCT field_char,field_int,field_date) AS costumernum
The fields in the distinct are of different type :
distinct 中的字段具有不同的类型：
field_char = character
field_int = integer
field_date = datetime
The expression is inside a parent query select, so if i try to achieve the result with a sub query approach, i stumble in this situation :
表达式在父查询选择中，所以如果我尝试使用子查询方法获得结果，我会在这种情况下绊倒：
SELECT t0.description,t0.depnum
(select count(*) from (
select distinct f1, f2, f3 from salestable t1
where t1.depnum = t0.depnum
) a) AS numitems
FROM salestable t0
I get an error with this query, how can i get the value of the parent query ?
我收到此查询的错误，如何获取父查询的值？
The expression work correctly on MySQL but i get an error when i try to execute it on Sql Server or PostgreSQL (the problem is that the count function doesn't accept 3 arguments of different type on MSSQL/PostgreSQL), is there a way to achieve the same result with an expression that work in each of these database server (SQL Server, MySQL, PostgreSQL ) ?
该表达式在 MySQL 上正常工作，但是当我尝试在 Sql Server 或 PostgreSQL 上执行它时出现错误（问题是计数函数不接受 MSSQL/PostgreSQL 上的 3 个不同类型的参数），有没有办法使用适用于这些数据库服务器（SQL Server、MySQL、PostgreSQL）中的每一个的表达式获得相同的结果？
回答by Derek Kromm
A general way to do this on any platform is as follows:
在任何平台上执行此操作的一般方法如下：
select count(*) from (
select distinct f1, f2, f3 from table
) a
Editfor new info:
编辑新信息：
What if you try joining to the distinct list (including the dept) and then doing the count? I created some test data and this seems to work. Make sure the COUNTis on one of the t1 columns - otherwise it will mistakenly return 1 instead of 0 when there are no corresponding entries in t1.
如果您尝试加入不同的列表（包括部门）然后进行计数会怎样？我创建了一些测试数据，这似乎有效。确保在COUNTt1 列之一上 - 否则当 t1 中没有相应的条目时，它会错误地返回 1 而不是 0。
SELECT t0.description, t0.depnum, count(t1.depnum) as 'numitems'
FROM salestable t0
LEFT JOIN (select distinct f1,f2,f3,depnum from salestable) t1
ON t0.depnum = t1.depnum
GROUP BY
t0.description, t0.depnum
回答by Adriano Carneiro
How about concatenating?
串联起来怎么样？
COUNT(DISTINCT field_char || '.' ||
cast(field_int as varchar) || '.' ||
cast(field_date as varchar)) AS costumernum
Warning: your concatenation operator may vary with RDBMS flavor.
警告：您的连接运算符可能因 RDBMS 风格而异。
Update
更新
Apparently, the concatenation operator portability is question by itself:
显然，连接运算符的可移植性本身就是一个问题：
I tried to help you with the distinctissue.
我试图帮助你解决这个distinct问题。
