有两个表,
订单产品表(ProductOrderItem)
字段标题 字段名称 数据属性
Id标记 productorderitemid 主键
orderid orderid varchar
产品id productid int
总库产品id merchandiseid int
供货商id providerid int
产品通用名称 productname varchar
无效订单原因表(orderInvalid)
字段标题 字段名称 数据属性
Id标记 orderInvalidid 自动增值int
订单编号 orderid int
无效原因 InvalidContent varchar
提交时间 createtime datetime 我想做一个查询实现以下功能:谢谢各位大侠 商品名称 无效订单数 无效原因
缺货 快递不支持 顾客联系不上
仙卡疤痕敌 37 30 5 2 好迪 25 20 5 0
要求就是要在一个JSP页面实现上面这个功能。按照商品名统计无效订单数,及各种 无效原因的无效订单数,设计两个表关联查询及子查询,谢谢啊
订单产品表(ProductOrderItem)
字段标题 字段名称 数据属性
Id标记 productorderitemid 主键
orderid orderid varchar
产品id productid int
总库产品id merchandiseid int
供货商id providerid int
产品通用名称 productname varchar
无效订单原因表(orderInvalid)
字段标题 字段名称 数据属性
Id标记 orderInvalidid 自动增值int
订单编号 orderid int
无效原因 InvalidContent varchar
提交时间 createtime datetime 我想做一个查询实现以下功能:谢谢各位大侠 商品名称 无效订单数 无效原因
缺货 快递不支持 顾客联系不上
仙卡疤痕敌 37 30 5 2 好迪 25 20 5 0
要求就是要在一个JSP页面实现上面这个功能。按照商品名统计无效订单数,及各种 无效原因的无效订单数,设计两个表关联查询及子查询,谢谢啊
我是这样写的,但是结果并不正确啊
Sum(Case when A.InvalidContent='缺货' as 1 then else 0 end ) as '缺货' ,
Sum(Case when A.InvalidContent='快递不支持' as 1 then else 0 end) as '快递不支持' ,
Sum(Case when A.InvalidContent='顾客联系不上' as 1 then else 0 end) as '顾客联系不上' ,
from orderInvalid A Inner join ProductOrderItem B
on A.orderid =B.orderid group by B.productname
Sum(Case when A.InvalidContent='缺货' as 1 then else 0 end ) as '缺货' ,
Sum(Case when A.InvalidContent='快递不支持' as 1 then else 0 end) as '快递不支持' ,
Sum(Case when A.InvalidContent='顾客联系不上' as 1 then else 0 end) as '顾客联系不上'
from orderInvalid A Inner join ProductOrderItem B
on A.orderid =B.orderid group by B.productname
上一个多写了个逗号!
Sum(Case when A.InvalidContent='缺货' then 1 else 0 end ) as '缺货' ,
Sum(Case when A.InvalidContent='快递不支持' then 1 else 0 end) as '快递不支持' ,
Sum(Case when A.InvalidContent='顾客联系不上' then 1 else 0 end) as '顾客联系不上'
from orderInvalid A Inner join ProductOrderItem B
on A.orderid =B.orderid group by B.productname
不好意思,上两个没测试,写错了!这个试一下,看是否满足你的需求!
create table #ProductOrderItem(productname varchar(20),orderid varchar(20))
create table #orderInvalid(orderid int,InvalidContent varchar(20))
--插入测试数据
insert into #ProductOrderItem
select '仙卡疤痕敌','00001' union all
select '仙卡疤痕敌','00002' union all
select '仙卡疤痕敌','00003' union all
select '仙卡疤痕敌','00004' union all
select '仙卡疤痕敌','00005' union all
select '仙卡疤痕敌','00006' union all
select '好迪','00007' union all
select '好迪','00008' union all
select '好迪','00009' union all
select '好迪','000010' union all
select '好迪','000011' union all
select '好迪','000012' union all
select '好迪','000013'
insert into #orderInvalid
select 1,'缺货' union all
select 2,'快递不支持' union all
select 3,'快递不支持' union all
select 4,'顾客联系不上' union all
select 5,'顾客联系不上' union all
select 6,'顾客联系不上' union all
select 7,'缺货' union all
select 8,'缺货' union all
select 9,'快递不支持' union all
select 10,'顾客联系不上' union all
select 11,'顾客联系不上' union all
select 12,'顾客联系不上' union all
select 13,'顾客联系不上'DECLARE @COL_A NVARCHAR(4000)
DECLARE @COL NVARCHAR(4000)
DECLARE @COL_SUM NVARCHAR(4000)
DECLARE @SQL NVARCHAR(4000)create table #Order_invalid_tmp(productname varchar(20),orderid varchar(20),InvalidContent varchar(20),CNT int)
INSERT INTO #Order_invalid_tmp
SELECT A.productname,B.orderid,B.InvalidContent,1
FROM #ProductOrderItem A LEFT JOIN #orderInvalid B ON
A.orderid = B.orderidSELECT * FROM #Order_invalid_tmp
SELECT
@COL_A=ISNULL(@COL_A+',','')+'TBL_A.['+InvalidContent+'] AS ['+InvalidContent+']',
@COL=ISNULL(@COL+',','')+'['+InvalidContent+']',
@COL_SUM =ISNULL(@COL_SUM+'+','')+'TBL_A.['+InvalidContent+']'
FROM (SELECT DISTINCT TOP 100 PERCENT InvalidContent AS InvalidContent FROM #Order_invalid_tmp
) TBL_TMPSELECT @COL_A,@COL,@COL_SUMSET @SQL = N' SELECT TBL_A.productname AS ''商品名称'',('+@COL_SUM+') AS ''无效订单数'','+@COL_A+
N' FROM '+
N' (SELECT productname,'+@COL+' FROM (SELECT productname,CNT,InvalidContent FROM #Order_invalid_tmp) AS PIVOT_SOURCE '+
N' PIVOT (SUM(CNT) FOR InvalidContent IN ('+@COL+')) AS PIVOTTABLE ) TBL_A 'EXECUTE (@SQL);DROP TABLE #ProductOrderItem;
DROP TABLE #orderInvalid;
DROP TABLE #Order_invalid_tmp;
count(1) as '无效订单数'
Sum(Case when A.InvalidContent='缺货' then 1 else 0 end ) as '缺货' ,
Sum(Case when A.InvalidContent='快递不支持' then 1 else 0 end) as '快递不支持' ,
Sum(Case when A.InvalidContent='顾客联系不上' then 1 else 0 end) as '顾客联系不上'
from orderInvalid A Inner join ProductOrderItem B
on A.orderid =B.orderid group by B.productname
count(1) as '无效订单数',
Sum(Case when A.InvalidContent='缺货' then 1 else 0 end ) as '缺货' ,
Sum(Case when A.InvalidContent='快递不支持' then 1 else 0 end) as '快递不支持' ,
Sum(Case when A.InvalidContent='顾客联系不上' then 1 else 0 end) as '顾客联系不上'
from orderInvalid A Inner join ProductOrderItem B
on A.orderid =B.orderid group by B.productname