一上午就写了一条SQL语句

select rewarduser.Reward_UserID,
(
case rewarduser.Reward_SectionID when 0 then ‘暂无分配部门’ else
(
select section.Reward_SectionName
from reward_section as section
where rewarduser.Reward_SectionID=section.Reward_SectionID
)
end
) as section,
rewarduser.Reward_UserName ,
(
select sum(
round(
divideuser5.Reward_DivideSumdivideuser5.Reward_DivideUserScaleOne/divideuser5.Reward_DivideUserScaleTwo
(
select bonusratioitem.Reward_BonusRatioScaleItemOne/bonusratioitem.Reward_BonusRatioScaleItemTwo
from reward_item as rewarditem,reward_bonusratioitem as bonusratioitem
where rewarditem.Reward_ItemID=23
and rewarditem.Reward_ItemID=bonusratioitem.Reward_ItemID
and rewarditem.Reward_StageID=bonusratioitem.Reward_StageItemID
and bonusratioitem.Reward_WorkItemID=
(
select distinct(rewardworkone.Reward_WorkID)
from reward_divideuser as divideusertwo,reward_work as rewardworkone
where divideuser.Reward_DivideUserID=divideusertwo.Reward_DivideUserClosingID
and divideusertwo.Reward_MessageID=rewardworkone.Reward_WorkID
)
)
)
)
from reward_divideuser as divideuser5
where divideuser5.Reward_UserID=divideuser.Reward_UserID
and divideuser5.Reward_ItemID=23
and divideuser5.Reward_DivideUserstyle=1
)as one,
(
select
group_concat(concat(
(
select distinct(rewardworkone.Reward_WorkName)
from reward_divideuser as divideusertwo,reward_work as rewardworkone
where divideuserone.Reward_DivideUserID=divideusertwo.Reward_DivideUserClosingID
and divideusertwo.Reward_MessageID=rewardworkone.Reward_WorkID
)
,":",
round(
divideuserone.Reward_DivideSumdivideuserone.Reward_DivideUserScaleOne/divideuserone.Reward_DivideUserScaleTwo
(
select bonusratioitem.Reward_BonusRatioScaleItemOne/bonusratioitem.Reward_BonusRatioScaleItemTwo
from reward_item as rewarditem,reward_bonusratioitem as bonusratioitem
where rewarditem.Reward_ItemID=23
and rewarditem.Reward_ItemID=bonusratioitem.Reward_ItemID
and rewarditem.Reward_StageID=bonusratioitem.Reward_StageItemID
and bonusratioitem.Reward_WorkItemID=
(
select distinct(rewardworkone.Reward_WorkID)
from reward_divideuser as divideusertwo,reward_work as rewardworkone
where divideuser.Reward_DivideUserID=divideusertwo.Reward_DivideUserClosingID
and divideusertwo.Reward_MessageID=rewardworkone.Reward_WorkID
)
)
)
) separator ‘,’)
from
reward_divideuser as divideuserone
where divideuserone.Reward_ItemID=23
and divideuserone.Reward_UserID=divideuser.Reward_UserID
and divideuserone.Reward_DivideUserstyle=1
) as onedeta,
sum(
round(
divideuser.Reward_DivideUserScaleOne/divideuser.Reward_DivideUserScaleTwo*
(
select (1-(divideuser3.Reward_DivideUserScaleOne/divideuser3.Reward_DivideUserScaleTwo))divideuser3.Reward_DivideSum
from reward_divideuser as divideuser3
where divideuser3.Reward_DivideUserID=divideuser.Reward_DivideUserClosingID
)

(
select bonusratioitem.Reward_BonusRatioScaleItemOne/bonusratioitem.Reward_BonusRatioScaleItemTwo
from reward_item as rewarditem,reward_bonusratioitem as bonusratioitem
where rewarditem.Reward_ItemID=23
and rewarditem.Reward_ItemID=bonusratioitem.Reward_ItemID
and rewarditem.Reward_StageID=bonusratioitem.Reward_StageItemID
and bonusratioitem.Reward_WorkItemID=divideuser.Reward_MessageID
)
,0
)) as two,
(
select
group_concat(concat(
(
select rewardworkone.Reward_WorkName
from reward_work as rewardworkone
where divideuserone.Reward_MessageID=rewardworkone.Reward_WorkID
)
,":",
(
select round(divideuserone.Reward_DivideUserScaleOne/divideuserone.Reward_DivideUserScaleTwo*(
(1-divideusertwo.Reward_DivideUserScaleOne/divideusertwo.Reward_DivideUserScaleTwo)*divideusertwo.Reward_DivideSum
)
*
(
select bonusratioitem.Reward_BonusRatioScaleItemOne/bonusratioitem.Reward_BonusRatioScaleItemTwo
from reward_item as rewarditem,reward_bonusratioitem as bonusratioitem
where rewarditem.Reward_ItemID=23
and rewarditem.Reward_ItemID=bonusratioitem.Reward_ItemID
and rewarditem.Reward_StageID=bonusratioitem.Reward_StageItemID
and bonusratioitem.Reward_WorkItemID=
(
select rewardworkone.Reward_WorkID
from reward_work as rewardworkone
where divideuserone.Reward_MessageID=rewardworkone.Reward_WorkID
)
)
,0)
from reward_divideuser as divideusertwo
where divideuserone.Reward_DivideUserClosingID=divideusertwo.Reward_DivideUserID
)
) separator ‘,’) as onedeta
from
reward_divideuser as divideuserone
where divideuserone.Reward_ItemID=23
and divideuserone.Reward_UserID=divideuser.Reward_UserID
and divideuserone.Reward_DivideUserstyle=2
)as twodeta,
(
select itemsatisfaction.Reward_ItemSatisfactionDateOne from reward_itemsatisfaction as itemsatisfaction
where itemsatisfaction.Reward_UserID=rewarduser.Reward_UserID
and itemsatisfaction.Reward_ItemID=23 limit 1
) as itemsatisfaction
from reward_divideuser as divideuser,
reward_user as rewarduser
where divideuser.Reward_ItemID=23
and divideuser.Reward_UserID=rewarduser.Reward_UserID
group by divideuser.Reward_UserID

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页