如何在SQL Server 2012中生成特定的查询

Question

我需要生成一个报告，其中包含特定产品（服装）的销售数量。

我有4个表：

• Orders 包含 ID 和日期
• Product_Model 包含型号描述和性别
• Product_Type 包含产品型号 ID、价格、尺码和颜色
• Product_Order 包含“订单”和“产品类型”的引用

我需要生成一个报告，显示所有月份中男性蓝色 T 恤的数量。

按照以下顺序：产品型号 / 性别 / 颜色

例如：

同时请查看我在SQLFiddle上的简单演示

非常感谢您的支持！=)

- Rodrigo Abib

---

你尝试过什么？当发帖者表现出解决问题的努力时，这里的问题会得到更好的回应。 - Gordon Linoff

Answer 1

尝试像这样做：

试试以下方法：

SELECT MODEL, 
       GENDER, 
       COLOR, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 1 THEN 1 
             ELSE 0 
           END) Jan, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 2 THEN 1 
             ELSE 0 
           END) Feb, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 3 THEN 1 
             ELSE 0 
           END) Mar, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 4 THEN 1 
             ELSE 0 
           END) Apr 
FROM   PRODUCT_MODELS t1 
       INNER JOIN PRODUCT_TYPES t2 
               ON t1.PRODUCT_MODEL_ID = t2.PRODUCT_MODEL_ID 
       INNER JOIN PRODUCT_ORDERS T3 
               ON t2.PRODUCT_TYPE_ID = t3.PRODUCT_TYPE_ID 
       INNER JOIN ORDERS T4 
               ON T3.ORDER_ID = T4.ORDER_ID 
GROUP  BY MODEL, 
          GENDER, 
          COLOR 

我使用了你的SQL Fiddle来编写我的示例。

- Gidil

---

我真的相信没有任何答案能比你的更帮助我！非常感谢！！=D - Rodrigo Abib

Answer 2

另一种解决方案是使用SQL透视表查询来满足您的需求

这是我在您的表格上构建的透视查询

with OrderReport as (
select
--  o.Order_ID,
--  date,
    DATENAME(Month,date) months,
    Order_Quantity,
--  Price,
--  Size,
--  Color,
--  model_description,
--  gender,
    product = model_description + ' / ' + gender + ' / ' + Color
from Orders o
inner join Product_Order po on po.Order_ID = o.Order_ID
inner join Product_Type t on t.Product_Type_ID = po.Product_Type_ID
inner join Product_Model m on m.Product_Model_ID = t.Product_Model_ID
) SELECT * FROM OrderReport
PIVOT (
    SUM(Order_Quantity)
    FOR months IN (
        [January],[February],[March],[April],
        [May],[June],[July],[August],
        [September],[October],[November],[December]
    )
) PivotTable

这里是DDL和一些样本数据，用于测试透视查询

create table Product_Model (Product_Model_ID int, model_description nvarchar(50),     gender nvarchar(20))
create table Product_Type (Product_Type_ID int, Product_Model_ID int, price int, size nvarchar(20), color nvarchar(20))
create table Orders (Order_ID int, date datetime)
create table Product_Order (Order_ID int, Product_Type_ID int, Order_Quantity int)

insert into Product_Model select 1, 'Basic T-Shirt','Male'
insert into Product_Model select 2, 'Basic T-Shirt','Female'
insert into Product_Model select 3, 'Polo','Male'
insert into Product_Model select 4, 'Polo','Female'
insert into Product_Model select 5, 'Jacket','Male'
insert into Product_Model select 6, 'Jacket','Female'
insert into Product_Model select 7, 'Long T-Shirt','Female'

insert into Product_Type select 1,1,1,'1','Red'
insert into Product_Type select 2,1,1,'1','Blue'
insert into Product_Type select 3,2,1,'1','Cyano'
insert into Product_Type select 4,2,1,'1','Pink'
insert into Product_Type select 5,3,2,'1','Grey'
insert into Product_Type select 6,3,2,'1','Black'
insert into Product_Type select 7,4,2,'1','Blue'
insert into Product_Type select 8,4,2,'1','Red'

insert into Orders select 1,'20130131'
insert into Orders select 2,'20130110'
insert into Orders select 3,'20130216'
insert into Orders select 4,'20130220'
insert into Orders select 5,'20130311'
insert into Orders select 6,'20130311'

insert into Product_Order select 1,1,3
insert into Product_Order select 2,1,5
insert into Product_Order select 3,2,7
insert into Product_Order select 4,1,4
insert into Product_Order select 5,1,1
insert into Product_Order select 6,3,8

希望这能有所帮助，

- kodyaz