创建包含查找表的视图时出现问题

should be simple enough but it's causing me a couple of issues.

应该很简单,但这会给我带来一些问题。

I have a data set similar to the following:

我有一个类似于以下的数据集:

User
  UserID
  Name
  Age

UserPropertyValues
  UserID
  PropertyCodeValueID

PropertyCodes
  PropertyCodeID
  PropertyCodeName

PropertyCodeValues
  PropertyCodeValueID
  PropertyCodeID
  PropertValue

Now let's assume the tables contain the following data:

现在让我们假设这些表包含以下数据:

1  John  25
2  Sarah  34

1  2
1  3
2  1
2  3

1  FavColour
2  CarMake
3  PhoneType

1  1  Blue
2  1  Yellow
3  2  Ford
4  3  Mobile
5  3  Landline

Now from this I'm looking to create a view to return the User details, as well as the property values for Property code 1 and 2 like so:

现在,我正在寻找创建一个视图来返回用户详细信息,以及属性代码1和2的属性值,如下所示:

John 25 Yellow Ford
Sarah 34 Blue Ford

The queries I have tried so far tend to return repeating rows of data :

到目前为止我尝试过的查询往往会返回重复的数据行:

John 25 Yellow
John 25 Ford
Sarah 34 Blue
Sarah 34 Ford

Any help is appreciated, thank you all in advance.

感谢任何帮助,谢谢大家提前。

3 个解决方案

#1

Input data:

DECLARE @User TABLE (UserID INT, Name VARCHAR(10), Age INT)
INSERT INTO @User
SELECT 1,  'John',  25 UNION
SELECT 2,  'Sarah',  34
DECLARE @UserPropertyValues TABLE(UserID INT, PropertyCodeValueID INT)
INSERT INTO @UserPropertyValues
SELECT 1, 2 UNION
SELECT 1, 3 UNION
SELECT 2, 1 UNION
SELECT 2,  3
DECLARE @PropertyCodes 
  TABLE (PropertyCodeID INT, PropertyCodeName VARCHAR(10))
INSERT INTO @PropertyCodes
SELECT 1,  'FavColour' UNION
SELECT 2,  'CarMake' UNION
SELECT 3,  'PhoneType'
DECLARE @PropertyCodeValues TABLE (PropertyCodeValueID INT, 
  PropertyCodeID INT, PropertValue VARCHAR(10))
INSERT INTO @PropertyCodeValues
SELECT 1,  1,  'Blue' UNION
SELECT 2,  1,  'Yellow' UNION
SELECT 3,  2,  'Ford' UNION
SELECT 4,  3,  'Mobile' UNION
SELECT 5,  3,  'Landline'

If two properties is all that you need in result, and each user have those properties, then try this:

如果结果中需要两个属性,并且每个用户都具有这些属性,请尝试以下操作:

SELECT U.Name, U.Age, PCVFC.PropertValue, PCVCM.PropertValue 
FROM @User U
INNER JOIN @UserPropertyValues UPVFC ON U.UserID = UPVFC.UserID 
INNER JOIN @PropertyCodeValues PCVFC 
  ON UPVFC.PropertyCodeValueID = PCVFC.PropertyCodeValueID 
    AND PCVFC.PropertyCodeID = 1
INNER JOIN @UserPropertyValues UPVCM ON U.UserID = UPVCM.UserID 
INNER JOIN @PropertyCodeValues PCVCM 
  ON UPVCM.PropertyCodeValueID = PCVCM.PropertyCodeValueID 
    AND PCVCM.PropertyCodeID = 2

[edit] But to handle possible NULL values better use this:

[编辑]但要处理可能的NULL值更好地使用这个:

SELECT U.Name, U.Age, FC.PropertValue, CM.PropertValue 
FROM @User U
LEFT JOIN (
  SELECT UserID, PropertValue FROM @UserPropertyValues  UPV
    INNER JOIN @PropertyCodeValues PCV 
      ON UPV.PropertyCodeValueID = PCV.PropertyCodeValueID 
        AND PCV.PropertyCodeID = 1
) FC ON U.UserID = FC.UserID
LEFT JOIN (
  SELECT UserID, PropertValue FROM @UserPropertyValues  UPV
    INNER JOIN @PropertyCodeValues PCV 
      ON UPV.PropertyCodeValueID = PCV.PropertyCodeValueID 
        AND PCV.PropertyCodeID = 2
) CM ON U.UserID = CM.UserID

#2

What you really need to is abandon this type of database design as soon as humanly possible. It will never be either effective of efficient. To get three types of values you have to join to the table three times. Once you have 30 or forty differnt types of information, you will need to join to the table that many times (and left joins at that). Further everytime you want any information you will need to join to this table. I see this as creating a major locking issue in your database. The people who originally designed one of the databases I work with did this and caused a huge performance issue when the company grew from having one or two customers to the largest in our industry.

你真正需要的是尽快放弃这种类型的数据库设计。它永远不会有效率。要获得三种类型的值,您必须三次加入表中。一旦您有30或40种不同类型的信息,您将需要多次加入该表(并且左键加入该表)。每当您需要任何信息时,您需要加入此表。我认为这是在数据库中创建一个主要的锁定问题。最初设计与我合作的数据库之一的人这样做,并且当公司从拥有一个或两个客户成长为我们行业中最大的客户时,造成了巨大的性能问题。

If the properties are ones that will likely only have one realted records per person, put them into the user table. If they will have multiple records then create a separate table for each type of information (one for hones, one for email, one for cartype, etc.) Since the information you will eventually want to collect will usually be more than the simple value and differnt for each type of information they must be in separate tables. Then when you only need to see one value (say phone number but not email) you join to just that table and you aren't interfeing with people trying to access email but not phone number. And if you have a yellow ford or white Honda, it will be stored in only one record in the auto table rather than two property records in your design.

如果属性是每个人可能只有一个实际记录的属性,则将它们放入用户表中。如果他们将有多个记录,那么为每种类型的信息创建一个单独的表(一个用于hones,一个用于电子邮件,一个用于cartype等)因为您最终想要收集的信息通常不仅仅是简单的值和对于每种类型的信息,它们必须在不同的表中。然后,当您只需要查看一个值(比如电话号码而不是电子邮件)时,您就可以加入到该表中,并且您不会干扰尝试访问电子邮件而非电话号码的人。如果您有黄色福特或白色本田,它将仅存储在自动表中的一个记录中,而不是存储在您的设计中的两个属性记录中。

#3

SELECT 
  u.[Name],
  u.Age,
  pcv1.PropertValue,
  pcv2.PropertValue
FROM 
  Users  u
  LEFT JOIN 
  ( UserPropertyValues upv1 
    JOIN PropertyCodeValues pcv1 ON 
    upv1.PropertyCodeValueID = pcv1.PropertyCodeValueID 
    AND pcv1.PropertyCodeID = 1
  )
  ON upv1.UserID = u.UserID
  LEFT JOIN (
    UserPropertyValues upv2 
    JOIN PropertyCodeValues pcv2 ON 
    upv2.PropertyCodeValueID = pcv2.PropertyCodeValueID 
    AND pcv2.PropertyCodeID = 2
  )
  ON upv2.UserID = u.UserID

Edit : I renamed user to users

编辑:我将用户重命名为用户

Edit2 : Allow for null (not entered values)

Edit2:允许为空(未输入值)

#1