使用参数在Server中创建X读取报告

Question

我有这段代码和它的临时表，所以您可以运行它。

create table #Order_details(
id int identity(1,1),
user_id int,
product_id int,
quantity int,
date datetime
)

insert into #Users(name)
values('Manny'),('Danny')

insert into #Products(name,price)
values('Product A',150),('Product B',200),('Product C',250),('Product D',300)

insert into #Order_details(user_id,product_id,quantity,date)
values(1,1,1,getdate()),(1,1,1,getdate()),(1,3,1,getdate()),(2,1,3,getdate())

drop table #Users
drop table #Products
drop table #Order_details

所以你可以看到，产品A的数量是2，C产品的数量是1，而产品A是在丹尼的时间内购买的3倍

 id          name        qty         sum      user
 ---------------------------------------------------
 1           Product A   3           450.00   Danny

我只想看到所有的产品，即使产品B，C和D没有在丹尼的时代购买

我的目标是：

 id          name        qty         sum      user
 ---------------------------------------------------
 1           Product A   3           450.00   Danny
 2           Product B   null        null     null
 3           Product C   null        null     null
 4           Product D   null        null     null

我的尝试：

select
  P.id,
  P.name,
  sum(OD.quantity) 'qty',
  sum(OD.quantity) * P.price 'sum',
  min(U.name) 'user'
from #Products P
  left join #Order_details OD
on OD.product_id = P.id
  left join #Users U
on U.id = OD.user_id
  where U.id = 2 --Danny
  group by P.id, P.name, U.id, P.price

Answer 1

您只需稍微编辑一下查询，就可以得到预期的结果。

select
    id, name, qty = max(qty), [sum] = max([sum]), [user] = max([user])
from (
    select
      P.id,
      P.name,
      sum(OD.quantity) 'qty',
      sum(OD.quantity) * P.price 'sum',
      min(U.name) 'user'
    from #Products P
      left join #Order_details OD
    on OD.product_id = P.id
      left join #Users U
    on U.id = OD.user_id
      where U.id = 2 --Danny
      group by P.id, P.name, U.id, P.price

      union all
      select id, name, null, null, null from #Products
) t
group by id, name

Answer 2

，请试试这个-

create table #Order_details
(
    id int identity(1,1),
    user_id int,
    product_id int,
    quantity int,
    date datetime
)

CREATE TABLE #Users ( user_id INT , name VARCHAR(100) )
GO

DROP TABLE #Users

insert into #Users(user_id,name)
values(1,'Manny'),(2,'Danny')

CREATE TABLE #Products (product_id INT ,name VARCHAR(100) ,price INT )
GO

insert into #Products(product_id,name,price)
values(1,'Product A',150),(2,'Product B',200),(3,'Product C',250),(4,'Product D',300)

insert into #Order_details(user_id,product_id,quantity,date)
values(1,1,1,getdate()),(1,1,1,getdate()),(1,3,1,getdate()),(2,1,3,getdate())

溶液

SELECT p.product_id, MAX(p.name) ProductName ,
IIF(SUM(CASE WHEN  u.user_id = 2 THEN o.quantity ELSE 0 END)=0,NULL,SUM(CASE WHEN  u.user_id = 2 THEN o.quantity ELSE 0 END)) quantity, SUM(p.price) price , MAX(u.name) userName FROM #Order_details o
FULL JOIN #Products p ON o.product_id = p.product_id
FULL JOIN #Users u ON u.user_id = o.user_id AND u.user_id = 2
WHERE u.user_id IS NULL or u.user_id = 2
GROUP BY p.product_id

输出

product_id  ProductName           quantity    price       userName
----------- --------------------- ----------- ----------- -----------------------------------------------
1           Product A             3           450         Danny
2           Product B             NULL        200         NULL
3           Product C             NULL        250         NULL
4           Product D             NULL        300         NULL

(4 rows affected)