请问一个SQL查询语句是否标准
请教一个SQL查询语句是否标准
查询语句如下
select 物料汇总名,sum(产量) as 产量,sum(库存量) as 库存量,sum(发货数量) as 发货数量,sum(慢流数量) as 慢流,sum(即时库存) as 即时库存,sum(待检数量) as 待见数量
from (SELECT A.FItemID as 物料内码,A.FNumber as 物料代码,(case when charindex('物料1',A.fname)>0 then '物料1' when charindex('物料2',A.fname)>0 then '物料2' else '' end) as 物料汇总名,
A.fname as 物料名称,(CASE WHEN sum(b.FAuxQtyFinish) IS NOT NULL THEN sum(b.FAuxQtyFinish) ELSE 0 END) as 产量,
((CASE WHEN C.fqty IS NOT NULL THEN C.fqty ELSE 0 END)+(CASE WHEN e.a IS NOT NULL THEN e.a ELSE 0 END)) as 库存量,(CASE WHEN D.FAuxQty IS NOT NULL THEN D.FAuxQty ELSE 0 END) as 发货数量,
(CASE WHEN slow.fqty1 IS NOT NULL THEN slow.fqty1 ELSE 0 END) as 慢流数量,
(CASE WHEN C.fqty IS NOT NULL THEN C.fqty ELSE 0 END) as 即时库存,(CASE WHEN e.a IS NOT NULL THEN e.a ELSE 0 END) as 待检数量
FROM (select FItemID,FName,FNumber from t_ICItemCore as t where FItemID in(6231,6233,11174,11719,6235,6227,6229,11717,11156)) A
LEFT JOIN (select ICMORpt.FDate as fdate,ICMORptEntry.FSourceBillNo as FSourceBillNo,
ICMORptEntry.FAuxQtyFinish as FAuxQtyFinish,ICMORptEntry.FItemID as FItemID
from ICMORpt,ICMORptEntry where ICMORpt.FInterID=ICMORptEntry.FInterID and ICMORpt.FDate>(GETDATE()-1)) B
ON A.FItemID=B.FItemID
left join (select SUM(ICInventory.FQty) AS fqty,ICInventory.FItemID from ICInventory group by ICInventory.FItemID) C on C.FItemID=A.FItemID
left join (select SUM(ICStockBillEntry.FAuxQty) AS FAuxQty,ICStockBillEntry.FItemID as fitemid
from ICStockBillEntry,ICStockBill where ICStockBillEntry.FInterID=ICStockBill.FInterID AND ICStockBill.fdate>(GETDATE()-1) and (ICStockBill.FBillNo like '%DL%' or ICStockBill.FBillNo like 'xout%')
group by ICStockBillEntry.FItemID) D on D.fitemid=A.FItemID
left join (SELECT sum(ICQCBill.FCheckQty) AS qcaux,sum(QMIcmoCkRequestEntry.FAuxQtyCheck) as yiaux,(sum(ICQCBill.FCheckQty)-sum(QMIcmoCkRequestEntry.FAuxQtyCheck)) as a,t_ICItemCore.FItemID as fitemid
FROM ICQCBill INNER JOIN
QMIcmoCkRequestEntry ON
ICQCBill.FInStockInterID = QMIcmoCkRequestEntry.FInterID AND
ICQCBill.FItemID = QMIcmoCkRequestEntry.FItemID AND
ICQCBill.FBatchNo = QMIcmoCkRequestEntry.FBatchNo INNER JOIN
t_ICItemCore ON ICQCBill.FItemID = t_ICItemCore.FItemID INNER JOIN
QMIcmoCkRequest ON QMIcmoCkRequest.FInterID=QMIcmoCkRequestEntry.FInterID inner join
ICMO on ICMO.FInterID=QMIcmoCkRequest.FICMOInterID
WHERE (ICQCBill.FResult = 13556) AND (ICQCBill.FTranType = 713) AND
(ICQCBill.FCheckTimes = 1) AND (ICQCBill.FCheckQty > QMIcmoCkRequestEntry.FAuxQtyCheck) AND ICQCBill.fbillno <> 'FQC000443'
group by t_ICItemCore.FItemID) as e on e.fitemid=A.FItemID
left join (SELECT ICInventory.FItemID,SUM(ICInventory.fqty) as fqty1
FROM ICInventory
where ICInventory.FStockID='5724' and ICInventory.FQty>0 and (GETDATE()-ICInventory.FKFDate)>30
group by ICInventory.FItemID) as slow on slow.FItemID=A.FItemID
group by A.FItemID,A.fname,C.fqty,D.FAuxQty,e.a,slow.fqty1,A.FNumber) as yuan
group by 物料汇总名
order by charindex(cast(yuan.物料汇总名 as nvarchar(10)), '物料1,物料2')
我的问题是，虽然以上语句能够取出我想要的数据，但是红色字部分总感觉很别捏。但如果不按红色部分，写这么多group by条件，SQL系统又无法通过。
想请问我这样写是否标准呢？是否需要修改，谢谢。
------解决思路----------------------
--大部份简化一下语句和优化了一下条件，在日期列最好加上索引，楼主自己测一下看看结果是否同之前一致，自己检查一下
------解决思路----------------------
使用group by的sql，select 之後的數據必須是group by之後的字段或聚合函數計算出來的值。
很顯然select 中有A.fname，A.FNumber這兩個字段，所以不能去掉group by後面的這兩個字段。
另：1）表名長的可以是別名代替。
2）case when 字段 is not null then 字段 else 0 end ，可以採用isnull(字段,0)的方法。
3）查詢長的sql，可以分幾步來查詢。先插入到臨時表中，select * into #temp from tablename,最後再drop 掉就OK。
查询语句如下
select 物料汇总名,sum(产量) as 产量,sum(库存量) as 库存量,sum(发货数量) as 发货数量,sum(慢流数量) as 慢流,sum(即时库存) as 即时库存,sum(待检数量) as 待见数量
from (SELECT A.FItemID as 物料内码,A.FNumber as 物料代码,(case when charindex('物料1',A.fname)>0 then '物料1' when charindex('物料2',A.fname)>0 then '物料2' else '' end) as 物料汇总名,
A.fname as 物料名称,(CASE WHEN sum(b.FAuxQtyFinish) IS NOT NULL THEN sum(b.FAuxQtyFinish) ELSE 0 END) as 产量,
((CASE WHEN C.fqty IS NOT NULL THEN C.fqty ELSE 0 END)+(CASE WHEN e.a IS NOT NULL THEN e.a ELSE 0 END)) as 库存量,(CASE WHEN D.FAuxQty IS NOT NULL THEN D.FAuxQty ELSE 0 END) as 发货数量,
(CASE WHEN slow.fqty1 IS NOT NULL THEN slow.fqty1 ELSE 0 END) as 慢流数量,
(CASE WHEN C.fqty IS NOT NULL THEN C.fqty ELSE 0 END) as 即时库存,(CASE WHEN e.a IS NOT NULL THEN e.a ELSE 0 END) as 待检数量
FROM (select FItemID,FName,FNumber from t_ICItemCore as t where FItemID in(6231,6233,11174,11719,6235,6227,6229,11717,11156)) A
LEFT JOIN (select ICMORpt.FDate as fdate,ICMORptEntry.FSourceBillNo as FSourceBillNo,
ICMORptEntry.FAuxQtyFinish as FAuxQtyFinish,ICMORptEntry.FItemID as FItemID
from ICMORpt,ICMORptEntry where ICMORpt.FInterID=ICMORptEntry.FInterID and ICMORpt.FDate>(GETDATE()-1)) B
ON A.FItemID=B.FItemID
left join (select SUM(ICInventory.FQty) AS fqty,ICInventory.FItemID from ICInventory group by ICInventory.FItemID) C on C.FItemID=A.FItemID
left join (select SUM(ICStockBillEntry.FAuxQty) AS FAuxQty,ICStockBillEntry.FItemID as fitemid
from ICStockBillEntry,ICStockBill where ICStockBillEntry.FInterID=ICStockBill.FInterID AND ICStockBill.fdate>(GETDATE()-1) and (ICStockBill.FBillNo like '%DL%' or ICStockBill.FBillNo like 'xout%')
group by ICStockBillEntry.FItemID) D on D.fitemid=A.FItemID
left join (SELECT sum(ICQCBill.FCheckQty) AS qcaux,sum(QMIcmoCkRequestEntry.FAuxQtyCheck) as yiaux,(sum(ICQCBill.FCheckQty)-sum(QMIcmoCkRequestEntry.FAuxQtyCheck)) as a,t_ICItemCore.FItemID as fitemid
FROM ICQCBill INNER JOIN
QMIcmoCkRequestEntry ON
ICQCBill.FInStockInterID = QMIcmoCkRequestEntry.FInterID AND
ICQCBill.FItemID = QMIcmoCkRequestEntry.FItemID AND
ICQCBill.FBatchNo = QMIcmoCkRequestEntry.FBatchNo INNER JOIN
t_ICItemCore ON ICQCBill.FItemID = t_ICItemCore.FItemID INNER JOIN
QMIcmoCkRequest ON QMIcmoCkRequest.FInterID=QMIcmoCkRequestEntry.FInterID inner join
ICMO on ICMO.FInterID=QMIcmoCkRequest.FICMOInterID
WHERE (ICQCBill.FResult = 13556) AND (ICQCBill.FTranType = 713) AND
(ICQCBill.FCheckTimes = 1) AND (ICQCBill.FCheckQty > QMIcmoCkRequestEntry.FAuxQtyCheck) AND ICQCBill.fbillno <> 'FQC000443'
group by t_ICItemCore.FItemID) as e on e.fitemid=A.FItemID
left join (SELECT ICInventory.FItemID,SUM(ICInventory.fqty) as fqty1
FROM ICInventory
where ICInventory.FStockID='5724' and ICInventory.FQty>0 and (GETDATE()-ICInventory.FKFDate)>30
group by ICInventory.FItemID) as slow on slow.FItemID=A.FItemID
group by A.FItemID,A.fname,C.fqty,D.FAuxQty,e.a,slow.fqty1,A.FNumber) as yuan
group by 物料汇总名
order by charindex(cast(yuan.物料汇总名 as nvarchar(10)), '物料1,物料2')
我的问题是，虽然以上语句能够取出我想要的数据，但是红色字部分总感觉很别捏。但如果不按红色部分，写这么多group by条件，SQL系统又无法通过。
想请问我这样写是否标准呢？是否需要修改，谢谢。
------解决思路----------------------
本帖最后由 roy_88 于 2014-12-01 11:27:30 编辑
GETDATE()--注意有精确度，有时分秒需要考虑自己的需求--大部份简化一下语句和优化了一下条件，在日期列最好加上索引，楼主自己测一下看看结果是否同之前一致，自己检查一下
SELECT CASE WHEN CHARINDEX('物料1', A.fname) > 0 THEN '物料1'
WHEN CHARINDEX('物料2', A.fname) > 0 THEN '物料2'
ELSE ''
END AS 物料汇总名
,CASE WHEN SUM(c.FAuxQtyFinish) IS NOT NULL THEN SUM(c.FAuxQtyFinish)
ELSE 0
END AS 产量
,SUM(CASE WHEN C.fqty IS NOT NULL THEN C.fqty
ELSE 0
END + CASE WHEN f.a IS NOT NULL THEN f.a
ELSE 0
END) AS 库存量
,SUM(CASE WHEN e.FAuxQty IS NOT NULL THEN e.FAuxQty
ELSE 0
END) AS 发货数量
,SUM(CASE WHEN h.fqty1 IS NOT NULL THEN h.fqty1
ELSE 0
END) AS 慢流数量
,SUM(CASE WHEN C.fqty IS NOT NULL THEN C.fqty
ELSE 0
END) AS 即时库存
,SUM(CASE WHEN f.a IS NOT NULL THEN f.a
ELSE 0
END) AS 待检数量
FROM t_ICItemCore AS a
LEFT JOIN ( ICMORpt AS b
INNER JOIN ICMORptEntry AS c ON b.FInterID = c.FInterID
AND b.FDate > GETDATE()
- 1
) ON a.FItemID = b.FItemID
LEFT JOIN ( SELECT SUM(FQty) AS fqty
,FItemID
FROM ICInventory
GROUP BY FItemID
) AS d ON d.FItemID = a.FItemID
LEFT JOIN ( SELECT a.FItemID
,SUM(a.FAuxQty) AS FAuxQty
FROM ICStockBillEntry AS a
INNER JOIN ICStockBill AS b ON a.FInterID = b.FInterID
AND b.fdate > GETDATE()
- 1
GROUP BY a.FItemID
) AS e ON e.FItemID = a.FItemID
LEFT JOIN ( SELECT SUM(a.FCheckQty) AS qcaux
,SUM(b.FAuxQtyCheck) AS yiaux
,SUM(a.FCheckQty) - SUM(b.FAuxQtyCheck) AS a
,c.FItemID AS fitemid
FROM ICQCBill AS a
INNER JOIN QMIcmoCkRequestEntry AS b ON a.FInStockInterID = b.FInterID
AND a.FItemID = b.FItemID
AND a.FBatchNo = b.FBatchNo
INNER JOIN t_ICItemCore AS c ON a.FItemID = c.FItemID
INNER JOIN QMIcmoCkRequest AS d ON d.FInterID = b.FInterID
INNER JOIN ICMO as e ON e.FInterID = d.FICMOInterID
WHERE ( a.FResult = 13556 )
AND ( a.FTranType = 713 )
AND ( a.FCheckTimes = 1 )
AND ( a.FCheckQty > b.FAuxQtyCheck )
AND a.fbillno <> 'FQC000443'
GROUP BY c.FItemID
) AS f ON f.FItemID = a.FItemID
LEFT JOIN ( SELECT FItemID
,SUM(fqty) AS fqty1
FROM ICInventory
WHERE FStockID = '5724'
AND FQty > 0
AND FKFDate > GETDATE() - 30
GROUP BY FItemID
) AS h ON h.FItemID = a.FItemID
GROUP BY CASE WHEN CHARINDEX('物料1', A.fname) > 0 THEN '物料1'
WHEN CHARINDEX('物料2', A.fname) > 0 THEN '物料2'
ELSE ''
END
ORDER BY CHARINDEX(a.fname, '物料1,物料2')
------解决思路----------------------
使用group by的sql，select 之後的數據必須是group by之後的字段或聚合函數計算出來的值。
很顯然select 中有A.fname，A.FNumber這兩個字段，所以不能去掉group by後面的這兩個字段。
另：1）表名長的可以是別名代替。
2）case when 字段 is not null then 字段 else 0 end ，可以採用isnull(字段,0)的方法。
3）查詢長的sql，可以分幾步來查詢。先插入到臨時表中，select * into #temp from tablename,最後再drop 掉就OK。