代码之家 › 专栏 › 技术社区 › Chris Ballance

SQL联接中的重复项出现问题

join database sql-server sql

Chris Ballance · 技术社区 · 15 年前

我从三个表的联接中得到了以下结果集, 文章表,A 产品表 商品到产品 映射表。

我希望删除重复项的结果,类似于内容ID上的select distinct。

当前结果集:

[ContendId] [Title]          [productId]
1          article one      2
1          article one      3
1          article one      9
4          article four     1
4          article four     10
4          article four     14
5          article five     1
6          article six      8
6          article six      10
6           article six      11
6          article six      13
7           article seven    14

所需结果集:

[ContendId] [Title]          [productId]
1          article one      *
4          article four     *
5          article five     *
6          article six      *
7           article seven    *

下面是相关SQL的浓缩示例:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'tempdb.dbo.products') AND type = (N'U'))
drop table tempdb.dbo.products
go
CREATE TABLE tempdb.dbo.products ( 
productid int,
productname varchar(255)
)

go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U'))
drop table tempdb.dbo.articles
go
create table tempdb.dbo.articles (
contentid int,
title varchar(255)
)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articles') AND type = (N'U'))
drop table tempdb.dbo.articles
go
create table tempdb.dbo.articles (
contentid int,
title varchar(255)
)


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'articleproducts') AND type = (N'U'))
drop table tempdb.dbo.articleproducts
go
create table tempdb.dbo.articleproducts (
contentid int,
productid int
)


insert into tempdb.dbo.products values (1,'product one'),
                                        (2,'product two'),
                                        (3,'product three'),
                                        (4,'product four'),
                                        (5,'product five'),
                                        (6,'product six'),
                                        (7,'product seven'),
                                        (8,'product eigth'),
                                        (9,'product nine'),
                                        (10,'product ten'),
                                        (11,'product eleven'),
                                        (12,'product twelve'),
                                        (13,'product thirteen'),
                                        (14,'product fourteen')

insert into tempdb.dbo.articles VALUES (1,'article one'), 
                                        (2, 'article two'), 
                                        (3, 'article three'), 
                                        (4, 'article four'), 
                                        (5, 'article five'), 
                                        (6, 'article six'), 
                                        (7, 'article seven'), 
                                        (8, 'article eight'), 
                                        (9, 'article nine'), 
                                        (10, 'article ten')


INSERT INTO tempdb.dbo.articleproducts VALUES (1,2),
                                              (1,3),
                                              (1,9),
                                              (4,1),
                                              (4,10),
                                              (4,14),
                                              (5,1),
                                              (6,8),
                                              (6,10),
                                              (6,11),
                                              (6,13),
                                              (7,14)

GO


select DISTINCT(a.contentid), a.title, p.productid  from articles a 
        JOIN articleproducts ap ON a.contentid = ap.contentid 
        JOIN products p ON a.contentid = ap.contentid AND p.productid = ap.productid 

        ORDER BY a.contentid

3 回复 | 直到 15 年前

Ignacio Vazquez-Abrams 15 年前

你的问题必须错过了什么…

SELECT DISTINCT a.*
FROM articles AS a
INNER JOIN articleproducts AS ap
  ON a.contentid = ap.contentid
ORDER BY a.contentid

Chris Ballance 15 年前

只是想清楚了。我需要一个合适的 Group By 条款

选择a.contentid、a.title、count(*) 从文章A连接 A.ContentID上的ArticleProducts AP= ap.contentid加入产品p on a.contentID=ap.contentID和 p.productID=ap.productID分组依据 A.内容ID,A.标题排序依据有争议的

Atul 15 年前

    SELECT DISTINCT(a.contentid), a.title, p.productid  
    FROM articles a  
    INNER JOIN articleproducts ap 
         ON a.contentid = ap.contentid  
    INEER JOIN products p 
         ON a.contentid = ap.contentid 
         AND p.productid = ap.productid  
    GROUP BY (a.contentid), a.title
    ORDER BY a.contentid

这应该管用!