SQL问题 菜鸟求教了 一句一句解释谢谢了 给100分
SELECT '2012-02-01' as stDate,'2012-02-29'as endDate, A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID,
SUM(AD.EatMoney1) AS EatMoney1,SUM(AD.EatCnt1) AS EatCnt1, SUM(AD.EatMoney2)
AS EatMoney2,SUM(AD.EatCnt2) AS EatCnt2, SUM(AD.EatMoney3) AS EatMoney3,SUM(AD.EatCnt3)
AS EatCnt3, SUM(AD.EatMoney4) AS EatMoney4,SUM(AD.EatCnt4) AS EatCnt4, SUM(AD.EatMoney5)
AS EatMoney5,SUM(AD.EatCnt5) AS EatCnt5, SUM(AD.EatMoney6) AS EatMoney6,SUM(AD.EatCnt6) AS EatCnt6,
SUM(AD.OEatMoney) AS OEatMoney,SUM(AD.OEatCnt) AS OEatCnt, SUM(AD.SumEatMoney)-SUM(AD.WaterMoney)
AS SumEatMoney,SUM(AD.SumEatCnt)-SUM(AD.WaterCnt) AS SumEatCnt FROM AllDayData AD
INNER JOIN (SELECT CardID,UserID,UserName,DeptID FROM CardUser ) C ON AD.UserID = C.UserID
INNER JOIN Dept D ON C.DeptID = D.DeptId INNER JOIN AreaSet A ON D.AreaNo = A.AreaNo
where AD.Cdate>='2012-02-01' and AD.Cdate<='2012-02-29'
GROUP BY A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID
[解决办法]
Lz拆分下就知道了
SELECT xxx FROM AllDayData AD //从表中AllDayData查询数据，命名AllDayData别名为AD
'2012-02-01' as stDate//添加一列'2012-02-01'名称为stDate
SUM(AD.EatMoney1) AS EatMoney1求AllDayData表中的和命名为EatMoney1
这个应该发在SQL SERVER或ORACLE中。
[解决办法]
类似这样的语法,就是3个表内连接(inner join)加上一些条件过滤后汇总(Sum),别看代码长,没什么难的
select A.f1,B.f2,Sum(A.f2) as A1,Sum(B.f1) as B1 from Ainner join B on A.ID=B.IDinner join C on B.ID=C.ID where A.f3='条件'group by A.f1,B.f2
[解决办法]
SELECT '2012-02-01' as stDate,'2012-02-29'as endDate, A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID,
SUM(AD.EatMoney1) AS EatMoney1,SUM(AD.EatCnt1) AS EatCnt1, SUM(AD.EatMoney2)
AS EatMoney2,SUM(AD.EatCnt2) AS EatCnt2, SUM(AD.EatMoney3) AS EatMoney3,SUM(AD.EatCnt3)
AS EatCnt3, SUM(AD.EatMoney4) AS EatMoney4,SUM(AD.EatCnt4) AS EatCnt4, SUM(AD.EatMoney5)
AS EatMoney5,SUM(AD.EatCnt5) AS EatCnt5, SUM(AD.EatMoney6) AS EatMoney6,SUM(AD.EatCnt6) AS EatCnt6,
SUM(AD.OEatMoney) AS OEatMoney,SUM(AD.OEatCnt) AS OEatCnt, SUM(AD.SumEatMoney)-SUM(AD.WaterMoney)
AS SumEatMoney,SUM(AD.SumEatCnt)-SUM(AD.WaterCnt) AS SumEatCnt FROM AllDayData AD
INNER JOIN (SELECT CardID,UserID,UserName,DeptID FROM CardUser ) C ON AD.UserID = C.UserID
INNER JOIN Dept D ON C.DeptID = D.DeptId INNER JOIN AreaSet A ON D.AreaNo = A.AreaNo
where AD.Cdate>='2012-02-01' and AD.Cdate<='2012-02-29'
GROUP BY A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID
解释如下：
选择 '2012-02-01' 栏位名定义为 stDate,'2012-02-29' 栏位名定义为 endDate,A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID,
加总(AD.EatMoney1) 栏位名定义为 EatMoney1,加总(AD.EatCnt1) 栏位名定义为 EatCnt1, 加总(AD.EatMoney2)
栏位名定义为 EatMoney2,加总(AD.EatCnt2) 栏位名定义为 EatCnt2, 加总(AD.EatMoney3) 栏位名定义为 EatMoney3,加总(AD.EatCnt3)
栏位名定义为 EatCnt3, 加总(AD.EatMoney4) 栏位名定义为 EatMoney4,加总(AD.EatCnt4) 栏位名定义为 EatCnt4, 加总(AD.EatMoney5)
栏位名定义为 EatMoney5,加总(AD.EatCnt5) 栏位名定义为 EatCnt5, 加总(AD.EatMoney6) 栏位名定义为 EatMoney6,加总(AD.EatCnt6) 栏位名定义为 EatCnt6,
加总(AD.OEatMoney) 栏位名定义为 OEatMoney,加总(AD.OEatCnt) 栏位名定义为 OEatCnt, 加总(AD.SumEatMoney）减去 加总(AD.WaterMoney)
栏位名定义为 SumEatMoney,加总(AD.SumEatCnt)减去 加总(AD.WaterCnt) 栏位名定义为 SumEatCnt
从表 AllDayData 别名 AD
内联 INNER JOIN 表 (SELECT CardID,UserID,UserName,DeptID FROM CardUser ) 别名 C ON AD.UserID = C.UserID 关联条件
内联 INNER JOIN 表 Dept 别名 D ON C.DeptID = D.DeptId 关联条件
内联 INNER JOIN 表 AreaSet 别名 A ON D.AreaNo = A.AreaNo 关联条件
查询记录的条件 where AD.Cdate>='2012-02-01' and AD.Cdate<='2012-02-29'
按A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID分组
GROUP BY A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID
[解决办法]
--查询需要的字段值，'2012-02-01' as stDate,'2012-02-29'as endDate 为自定义的字段，
--sum(XXX)为求和
SELECT '2012-02-01' as stDate,'2012-02-29'as endDate, A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID,
SUM(AD.EatMoney1) AS EatMoney1,SUM(AD.EatCnt1) AS EatCnt1, SUM(AD.EatMoney2)
AS EatMoney2,SUM(AD.EatCnt2) AS EatCnt2, SUM(AD.EatMoney3) AS EatMoney3,SUM(AD.EatCnt3)
AS EatCnt3, SUM(AD.EatMoney4) AS EatMoney4,SUM(AD.EatCnt4) AS EatCnt4, SUM(AD.EatMoney5)
AS EatMoney5,SUM(AD.EatCnt5) AS EatCnt5, SUM(AD.EatMoney6) AS EatMoney6,SUM(AD.EatCnt6) AS EatCnt6,
SUM(AD.OEatMoney) AS OEatMoney,SUM(AD.OEatCnt) AS OEatCnt, SUM(AD.SumEatMoney)-SUM(AD.WaterMoney)
AS SumEatMoney,SUM(AD.SumEatCnt)-SUM(AD.WaterCnt) AS SumEatCnt
--从这个表AllDayData取部分字段（就是AD.*那些字段，AD为表AllDayData的临时别名）
FROM AllDayData AD
--INNER JOIN唯一关联关系，也就是表AllDayData跟（...）C 的一一对应关系的数据
INNER JOIN (SELECT CardID,UserID,UserName,DeptID FROM CardUser ) C ON AD.UserID = C.UserID
INNER JOIN Dept D ON C.DeptID = D.DeptId INNER JOIN AreaSet A ON D.AreaNo = A.AreaNo
--增加条件，取日期范围内的数据
where AD.Cdate>='2012-02-01' and AD.Cdate<='2012-02-29'
--分组求和，聚合函数
GROUP BY A.AreaName,D.DeptID,D.DeptName,AD.UserID,C.UserName,C.CardID
