先把当期和比较期的数据取出来,然后再用下面的条件过滤AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%' )
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' )
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%' )
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' )
解决方案 »
- 一个OR运算符的组合查询抛出异常的问题。。麻烦了
- JDBC get String from SQL SERVER 时部分字体发生乱码
- Eclipse 调用存储过程
- 高手帮忙!SQL数据表的奇怪问题!
- 急,各位版主看看没有这样的函数,没有也告诉我一声,与 Boolean_expression?值1:值2 对应的在 sql中是那个函数??
- 数据库存储过程问题!比较急!
- 关于数据导入的问题
- 有没有这样的SQL函数:判断操作系统文件是否存在
- 求写SQL:关于left join的(Access2000)
- 在Sql Server的T-Sql中,有无类似C语言的Scanf语句?
- mysql重装错误,求指导
- sql查询
谢谢,我能弱弱的问一句,私信怎么玩的么?我第一次来CSDN,不会玩
c_adno AS 部门,
c_ccode AS 商品类别,
c_gcode AS 商品编码,
t.c_name AS 商品名称,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_number_sale ELSE 0 END
) AS 当期销售数量,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_number_sale END
) AS 比较期销售数量,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_sale ELSE 0 END
) AS 当期销售金额,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_sale END
) AS 比较期销售金额,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN c_at_sale ELSE 0 END
) AS 当期销售成本,
SUM(
CASE
WHEN DATEDIFF(DAY, t.c_dt, @StartDate_Cur) <= 0
AND DATEDIFF(DAY, t.c_dt, @EndDate_Cur) >= 0 THEN 0 ELSE c_at_sale END
) AS 比较期销售成本
FROM (
--当期数据
SELECT c_store_id,
c_adno,
c_ccode,
c_gcode,
t.c_name,
c_dt,
c_number_sale,
c_sale,
c_at_sale
FROM tbs_d_gds t(NOLOCK)
WHERE t.c_dt >= @StartDate_Cur
AND t.c_dt < @EndDate_Cur
AND EXISTS (
SELECT 1
FROM dbo.uf_split_string(@分类, ',') a
WHERE t.c_ccode LIKE a.c_str + '%'
)
AND EXISTS (
SELECT 1
FROM dbo.uf_split_string(@机构代码, ',') a
WHERE c_store_id LIKE a.c_str + '%'
)
AND (
ISNULL(@商品编码, '') = ''
OR EXISTS (
SELECT 1
FROM dbo.uf_split_string(@商品编码, ',') a
WHERE t.c_gcode LIKE a.c_str + '%'
)
)
UNION ALL
--比较期
SELECT c_store_id,
c_adno,
c_ccode,
c_gcode,
t.c_name,
c_dt,
c_number_sale,
c_sale,
c_at_sale
FROM tbs_d_gds t(NOLOCK)
WHERE t.c_dt >= @StartDate_Old
AND t.c_dt < @EndDate_Old
AND EXISTS (
SELECT 1
FROM dbo.uf_split_string(@分类, ',') a
WHERE t.c_ccode LIKE a.c_str + '%'
)
AND EXISTS (
SELECT 1
FROM dbo.uf_split_string(@机构代码, ',') a
WHERE c_store_id LIKE a.c_str + '%'
)
AND (
ISNULL(@商品编码, '') = ''
OR EXISTS (
SELECT 1
FROM dbo.uf_split_string(@商品编码, ',') a
WHERE t.c_gcode LIKE a.c_str + '%'
)
)
)t
LEFT JOIN tb_store s
ON t.c_store_id = s.c_id
GROUP BY
c_store_id,
c_adno,
c_ccode,
c_gcode,
t.c_name,
s.c_sname帮你格式化一下
语句是做同比分析的,今年和去年的同比分析。
今年和去年,那意思是不是就是@StartDate_Cur,@EndDate_Cur和@StartDate_Old,@EndDate_Old这两组日期不会出现交叉?如果是的话,那把语句简化试试:
SELECT
c_store_id + s.c_sname AS 机构 ,
c_adno AS 部门,
c_ccode AS 商品类别,
c_gcode as 商品编码,
t.c_name as 商品名称,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_number_sale else 0 end) as 当期销售数量,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_number_sale end) as 比较期销售数量,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_sale else 0 end) as 当期销售金额,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_sale end) as 比较期销售金额,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then c_at_sale else 0 end) as 当期销售成本,
sum(case when DATEDIFF(DAY,t.c_dt,@StartDate_Cur) <= 0 and DATEDIFF(DAY,t.c_dt,@EndDate_Cur) >= 0 then 0 else c_at_sale end) as 比较期销售成本
FROM
tbs_d_gds t(nolock)
LEFT JOIN tb_store s ON t.c_store_id = s.c_id
where ((t.c_dt >= @StartDate_Cur and t.c_dt < @EndDate_Cur) OR (t.c_dt >= @StartDate_Old and t.c_dt < @EndDate_Old)
AND EXISTS (select 1 from dbo.uf_split_string(@分类,',') a where t.c_ccode like a.c_str+'%')
AND EXISTS (select 1 from dbo.uf_split_string(@机构代码,',') a where c_store_id like a.c_str+ '%')
and (ISNULL(@商品编码,'')='' or exists (select 1 from dbo.uf_split_string(@商品编码,',') a where t.c_gcode like a.c_str+'%' ))
GROUP BY c_store_id,c_adno,c_ccode,c_gcode,t.c_name,s.c_sname
如果软件优化很好了,但效果还是不明显的话,那楼主你只能升级服务器硬件了。
比如,加大内存,升级CPU,加大带宽,机械硬盘改固态盘(这个效果一般比较明显,固盘IO效率确实比机械盘高了不少)