小弟目前在写一个跨表查询语句时遇到了困难 语句如下
SELECT TOP 100 t.*, c.country_name, cat.name AS category_name, p.FileName, p.IsDefault FROM Topics AS t LEFT JOIN Country AS c ON t.Country = c.ID LEFT JOIN Category AS cat ON t.Category = cat.ID JOIN Pictures AS p ON t.ID = p.TopicID p.TopicID LIKE 'myspace%' ORDER BY FileName DESC, RPH DESC
问题是 在Topics表中一个t.ID会对应多个p.TopicID, 这样导致的结果就是查询结果中会出现重复的topics表的纪录 无论用left join 还是right join都是一样的问题 有没有办法能让查询结果过滤掉某一个特定列里的重复内容? 谢谢
SELECT TOP 100 t.*, c.country_name, cat.name AS category_name, p.FileName, p.IsDefault FROM Topics AS t LEFT JOIN Country AS c ON t.Country = c.ID LEFT JOIN Category AS cat ON t.Category = cat.ID JOIN Pictures AS p ON t.ID = p.TopicID p.TopicID LIKE 'myspace%' ORDER BY FileName DESC, RPH DESC
问题是 在Topics表中一个t.ID会对应多个p.TopicID, 这样导致的结果就是查询结果中会出现重复的topics表的纪录 无论用left join 还是right join都是一样的问题 有没有办法能让查询结果过滤掉某一个特定列里的重复内容? 谢谢
可以用GROUP BY 特定列其它列加MAX来处理
用select distinct top ... (将select的显示结果中的重复行去掉)
或者 group by ... (聚合)
是对应多个p.id还是多个p.TopicID?
一般不应该出现一个t.id对应多个TopicID的。
结果就是有很多重复的t.title
with f as
(SELECT TOP 100 t.*, c.country_name, cat.name AS category_name, p.FileName, p.IsDefault FROM Topics AS t LEFT JOIN Country AS c ON t.Country = c.ID LEFT JOIN Category AS cat ON t.Category = cat.ID JOIN Pictures AS p ON t.ID = p.TopicID p.TopicID LIKE 'myspace%' ORDER BY FileName DESC, RPH DESC
)
select max(col1),max(col2)...... from f group by 没有聚合的列....
with f as
(SELECT
TOP 100 t.*,
c.country_name,
cat.name AS category_name,
p.FileName,
p.IsDefault
FROM
Topics AS t
LEFT JOIN
Country AS c
ON
t.Country = c.ID
LEFT JOIN
Category AS cat
ON
t.Category = cat.ID
JOIN
Pictures AS p
ON
t.ID = p.TopicID p.TopicID
LIKE
'myspace%'
ORDER BY
FileName DESC, RPH DESC
)
select max(col1),max(col2)...... from f group by 没有聚合的列....
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 VARCHAR(50),COL2 VARCHAR(50))
INSERT INTO TB
SELECT 'A','A' UNION ALL
SELECT 'A','B' UNION ALL
SELECT 'A','C' UNION ALL
SELECT 'B','D' UNION ALL
SELECT 'B','B' UNION ALL
SELECT 'B','C'
SELECT COL1,MAX(COL2) FROM TB GROUP BY COL1/*
A C
B D
*/
--想要不重复的字段在语句最后加止GROUP BY 字段名,SELECT的其它字段都加上MAX
--这样GROUP BY的字段就不重复了,其它字段如果有多个则只取最大的一个