如果我只选择了一个具有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
支持不同的聚合函数。
您可以这样尝试使用多列获取DISTINCT:
select
(SELECT group_concat(DISTINCT MS.SRC_TABLE_NAME) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID
WHERE MD.STATUS = 'Active') as SRC_TABLE_NAME,
(SELECT group_concat(DISTINCT MS.SRC_SYSTEM_ENVIRONMENT_NAME) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID
WHERE MD.STATUS = 'Active') as SRC_SYSTEM_ENVIRONMENT_NAME,
(SELECT group_concat(DISTINCT MS.SRC_SYSTEM_NAM) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID
WHERE MD.STATUS = 'Active') as SRC_SYSTEM_NAME
它在MYSQL中。
在SQL Server中,您可以像这样使用Group By:
SELECT MS.SRC_SYSTEM_NAM 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
INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID
WHERE MD.STATUS = 'Active'
GROUP BY MS.SRC_SYSTEM_NAM, MS.SRC_SYSTEM_ENVIRONMENT_NAME, MS.SRC_SYSTEM_NAME