选择1视图中的所有值(不能使用过程和临时表)

Question

我需要在我的数据库中创建一个带有select的视图。它必须是视图，不能使用存储过程，因此它只需要在一个查询中创建，而不需要临时存储。

我创建了一个简单的例子：

CREATE TABLE #TemporaryTable          -- Local temporary table - starts with single #
(
    id int,
    date_order date,
    order_status varchar(50)
);


insert into #TemporaryTable
VALUES
('1','2022-01-01','Completed'),
('2','2022-01-01','Cancelled'),
('3','2022-01-01','Completed'),
('4','2022-01-01','Completed'),
('5','2022-01-02','Cancelled'),
('6','2022-01-02','Cancelled'),
('7','2022-01-02','Completed'),
('8','2022-01-02','Completed'),
('9','2022-01-02','Completed'),
('10','2022-01-03','Cancelled'),
('11','2022-01-04','Completed')



select * from #TemporaryTable


SELECT COUNT(crm1.date_order) AS [count_all_orders], crm1.date_order AS [date_order],COUNT(crm2.date_order) AS [[count_cancelled_orders]
FROM            #TemporaryTable crm1 WITH (nolock)
left outer join #TemporaryTable crm2 WITH (nolock) on crm2.id = crm1.id and crm2.order_status ='Cancelled'
GROUP BY crm1.date_order,crm2.date_order

SQL小提琴

端点将有一个日期并计数所有订单+计数已取消的订单。

​

​

现在，这一日期翻了一番，并给出了坏的价值。请用SQL小提琴检查一下，并帮助我。谢谢!

Answer 1

不需要加入，只需使用条件聚合：

COUNT(someNonNullValue)和COUNT(*)是一回事

SELECT
  COUNT(*) AS count_all_orders,
  crm1.date_order,
  COUNT(CASE WHEN crm2.order_status = 'Cancelled' THEN 1 END) AS count_cancelled_orders
FROM #TemporaryTable crm
GROUP BY
  crm.date_order;

NOLOCK是一个非常糟糕的主意。只在特殊情况下使用。