SQL Server体系结构 [英] SQL Server Architecture

问题描述

这很难解释。我将尝试将我们的问题与一般规格相关联。

This is kind of a tough one to explain. I'm going to try to correlate our issue into a general spec.

我有一张根据特定情况需要提出的问题表。例如，假设我有两个苹果。 1个苹果是蓝色，另一个苹果是红色。

I've got a table of questions that needed to be asked based on certain circumstances. For instance, let's say that I have 2 apples. 1 apple is blue and the other apple is red.

现在，我需要分配一个问题，如果我们有1个红苹果。但是，我还需要为1个红苹果和 1个蓝苹果的组合分配一个问题。

Now, I need to be able to assign a question if we have 1 red apple. However, I also need to be able to assign a question for the combination of 1 red apple and 1 blue apple.

构造这个的最好方法是什么？我觉得我已经接近了，但是我仍在努力完成它。

What's the best way to architect this? I feel that I've gotten close, but I'm still struggling to complete it.

需要对FilterGroupID进行分组。因此，在下面，当我们获得BLUE& amp;时，需要查询问题ID 3。小。

The FilterGroupID is where they need to be grouped. So, down below, the questionID 3 needs to be queried when we've been given BLUE & SMALL.

场景
我得到了某种苹果的组合。蓝色和小号。我需要能够查询与这个苹果相关的这些问题。因此，我需要具有匹配的过滤器类型/值的任何问题。因此，在这种情况下，我需要以下问题：

SCENARIO I'm given a certain combination of an apple. It's Blue and Small. I need to be able to query these questions that are correlated to this apple. So, I would need ANY question that has a filter type/value that matches. So in this scenario, I would want the following questions:

1. 青苹果问题

小苹果问题

蓝色和小苹果问题

如果您看下面的图片，我得到了一个蓝色的苹果&大。我关心（存储在表中）的唯一问题是蓝苹果问题。现在，如果给我一个蓝色的小苹果，我会在意蓝苹果问题和蓝与小苹果问题。仅当苹果是蓝色和小时才显示问题蓝色和小苹果问题。意思是，如果苹果是蓝色和蓝色，则不会显示。

If you look @ the image below, I've been given an apple that is BLUE & LARGE. The only questions I care (that are stored in the table) about are "Blue apple question". Now, if I was given a blue & small apple, I would care about "blue apple question" AND "blue & small" apple questions. The question "Blue & small apple question" is only shown IF THE APPLE IS BLUE AND SMALL. Meaning, It's not shown if the apple is BLUE AND LARGE.

推荐答案

这是模型的简化版本，术语稍有不同，因此我可以理解我在说什么:-)：

This is a simplified version of the model, with a slightly different terminology so I can understand what I am talking about :-) :

Table: Qualities (Quality)
Records: (Color), (Size).

Table: QualityOptions(ID int, Quality, Option)
Records: (1, Color, Red), (2, Color, Blue), (3, Color, Green), (4, Size, Big),    
          (5, Size, Small)

Table: Questions (Question, QualityOptionID)
Records: (Q1, 1), (Q1, 5), (Q2, 1), (Q3, 4), (Q4, 5), (Q5, 2), (Q5, 4), (Q6, 2)

为提高示例的可读性，我将最后一张表及其记录重写为：

To improve readability of the example I rewrite the last table and its records as:

Table: Questions (Question, QualityOption)
Records: (Q1, Red), (Q1, Small), (Q2, Red), (Q3, Big), (Q4, Small), (Q5, Blue), 
         (Q5, Big), (Q6, Small)

Table: Items (Item)
Records: (a small blue item), (a blue item), (a red item), (a big blue item)

Table: ItemQualities (Item, QualityOption) - again, simplified!
Records: (a small blue item, small), (a small blue item, blue), (a blue item, blue),
         (a red item, red), (a big blue item, big), (a big blue item, blue)

假设 Item = 蓝色小物件 。查询 ItemQualities 关系以获取质量 small 和 blue 的质量。查询问题关系以获取候选：（Q1，small），（Q4，small ），（Q5，蓝色），（Q6，小）。不过您还没有完成：再次查询问题以查看候选问题 Q1 ， Q4 ， Q5 ， Q6 。对于 Q1 ，还有（Q1，red），这意味着我们必须丢弃 Q1 。对于 Q5 ，还有（Q5，大）->丢弃。因此我们得到了结果集： {Q4，Q6} 。

Suppose that Item = "a small blue item". Query ItemQualities relation to get the qualities small and blue. Query the Questions relation to get candidates: (Q1,small), (Q4,small), (Q5,blue), (Q6,small). You are not finished though: query again Questions to see if there are any other records for the candidate questions Q1, Q4, Q5, Q6. For Q1 there is also (Q1,red), meaning that we must discard Q1. For Q5 there is also (Q5, big) -> discard. So we have our result set: {Q4, Q6}.

现在假设 Item =蓝色的大物件 ，因此 ItemQualities 表返回 big 和蓝色。 个问题中的候选人是（第3季度，大），（第5季度，蓝色），（第5季度，大）。再次查询问题，以获得与候选项相关的所有其他记录。没有其他记录返回，我们可以返回 {Q3，Q5} 作为结果。

Suppose now that Item = "a big blue item", so the ItemQualities table returns big and blue. Candidates from Questions are (Q3,big), (Q5,blue), (Q5,big). Query again Questions to get every other record related to the candidates Q3, Q5. There are no other records returned and we can return {Q3, Q5} as the result.

演示：

Demo:

create table Items (Item varchar(100))
create table ItemQualities (Item varchar(100), QualityOption varchar(20))
create table Questions (Question varchar(20), QualityOption varchar(20))

insert Items select 'a small blue item' union all select 'a big blue item'
insert ItemQualities select 'a small blue item', 'small' union all select 'a small blue item', 'blue'
union all select 'a big blue item', 'big' union all select 'a big blue item', 'blue'

insert Questions select 'Q1', 'red' union all select 'Q1','small' 
union all select 'Q2', 'red' union all select 'Q3', 'big' union all select 'Q4', 'small'
union all select 'Q5', 'Blue' union all select 'Q5', 'big' union all select 'Q6', 'small'

select * from items
select * from itemqualities
select * from questions

declare @item varchar(100) = 'a small blue item';


; with x as (
select count(*) cnt, q.Question
from ItemQualities iq join Questions q on iq.QualityOption = q.QualityOption
where item = @item
group by q.Question
)
,
y as 
(
select count(*) cnt, x.Question 
    from x
join Questions q on x.Question = q.Question
    group by x.Question
)
select distinct x.Question from x join y on x.Question = y.Question and x.cnt = y.cnt

这篇关于SQL Server体系结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！