如何在sqlserver中获取订单总数 [英] How to get total count of orders in sqlserver

问题描述

我的表名是ShipmentDetails，其中包含已发货订单的详细信息，我希望在特定日期(由应用程序给出的特定日期)中获取已发货订单的计数，即基于我必须在一天之内返回总未发货订单的日期，从前端开始.这是基于输入的简单方法，但是在此我需要根据ItemSku筛选已发货的订单，这是ShipmentDetails表的列名，此列包含不同的产品，例如

Hi,
i have table name as ShipmentDetails that contains shipped orders details and i want to get shipped orders count in a particular date that will be given by the application i.e from frontend based on the date i have to return total no of shipped orders in a day.This is simple to get based on input but here i need to filter shipped orders based on ItemSku this is column name of ShipmentDetails table this column contains the different products like

NOTEBOOK01,NOTEPAD01,5X7SNP,4X5SGC,3X5SNC,2X3SGC

基于此列，我需要获取诸如ConsolidateCount和NonConsolidateCount之类的计数.
一次将ConsolidateCount与产品一起记录为NOTEBOOK01，NOTEPAD01、5X7SNP.非ConsolidateCount将与产品一起计数为4X5SGC，3X5SNC，2X3SGC.
ConsolidateCount表示将多个产品作为一个货件.
NonConsolidateCount表示每种产品都有一个发货计数.

下表数据显示了一些示例:

based on this column i need to get the count like ConsolidateCount and NonConsolidateCount.
ConsolidateCount will be count with the products are NOTEBOOK01,NOTEPAD01,5X7SNP as one shipmentCount.NonConsolidateCount will be count with the products are 4X5SGC,3X5SNC,2X3SGC.
ConsolidateCount means multiple products as one shipment.
NonConsolidateCount means each product has one shipment count.

the following table data shows some sample:

SubOrderId  ItemSkuType Id  CreatedDateTime
720090  5X7SNP  750712  2012-07-25 08:29:51.327
720090  4X5SGC  751086  2012-07-25 13:12:17.517
720090  3X5SNC  751265  2012-07-25 15:56:00.800
720090  2X3SGC  751432  2012-07-25 20:00:58.003
720090  NOTEBOOK01 751434  2012-07-25 20:00:58.003

此数据应作为一批货物返回合并计数为3 becoz的4X5SGC，3X5SNC，2X3SGC，而非合并计数为1 becoz 5X7SNP和NOTEBOOK01作为一次装运，即这两个均为合并产品.

请给我查询以获取合并项目和非合并项目的数量
结果表应为:

这些列是shipday consolidateCount NonConsolidateCount.
2012-07-25 1 3

This data should return consolidatecount is 3 becoz of 4X5SGC,3X5SNC,2X3SGC and Nonconsolidatecount is 1 becoz 5X7SNP and NOTEBOOK01 as one shipment i.e these two are consolidate products.

please give me query to get count of consolidated items and nonconsolidated items
the result table should like:

shipday consolidateCount NonConsolidateCount are the columns.
2012-07-25 1 3

推荐答案

我尝试使用表变量复制您的案例.已在SQL 2008中进行了测试.
希望该想法也可以在SQL 200上使用.

主要思路是汇总合并产品并转换为BIT .
不孤单只是和.

I have tried to replicate your case using a table variable. Which is tested in SQL 2008.
Hope the Idea will work on SQL 200 also.

The main Idea is to sum Consolidated Product and convert to BIT.
Non consolodated are just sum.

DECLARE @table TABLE(SubOrderId INT, ItemSkuType NVARCHAR(100), Id INT, CreatedDateTime DATETIME)
INSERT INTO @table
SELECT 720090 , '5X7SNP' , 750712,  '2012-07-25 08:29:51.327'
UNION
SELECT 720090,  '4X5SGC' , 751086  ,'2012-07-25 13:12:17.517'
UNION
SELECT 720090,  '3X5SNC'  ,751265  ,'2012-07-25 15:56:00.800'
UNION
SELECT 720090,  '2X3SGC'  ,751432  ,'2012-07-25 20:00:58.003'
UNION
SELECT 720090,  'NOTEBOOK01', 751434  ,'2012-07-25 20:00:58.003'

SELECT SUM
(
CASE WHEN ItemSkuType IN ('4X5SGC','3X5SNC','2X3SGC') THEN 1 ELSE 0 END
) AS ConsolidateCount ,
CONVERT(BIT,SUM
(
CASE WHEN ItemSkuType IN ('NOTEBOOK01','NOTEPAD01','5X7SNP') THEN 0 ELSE 1 END
)) AS NonConsolidateCount FROM @table

这篇关于如何在sqlserver中获取订单总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！