用case修改sql查询 [英] Sql query modify with case

问题描述

这是我的SQL服务器查询

这里table1给出了Salesman每天所做的所有数据，而table2给出了他们销售的2个特殊项目的特殊数据

所以我需要来自table1和table2的所有发票和金额我需要特殊物品发票，



我们可以优化此查询，我不必编写table2查询，如果我在同一个tabel1语句中写入，是否有任何casae查询。

This is My SQL SERVER QUERY
Here table1 is giving all the data made in per day by Salesman whereas table2 is giving special data of 2 special item which they ar selling
So i need all the invoice and sum from table1 and from table2 i need special item invoice,

Can we optimise this query where i dont have to write table2 query , Is there any casae query if i write in same tabel1 statement.

select m.*,Of_Order_Invoices_SpecialItem from (

select SUPERVISOR,Salesman_name,staffid,
ROUND(sum(Carton),0) AS O_CARTON,ROUND(SUM(Amount),0) AS ORD_AMNT	
ROUND(COUNT(DISTINCT(ORDER_ID)),2) as [#Of_Order_Invoices] ,ordt
from t_opdata_3 OP	
INNER JOIN T_TREE_ITEMS TI ON OP.ITEM=TI.ITEM_CODE
INNER JOIN daily_total_shoproute ON STAFFID=SALESMANCODE

WHERE order_date =format(getdate() ,'yyyy-MM-dd')
Order_month =MONTH(GETDATE()) and year =YEAR(GETDATE())
GROUP by SUPERVISOR,Salesman_name ,staffid
) table1
left join

(

select SUPERVISOR,Salesman_name,staffid,
ROUND(sum(Carton),0) AS O_CARTON,ROUND(SUM(Amount),0) AS ORD_AMNT	,
ROUND(COUNT(DISTINCT(ORDER_ID)),2) as Of_Order_Invoices_SpecialItem,ordt
from t_opdata_3 OP	
INNER JOIN T_TREE_ITEMS TI ON OP.ITEM=TI.ITEM_CODE
INNER JOIN daily_total_shoproute ON STAFFID=SALESMANCODE
---INNER JOIN T_TARGET T ON TU.ManagerCode=T.managercode

WHERE order_date =format(getdate() ,'yyyy-MM-dd') and O_Item in ('4567','23478')
Order_month =MONTH(GETDATE()) and year =YEAR(GETDATE())
GROUP by SUPERVISOR,Salesman_name ,staffid
) table2 on table1.staffid=table2.staffid

我尝试过：



我尝试将case放在table1中，其中item（''，''），但是我需要逐项完成并给予错误的结果

What I have tried:

I Tried by putting case if in table1 where item in ('',''),But then I need to do group by item and giving Wrong results

推荐答案

您可以在此查询中使用交叉联接

you can use Cross join in this query

这篇关于用case修改sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！