sql语句问题

Question

SELECT  SalesManSysNo ,
                            SUM(dbo.DO_Master.CashPay) FillPrice ,
                            SUM(Quantity) FillNumber
                    FROM    dbo.SO_Master Smt
                            LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
                            LEFT JOIN dbo.DO_Item ON dbo.DO_Master.SysNo = dbo.DO_Item.DOSysNo
                    WHERE   SalesManSysNo = 1
                            AND dbo.DO_Master.OutTime >= '2011-05-01'
                            AND dbo.DO_Master.OutTime <= '2011-05-31'
                            AND dbo.DO_Master.Status = 1
                            AND SOType=3
                    GROUP BY SalesManSysNo

Answer 1

SELECT  SalesManSysNo ,
                            SUM(dbo.DO_Master.CashPay) FillPrice ,
                            SUM(Quantity) FillNumber
                    FROM    dbo.SO_Master Smt
                            LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
                            LEFT JOIN dbo.DO_Item ON dbo.DO_Master.SysNo = dbo.DO_Item.DOSysNo
                    WHERE   SalesManSysNo = 1
                            AND dbo.DO_Master.OutTime >= '2011-05-01'
                            AND dbo.DO_Master.OutTime <= '2011-05-31'
                            AND dbo.DO_Master.Status = 1
                            AND SOType=3
                    GROUP BY SalesManSysNo

Answer 2

SELECT  SalesManSysNo ,
                            SUM(dbo.DO_Master.CashPay) FillPrice ,
                            SUM(Quantity) FillNumber
                    FROM    dbo.SO_Master Smt
                            LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
                            LEFT JOIN dbo.DO_Item ON dbo.DO_Master.SysNo = dbo.DO_Item.DOSysNo                    WHERE   SalesManSysNo = 1
                            AND dbo.DO_Master.OutTime >= '2011-05-01'
                            AND dbo.DO_Master.OutTime <= '2011-05-31'
                            AND dbo.DO_Master.Status = 1
                            AND SOType=3
                    GROUP BY SalesManSysNo

Answer 3

加上红色哪里出问题了 
加上那句会有重复数据 ，表DO_Item是dbo.DO_Master的子表
现在就是怎么才能没有重复数据

Answer 4

--最好都起个别名，然后按别名来写条件，记得最好所有列都加上所属别名。SELECT  a.SalesManSysNo ,
                            SUM(a.CashPay) FillPrice ,
                            SUM(a.Quantity) FillNumber
                    FROM    dbo.SO_Master a 
                            LEFT JOIN dbo.DO_Master b ON a.SysNo = b.SOSysNo
                            LEFT JOIN dbo.DO_Item c ON a.SysNo = c.DOSysNo                    WHERE   a.SalesManSysNo = 1
                            AND a.OutTime >= '2011-05-01'
                            AND a.OutTime <= '2011-05-31'
                            AND a.Status = 1
                            AND a.SOType=3
                    GROUP BY a.SalesManSysNo

Answer 5

SELECT  SalesManSysNo ,
                            SUM(dbo.DO_Master.CashPay) FillPrice 
                    FROM    dbo.SO_Master Smt
                            LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
                    WHERE   SalesManSysNo = 1
                            AND dbo.DO_Master.OutTime >= '2011-05-01'
                            AND dbo.DO_Master.OutTime <= '2011-05-31'
                            AND dbo.DO_Master.Status = 1
                            AND SOType=3
                    GROUP BY SalesManSysNo
这样的数据是对的 

Answer 6

SELECT  SalesManSysNo ,
                            SUM(dbo.DO_Master.CashPay) FillPrice ,
                            SUM(Quantity) FillNumber
                    FROM    dbo.SO_Master Smt
                            LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
                            INNER JOIN dbo.DO_Item ON dbo.DO_Master.SysNo = dbo.DO_Item.DOSysNo
                    WHERE   SalesManSysNo = 1
                            AND dbo.DO_Master.OutTime >= '2011-05-01'
                            AND dbo.DO_Master.OutTime <= '2011-05-31'
                            AND dbo.DO_Master.Status = 1
                            AND SOType=3
                    GROUP BY SalesManSysNo
加上一句sql后数据有重复

Answer 7

你查询结果集里完全没用到这个dbo.DO_Item  ，你为什么要innerjoin 那不是多此一举么

Answer 8

刚刚写错了，是left jion 

Answer 9

SUM(Quantity) FillNumber 的quantity是DO_Item里面的字段

Answer 10

INNER JOIN dbo.DO_Item 尝试着改为
inner join (select DOSysNo,max([]) as [],sum([]) as [] from dbo.DO_Item where ... )b on ...这样子，确保你和主表连接时按连接条件子表只出现一条符合的数据

Answer 11

括号里忘了加group by 了，搂住自己加上看看吧！

Answer 12

条件没办法给~这里跟DO_Master表是关联的

Answer 13

;with f as
(
SELECT SalesManSysNo ,
  SUM(dbo.DO_Master.CashPay) FillPrice ,
  SUM(Quantity) FillNumber
  FROM dbo.SO_Master Smt
  LEFT JOIN dbo.DO_Master ON smt.SysNo = dbo.DO_Master.SOSysNo
  INNER JOIN dbo.DO_Item ON dbo.DO_Master.SysNo = dbo.DO_Item.DOSysNo
  WHERE SalesManSysNo = 1
  AND dbo.DO_Master.OutTime >= '2011-05-01'
  AND dbo.DO_Master.OutTime <= '2011-05-31'
  AND dbo.DO_Master.Status = 1
  AND SOType=3
  GROUP BY SalesManSysNo
)select * from f  t where FillPrice=(select max(FillPrice)  from f where SalesManSysNo=t.SalesManSysNo)