找到你要的答案

Q:How to create a SQL query for the following operation?

Q:如何创建一个用于以下运行SQL查询?

I hope you will be able to help me out. I just started learning SQL and while applying my knowledge at work, I got stuck.I have SQL Database with multiple tables, which contain various data for properties of the items offered for sale. So far I successfully created a query which pulls most of the information needed. Unfortunately, the last table became problematic for me.Table is formatted like this:
| fkStockItemId  | PropertyName | PropertyValue | PropertyType |
|   ItemSKU-1    |  Item Style  |   SB-01123    |   Attribute  |
|   ItemSKU-1    |  Item Size   |    X-Small    |   Attribute  |
|   ItemSKU-1    |  Item Color  |      Red      |   Attribute  |
|   ItemSKU-2    |  Item Style  |   AA-66002    |   Attribute  |
|   ItemSKU-2    |  Item Size   |    Medium     |   Attribute  |
|   ItemSKU-2    |  Item Color  |     Green     |   Attribute  |                            
|   ItemSKU-3    |  Item Style  |    110445     |   Attribute  |
|   ItemSKU-3    |  Item Size   |     Small     |   Attribute  |

Output I am trying to get is like this:

    |    SKU    |  Item Style  |  Item Size  | Item Color  | 
    | ItemSKU-1 |   SB-01123   |   X-Small   |    Red      |
    | ItemSKU-2 |   AA-66002   |   Medium    |    Green    |
    | ItemSKU-3 |    110445    |    Small    |   *Null*    | 


    Please note that last column "PropertyType" is for technical purposes and 
is not needed to be queried.

This is what I got so far:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, siep.ProperyValue, siep.ProperyName
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep ON si.pkStockItemID = siep.fkStockItemId
WHERE siep.ProperyName = 'Item Style'

Tables "StockLevel" and "ProductCategories" show results just fine. If you notice, last "StockItem_ExtendedProperties" JOIN and "siep.ProperyValue", "siep.ProperyName" coupled with "WHERE siep.ProperyName = 'Item Style'" only allowed me to query 1 property. Thank you for your help and time!

I hope you will be able to help me out. I just started learning SQL and while applying my knowledge at work, I got stuck.I have SQL Database with multiple tables, which contain various data for properties of the items offered for sale. So far I successfully created a query which pulls most of the information needed. Unfortunately, the last table became problematic for me.Table is formatted like this:
| fkStockItemId  | PropertyName | PropertyValue | PropertyType |
|   ItemSKU-1    |  Item Style  |   SB-01123    |   Attribute  |
|   ItemSKU-1    |  Item Size   |    X-Small    |   Attribute  |
|   ItemSKU-1    |  Item Color  |      Red      |   Attribute  |
|   ItemSKU-2    |  Item Style  |   AA-66002    |   Attribute  |
|   ItemSKU-2    |  Item Size   |    Medium     |   Attribute  |
|   ItemSKU-2    |  Item Color  |     Green     |   Attribute  |                            
|   ItemSKU-3    |  Item Style  |    110445     |   Attribute  |
|   ItemSKU-3    |  Item Size   |     Small     |   Attribute  |

我试图得到的输出是这样的:

    |    SKU    |  Item Style  |  Item Size  | Item Color  | 
    | ItemSKU-1 |   SB-01123   |   X-Small   |    Red      |
    | ItemSKU-2 |   AA-66002   |   Medium    |    Green    |
    | ItemSKU-3 |    110445    |    Small    |   *Null*    | 


    Please note that last column "PropertyType" is for technical purposes and 
is not needed to be queried.

这就是我到目前为止:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, siep.ProperyValue, siep.ProperyName
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep ON si.pkStockItemID = siep.fkStockItemId
WHERE siep.ProperyName = 'Item Style'

表“库存水平”和“产品类别”显示的结果就好了。如果你注意到,去年“stockitem_extendedproperties”加入“希普。properyvalue”、“希普。properyname”加上“siep.properyname =“项目式”只让我查询1财产。谢谢你的帮助和时间!

answer1: 回答1:

Move the propertyname filter to the ON clause from the WHERE clause. Then join again for each property:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, style.ProperyValue as style, size.ProperyValue as size
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties style ON si.pkStockItemID = style.fkStockItemId
AND style.ProperyName = 'Item Style'
LEFT OUTER JOIN StockItem_ExtendedProperties size ON si.pkStockItemID = size.fkStockItemId
AND size.ProperyName = 'Item Size'

移动propertyName滤波器ON子句WHERE子句中的。然后再加入每个属性:

SELECT si.ItemNumber, si.ItemTitle, si.ItemDescription, si.RetailPrice, si.Weight, sl.Quantity, c.CategoryName, style.ProperyValue as style, size.ProperyValue as size
FROM StockItem si
LEFT OUTER JOIN StockLevel sl ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties style ON si.pkStockItemID = style.fkStockItemId
AND style.ProperyName = 'Item Style'
LEFT OUTER JOIN StockItem_ExtendedProperties size ON si.pkStockItemID = size.fkStockItemId
AND size.ProperyName = 'Item Size'
answer2: 回答2:

To get each value in a column like that, you can first write a subquery for each property, and then JOIN them all together, like this:

SELECT m1.fkStockItemId, m1.propertyValue AS 'Item Style', m2.propertyValue AS 'Item Size', m3.propertyValue AS 'Item Color'
FROM(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Style') m1
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Size') m2 ON m2.fkStockItemId = m1.fkStockItemId
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Color') m3 ON m3.fkStockItemId = m2.fkStockItemId;

Here is an SQL Fiddle example.

有柱状,每一个值,你可以先写一个每个属性的查询,然后将它们联系在一起,这样:

SELECT m1.fkStockItemId, m1.propertyValue AS 'Item Style', m2.propertyValue AS 'Item Size', m3.propertyValue AS 'Item Color'
FROM(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Style') m1
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Size') m2 ON m2.fkStockItemId = m1.fkStockItemId
LEFT JOIN(
  SELECT fkStockItemId, propertyValue
  FROM myTable
  WHERE propertyName = 'Item Color') m3 ON m3.fkStockItemId = m2.fkStockItemId;

这是一个SQL提琴的例子。

answer3: 回答3:

I believe that the best way is to make a function that will return the value of property you need and what you include in the query.

CREATE FUNCTION dbo.GetItemProperty
(
    @ItemName AS VARCHAR(50)
    , @Property AS VARCHAR(8)
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @Ans AS VARCHAR(50) = ''

    SELECT  @Ans = PropertyValue 
    FROM    StockItem AS si
    JOIN    StockItem_ExtendedProperties AS siep 
            ON si.pkStockItemID = siep.fkStockItemId
    WHERE   si.pkStockItemID = @ItemName
            AND siep.ProperyName = @Property

    RETURN @Ans;

END
GO

SELECT  si.ItemNumber, 
        si.ItemTitle, 
        si.ItemDescription, 
        si.RetailPrice, 
        si.Weight, 
        sl.Quantity, 
        c.CategoryName, 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Style') AS 'Item Style', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Size') AS 'Item Size', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Color') AS 'Item Color'
FROM StockItem si
LEFT OUTER JOIN StockLevel sl 
    ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c 
    ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep 
    ON si.pkStockItemID = siep.fkStockItemId;

God luck!

我相信最好的方法是创建一个函数,它将返回你需要的属性值和查询中包含的内容。

CREATE FUNCTION dbo.GetItemProperty
(
    @ItemName AS VARCHAR(50)
    , @Property AS VARCHAR(8)
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @Ans AS VARCHAR(50) = ''

    SELECT  @Ans = PropertyValue 
    FROM    StockItem AS si
    JOIN    StockItem_ExtendedProperties AS siep 
            ON si.pkStockItemID = siep.fkStockItemId
    WHERE   si.pkStockItemID = @ItemName
            AND siep.ProperyName = @Property

    RETURN @Ans;

END
GO

SELECT  si.ItemNumber, 
        si.ItemTitle, 
        si.ItemDescription, 
        si.RetailPrice, 
        si.Weight, 
        sl.Quantity, 
        c.CategoryName, 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Style') AS 'Item Style', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Size') AS 'Item Size', 
        dbo.GetItemProperty(si.pkStockItemID, 'Item Color') AS 'Item Color'
FROM StockItem si
LEFT OUTER JOIN StockLevel sl 
    ON si.pkStockItemID = sl.fkStockItemId
LEFT OUTER JOIN ProductCategories c 
    ON si.CategoryId = c.CategoryId
LEFT OUTER JOIN StockItem_ExtendedProperties siep 
    ON si.pkStockItemID = siep.fkStockItemId;

上帝的运气!

mysql  sql  database  qsqlquery  isqlquery