如果我只选择1个具有Distinct的列,则工作正常,如果我选择了1个不同的多列,则返回重复值..
SELECT
DISTINCT MS.SRC_TABLE_NAME AS SRC_TABLE_NAME,
MS.SRC_SYSTEM_ENVIRONMENT_NAME AS SRC_SYSTEM_ENVIRONMENT_NAME,
MS.SRC_SYSTEM_NAME AS SRC_SYSTEM_NAME
FROM
MAPPING_SPECIFICATION MS,
MAPPING_DETAILS MD
WHERE
MS.MAP_ID = MD.MAP_ID AND
MD.STATUS = 'Active'
返回重复的行
SELECT
DISTINCT MS.SRC_TABLE_NAME AS SRC_TABLE_NAME
FROM
MAPPING_SPECIFICATION MS,
MAPPING_DETAILS MD
WHERE
MS.MAP_ID = MD.MAP_ID AND
MD.STATUS = 'Active'
如果仅选择Distinct Row,则效果很好。
最佳答案
DISTINCT
与其他列一起使用时,不能保证您具有SRC_TABLE_NAME
唯一性。您的行是DISTINCT
,它工作得很好
CREATE TABLE #MyTable(col1 INT, col2 INT);
INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);
SELECT DISTINCT col1, col2
FROM #MyTable;
LiveDemo
使用
GROUP BY
代替aggregate function
,例如MIN/MAX/GROUP_CONCAT
:SELECT
MS.SRC_TABLE_NAME AS SRC_TABLE_NAME,
MIN(MS.SRC_SYSTEM_ENVIRONMENT_NAME) AS SRC_SYSTEM_ENVIRONMENT_NAME,
MIN(MS.SRC_SYSTEM_NAME) AS SRC_SYSTEM_NAME
FROM MAPPING_SPECIFICATION MS
JOIN MAPPING_DETAILS MD
ON MS.MAP_ID = MD.MAP_ID AND
WHERE MD.STATUS = 'Active'
GROUP BY MS.SRC_TABLE_NAME;
另外,请尝试避免使用逗号语法连接并使用
JOIN
。请记住,SQL Server
和MySQL
支持不同的聚合函数。关于mysql - SQL Distinct关键字不为多列返回不同的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/33692326/