SELECT BanBie AS 班别, CiShu AS 次数, YuanPin AS 原矿品位, Y_MFe AS 原矿磁性铁,
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '夜班')
UNION
SELECT BanBie AS 班别, CiShu AS 次数, YuanPin AS 原矿品位, Y_MFe AS 原矿磁性铁,
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData AS ZJ_RealData_1
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '白班')现在有一段代码是这样的,我想要让夜班在前,白班在后,而且在夜班后和白班后都插入平均数,并且在前面显示“平均”字符,该怎么办啊?
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '夜班')
UNION
SELECT BanBie AS 班别, CiShu AS 次数, YuanPin AS 原矿品位, Y_MFe AS 原矿磁性铁,
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData AS ZJ_RealData_1
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '白班')现在有一段代码是这样的,我想要让夜班在前,白班在后,而且在夜班后和白班后都插入平均数,并且在前面显示“平均”字符,该怎么办啊?
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '夜班')
union
SELECT '平均 '+BanBie AS 班别, avg(CiShu) AS 次数, avg(YuanPin) AS 原矿品位, avg(Y_MFe) AS 原矿磁性铁,
avg(JingPin) AS 精矿品位, avg(ShuiFen) AS 水分, AVG(WeiPin) AS 尾矿品位, AVG(W_MFe) AS 尾矿磁性铁,
AVG(SiCiJingPinWei) AS 四磁精品位, AVG(SiCiJingLiDu) AS 四磁精粒度, AVG(LiDu) AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '夜班')
UNION
SELECT BanBie AS 班别, CiShu AS 次数, YuanPin AS 原矿品位, Y_MFe AS 原矿磁性铁,
JingPin AS 精矿品位, ShuiFen AS 水分, WeiPin AS 尾矿品位, W_MFe AS 尾矿磁性铁,
SiCiJingPinWei AS 四磁精品位, SiCiJingLiDu AS 四磁精粒度, LiDu AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '白班')
UNION
SELECT '平均 '+BanBie AS 班别, avg(CiShu) AS 次数, avg(YuanPin) AS 原矿品位, avg(Y_MFe) AS 原矿磁性铁,
avg(JingPin) AS 精矿品位, avg(ShuiFen) AS 水分, AVG(WeiPin) AS 尾矿品位, AVG(W_MFe) AS 尾矿磁性铁,
AVG(SiCiJingPinWei) AS 四磁精品位, AVG(SiCiJingLiDu) AS 四磁精粒度, AVG(LiDu) AS 破碎粒度
FROM ZJ_RealData
WHERE (V_Time = '2010-3-11') AND (BaseInfoID = '3') AND (BanBie = '白班')
这样的?
不太明白你的平均是什么意思?
,CiShu AS 次数
,YuanPin AS 原矿品位
,Y_MFe AS 原矿磁性铁
,JingPin AS 精矿品位
,ShuiFen AS 水分
,WeiPin AS 尾矿品位
,W_MFe AS 尾矿磁性铁
,SiCiJingPinWei AS 四磁精品位
,SiCiJingLiDu AS 四磁精粒度
,LiDu AS 破碎粒度
FROM (
SELECT BanBie AS 班别
,CiShu AS 次数
,YuanPin AS 原矿品位
,Y_MFe AS 原矿磁性铁
,JingPin AS 精矿品位
,ShuiFen AS 水分
,WeiPin AS 尾矿品位
,W_MFe AS 尾矿磁性铁
,SiCiJingPinWei AS 四磁精品位
,SiCiJingLiDu AS 四磁精粒度
,LiDu AS 破碎粒度
,FLAG = 'D'
FROM ZJ_RealData
WHERE (V_Time='2010-3-11')
AND (BaseInfoID='3')
UNION
SELECT BanBie AS 班别
,AVG(CiShu) AS 次数
,AVG(YuanPin) AS 原矿品位
,AVG(Y_MFe) AS 原矿磁性铁
,AVG(JingPin) AS 精矿品位
,AVG(ShuiFen) AS 水分
,AVG(WeiPin) AS 尾矿品位
,AVG(W_MFe) AS 尾矿磁性铁
,AVG(SiCiJingPinWei) AS 四磁精品位
,AVG(SiCiJingLiDu) AS 四磁精粒度
,AVG(LiDu) AS 破碎粒度
,FLAG = 'S'
FROM ZJ_RealData
WHERE (V_Time='2010-3-11')
AND (BaseInfoID='3')
GROUP BY
BanBie
)
ORDER BY
BanBie DESC, FLAG
* Code formatted by SoftTree SQL Assistant ?v4.8.29
* Time: 2010-03-26 18:44:47
************************************************************/SELECT CASE FLAG
WHEN 'S' THEN '平均'
ELSE ''
END+BanBie AS 班别
,CiShu AS 次数
,YuanPin AS 原矿品位
,Y_MFe AS 原矿磁性铁
,JingPin AS 精矿品位
,ShuiFen AS 水分
,WeiPin AS 尾矿品位
,W_MFe AS 尾矿磁性铁
,SiCiJingPinWei AS 四磁精品位
,SiCiJingLiDu AS 四磁精粒度
,LiDu AS 破碎粒度
FROM (
SELECT BanBie AS 班别
,CiShu AS 次数
,YuanPin AS 原矿品位
,Y_MFe AS 原矿磁性铁
,JingPin AS 精矿品位
,ShuiFen AS 水分
,WeiPin AS 尾矿品位
,W_MFe AS 尾矿磁性铁
,SiCiJingPinWei AS 四磁精品位
,SiCiJingLiDu AS 四磁精粒度
,LiDu AS 破碎粒度
,FLAG = 'D'
FROM ZJ_RealData
WHERE (V_Time='2010-3-11')
AND (BaseInfoID='3')
UNION
SELECT BanBie AS 班别
,AVG(CiShu) AS 次数
,AVG(YuanPin) AS 原矿品位
,AVG(Y_MFe) AS 原矿磁性铁
,AVG(JingPin) AS 精矿品位
,AVG(ShuiFen) AS 水分
,AVG(WeiPin) AS 尾矿品位
,AVG(W_MFe) AS 尾矿磁性铁
,AVG(SiCiJingPinWei) AS 四磁精品位
,AVG(SiCiJingLiDu) AS 四磁精粒度
,AVG(LiDu) AS 破碎粒度
,FLAG = 'S'
FROM ZJ_RealData
WHERE (V_Time='2010-3-11')
AND (BaseInfoID='3')
GROUP BY
BanBie
)
ORDER BY
BanBie DESC
,FLAG