文章/答案/技术大牛

发布

社区首页 >问答首页 >在复杂的SQL查询中使用中间连接表

问在复杂的SQL查询中使用中间连接表
EN

Stack Overflow用户

提问于 2020-09-16 02:59:00

回答 3查看 203关注 0票数 0

我从一个商业伙伴那里继承了一个项目，并试图将其添加到其中，但在这里却逆风而行。这是一个由7个表组成的鸡尾酒数据库。

drinks (ID, drinkName, lu_category, lu_glassware, lu_IBA)
category (categoryID, category)
glassware (glasswareID, glassware)
ingredients (ingredientID, ingredient, notes)
measure (measureID, measure)
IBA (IBAID, IBA)
ingredient_drinks_measure (idmID, drinkID, ingredientID, measureID)

此查询的工作方式与预期一致，并在每个饮品中拉回一行中的所有内容。

SELECT        dbo.drinks.id AS [drinkID], 
              dbo.drinks.drinkName, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA, 
              string_agg(measure.measure + ' ' + ingredients.ingredient, ', ') as [Ingredients]
FROM          dbo.glassware 
              RIGHT OUTER JOIN dbo.measure 
              LEFT OUTER JOIN dbo.ingredient_drinks_measure ON dbo.measure.measureID = dbo.ingredient_drinks_measure.measureID 
              RIGHT OUTER JOIN dbo.ingredients ON dbo.ingredient_drinks_measure.ingredientID = dbo.ingredients.ingredientID 
              RIGHT OUTER JOIN dbo.drinks ON dbo.ingredient_drinks_measure.drinkID = dbo.drinks.id 
              LEFT OUTER JOIN dbo.IBA ON dbo.drinks.lu_IBA = dbo.IBA.IBAID ON dbo.glassware.glasswareID = dbo.drinks.lu_glassware 
              LEFT OUTER JOIN dbo.category ON dbo.drinks.lu_category = dbo.category.categoryID
GROUP BY      dbo.drinks.id, 
              dbo.drinks.drinkName, 
              dbo.drinks.imagePath, 
              dbo.drinks.dateModified, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA

drinkID | drinkName | category  | glassware         | IBA                   | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

我现在需要为每种饮料添加标签

(目前)只有22个标签，每种饮料可以有1到22个标签(标签像“万圣节”、“圣诞节”、“潘趣碗”、“早餐”、“晚餐派对”、“水果”、“醉酒”等)。

我有一个tags table (id, tag)，我在中间有一个drinkTags table (drinkID, tagID)，用于将饮料和标签组合在一起(两者的创建脚本如下所示)。

然而，无论我如何尝试，当我将连接添加到查询中并为标签添加一个附带的string_agg列(逗号分隔的聚合列)时，至少有一个string_agg列被复制和/或我在每杯饮料中得到多个行。如下所示：

drinkID | drinkName | category  | glassware         | IBA                   | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar, 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar, 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

或者这样：

drinkID | drinkName | category  | glassware         | IBA                   | tags      | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | Alcoholic | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | IBA       | 2 tsp Superfine Sugar, Juice of 1 Lime, 2-3 oz Jamaican Rum
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | USA       | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

有什么想法吗？我非常感谢任何人的帮助，即使它只是给我指出了一些我在所有研究中忽略的东西。

如果您需要，下面是创建脚本。

/****** Object:  Table [dbo].[drinks]    Script Date: 9/15/2020 1:25:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[drinks](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [drinkName] [varchar](37) NOT NULL,
    [lu_category] [int] NULL,
    [lu_IBA] [int] NULL,
    [lu_glassware] [int] NULL
 CONSTRAINT [PK__drinks_c__2B658F5CD9E4315A] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[category]    Script Date: 9/15/2020 1:27:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[category](
    [categoryID] [int] IDENTITY(1,1) NOT NULL,
    [category] [nvarchar](50) NULL,
 CONSTRAINT [PK__category__23CAF1F80317C8FA] PRIMARY KEY CLUSTERED 
(
    [categoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[drinkTags]    Script Date: 9/15/2020 1:27:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[drinkTags](
    [drinkID] [int] NOT NULL,
    [tagID] [int] NOT NULL,
 CONSTRAINT [PK_drinkTags_1] PRIMARY KEY CLUSTERED 
(
    [drinkID] ASC,
    [tagID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO




/****** Object:  Table [dbo].[glassware]    Script Date: 9/15/2020 1:28:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[glassware](
    [glasswareID] [int] IDENTITY(1,1) NOT NULL,
    [glassware] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [glasswareID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[IBA]    Script Date: 9/15/2020 1:28:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IBA](
    [IBAID] [int] IDENTITY(1,1) NOT NULL,
    [IBA] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [IBAID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[ingredient_drinks_measure]    Script Date: 9/15/2020 1:28:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ingredient_drinks_measure](
    [idmID] [int] IDENTITY(1,1) NOT NULL,
    [drinkID] [int] NULL,
    [ingredientID] [int] NULL,
    [measureID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [idmID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[ingredients]    Script Date: 9/15/2020 1:28:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ingredients](
    [ingredientID] [int] IDENTITY(1,1) NOT NULL,
    [ingredient] [nvarchar](150) NULL
PRIMARY KEY CLUSTERED 
(
    [ingredientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



/****** Object:  Table [dbo].[measure]    Script Date: 9/15/2020 1:29:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[measure](
    [measureID] [int] IDENTITY(1,1) NOT NULL,
    [measure] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [measureID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO



/****** Object:  Table [dbo].[tags]    Script Date: 9/15/2020 1:29:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tags](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tag] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_drinkTags] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

sql-server-2019

sql

join

回答 3

Stack Overflow用户

发布于 2020-09-16 03:16:17

解决方案1：

饮料与标签之间是否存在一对一的关系？如果这是真的，那么您需要做的就是将一个标签列添加到饮料表中。这将是一种非规范化的解决问题的方法。

解决方案2：

如果饮料与标签实体不具有一对一关系。

哪个表存储了饮料和标签之间的关系？如果您没有这个表，那么您需要创建一个表并填充数据。如果你需要帮助创建饮料标签的关系，那么我可以帮助你。然后，您将需要更新SQL以使用tag to drink关系来获取单个记录，并在SQL中添加tag字段。

票数 0

Stack Overflow用户

发布于 2020-09-16 03:57:49

希望下面的查询能解决您的问题。我添加了一个string_agg字段来返回与一杯饮料和两个内部连接相关的所有标签。

SELECT        dbo.drinks.id AS [drinkID], 
              dbo.drinks.drinkName, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA, 
              string_agg(measure.measure + ' ' + ingredients.ingredient, ', ') as [Ingredients],
          string_agg(dbo.tags.tag, ', ‘) as [Tags]
FROM          dbo.glassware 
              RIGHT OUTER JOIN dbo.measure 
              LEFT OUTER JOIN dbo.ingredient_drinks_measure ON dbo.measure.measureID = dbo.ingredient_drinks_measure.measureID 
              RIGHT OUTER JOIN dbo.ingredients ON dbo.ingredient_drinks_measure.ingredientID = dbo.ingredients.ingredientID 
              RIGHT OUTER JOIN dbo.drinks ON dbo.ingredient_drinks_measure.drinkID = dbo.drinks.id 
              LEFT OUTER JOIN dbo.IBA ON dbo.drinks.lu_IBA = dbo.IBA.IBAID ON dbo.glassware.glasswareID = dbo.drinks.lu_glassware 
              LEFT OUTER JOIN dbo.category ON dbo.drinks.lu_category = dbo.category.categoryID
          INNER JOIN  dbo.drinkTags.drinkID =  dbo.drinks.id    
          INNER JOIN  dbo.tags.id = dbo.drinkTags.tagID
GROUP BY      dbo.drinks.id, 
              dbo.drinks.drinkName, 
              dbo.drinks.imagePath, 
              dbo.drinks.dateModified, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA

票数 0

Stack Overflow用户

发布于 2020-09-29 00:26:16

经过更多的研究，我找到了一个适合我的解决方案。这确实假设每个drinkID至少有一个标记，但我可以暂时处理这个问题。

;WITH a AS
(
SELECT 
    d.id,
    d.drinkName as [Name] ,
    c.category as [Category],
    g.glassware as [Glass],
    b.IBA as [IBA],
    string_agg(m.measure + ' ' + i.ingredient, ', ') as [Ingredients],
    d.instructions as [Instructions],
    string_agg(i.notes, ', ') as [IngredientNotes],
    d.addedInfo as [Additional Information],
    d.imagePath as [Image]
FROM drinks d
    JOIN category c on c.categoryID = d.lu_category
    JOIN glassware g on g.glasswareID = d.lu_glassware
    LEFT JOIN IBA b on b.IBAID = d.lu_IBA
    JOIN ingredient_drinks_measure idm on idm.drinkID = d.id
    JOIN ingredients i on i.ingredientID = idm.ingredientID
    JOIN measure m on m.measureID = idm.measureID
group by d.id, d.drinkName, c.category, g.glassware, b.IBA, d.addedInfo, d.imagePath, d.instructions
)
SELECT a.*, dtm.tags
FROM a
JOIN (select 
        drinkID, string_agg(t.tag, ', ') as tags
        from tags t
        join tagmap tm on tm.tagID = t.id
        group by drinkID) as dtm
ON a.ID = dtm.drinkID

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/63908255

复制

相似问题

问在复杂的SQL查询中使用中间连接表
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在复杂的SQL查询中使用中间连接表EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在复杂的SQL查询中使用中间连接表
EN