问题描述
这是我的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
推荐答案
这篇关于用case修改sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！