SELECT SQL_BUFFER_RESULT `b`.`man_name`, IF(`c`.`star_time` is null , 0, if(POSITION(concat(`b`.`man_name`, ',') IN `c`.`man`) > 0,`c`.`star_time`, `c`.`star_time`)) AS `ppp`, (CASE count(`c`.match_info_id) WHEN 0 THEN 5400 WHEN 1 THEN IF(POSITION(concat(`b`.`man_name`, ',') IN `c`.`man`) > 0, sum(if( (CAST(c.star_time as SIGNED)-1200)>5100, CAST(c.star_time as SIGNED) - 1200-400, CAST(c.star_time as SIGNED) - (if(CAST(c.star_time as SIGNED) < 3100,300,1200)))) , sum(if(CAST(c.star_time as SIGNED) < 3100, if( (CAST(c.star_time as SIGNED)-900-300)<5100, 3100-CAST(c.star_time as SIGNED)+(7000-4000)-((7000-4000)+(3100-300)-5400), 3100-CAST(c.star_time as SIGNED)+(7000-4000)), if( (CAST(c.star_time as SIGNED)-900-300)<5100, (7000-CAST(c.star_time as SIGNED))-((7000-4000)+(3100-300)-5400), (7000-CAST(c.star_time as SIGNED))))) ) WHEN 2 THEN if( max(CAST(c.star_time as SIGNED)) > 4000 and min(CAST(c.star_time as SIGNED)) < 3100, if( max(CAST(c.star_time as SIGNED))-1200>5100, max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-1300, max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-900 ), if ( min(CAST(c.star_time as SIGNED)) > 4000, if ( max(CAST(c.star_time as SIGNED))-1200>5100, if(max(CAST(c.star_time as SIGNED))-1200>5100 and min(CAST(c.star_time as SIGNED))-1200>5100, max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED)), max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-400), max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED)) ), max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED)) )) END) as swa FROM `table_construe_content` `c` right outer JOIN `table_match_info_member` `b` ON (`c`.`match_info_id` =`b`.`match_info_id` and POSITION(b.man_name in c.man)>0 and c.rule='18') left join `table_match_info` `d` on (`b`.`match_info_id` =`d`.`id`) Where b.man_name<>'' and b.team_name='北京现代汽车队' and d.type like '%,45%' group by b.man_name,c.match_info_id
1 XXXX
2 XXXX
.....
WHEN...这样用
WHEN THEN 各是什么条件?
类推然后,对ID为2的,也是像上面那样处理依此类推,一直到ID为N的。这些都需要在一个SQL里实现。
LEFT JOIN tB ON tA.ID = tB.ID
GROUP BY tA.ID
其中,man字段是两个人,例如“王朋,李样”,star_time和end_time的值是一样的,分别代表“王朋”的下场时间,“李样”的上场时间。
table_match_info_member表:id,match_info_id(代表某一场比赛),team_name,man_nametable_match_info表: id,type(代表场次,例如2003赛季第一轮)
字段相关联然后再和table_match_info表左链接,与id关联
table_match_info_member表和table_match_info表没什么问题!
table_construe_content表的设计没法满足要求,起始上场阵容无法体现!
其中man字段是两个人,例如“王朋,李样”,star_time和end_time的值是一样的,分别代表“王朋”的下场时间,“李样”的上场时间。
这个字段设计得不好,没法统计!
王 5000 ->这个是第一场的
王 6000 ->这个是第二场的
怎么将相同名字的,比如“王”的时间相加,得到两场的总和:11000?谢谢了
我现在还不明白我想表达的意思!
我现在还不明白你想表达的意思!
`b`.`man_name`,
IF(`c`.`star_time` is null , 0, if(POSITION(concat(`b`.`man_name`, ',') IN `c`.`man`) > 0,`c`.`star_time`, `c`.`star_time`)) AS `ppp`,
(CASE count(`c`.match_info_id)
WHEN 0 THEN 5400 WHEN 1 THEN
IF(POSITION(concat(`b`.`man_name`, ',') IN `c`.`man`) > 0,
sum(if(
(CAST(c.star_time as SIGNED)-1200)>5100,
CAST(c.star_time as SIGNED) - 1200-400,
CAST(c.star_time as SIGNED) - (if(CAST(c.star_time as SIGNED) < 3100,300,1200))))
,
sum(if(CAST(c.star_time as SIGNED) < 3100,
if(
(CAST(c.star_time as SIGNED)-900-300)<5100,
3100-CAST(c.star_time as SIGNED)+(7000-4000)-((7000-4000)+(3100-300)-5400),
3100-CAST(c.star_time as SIGNED)+(7000-4000)),
if(
(CAST(c.star_time as SIGNED)-900-300)<5100,
(7000-CAST(c.star_time as SIGNED))-((7000-4000)+(3100-300)-5400),
(7000-CAST(c.star_time as SIGNED)))))
)
WHEN 2 THEN if(
max(CAST(c.star_time as SIGNED)) > 4000 and min(CAST(c.star_time as SIGNED)) < 3100,
if(
max(CAST(c.star_time as SIGNED))-1200>5100,
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-1300,
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-900
),
if (
min(CAST(c.star_time as SIGNED)) > 4000,
if (
max(CAST(c.star_time as SIGNED))-1200>5100,
if(max(CAST(c.star_time as SIGNED))-1200>5100 and min(CAST(c.star_time as SIGNED))-1200>5100,
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED)),
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))-400),
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))
),
max(CAST(c.star_time as SIGNED))-min(CAST(c.star_time as SIGNED))
))
END) as swa
FROM
`table_construe_content` `c`
right outer JOIN `table_match_info_member` `b` ON (`c`.`match_info_id` =`b`.`match_info_id`
and POSITION(b.man_name in c.man)>0 and c.rule='18') left join `table_match_info` `d` on (`b`.`match_info_id` =`d`.`id`)
Where b.man_name<>'' and b.team_name='北京现代汽车队' and d.type like '%,45%'
group by b.man_name,c.match_info_id
请指教了
王 5000 ->这个是第一场的
王 6000 ->这个是第二场的
怎么将相同名字的,比如“王”的时间相加,得到两场的总和:11000?看程序中,我用SUM不能得出两者相加的值,还是分开的
王 5000 ->这个是第一场的
王 6000 ->这个是第二场的
怎么将相同名字的,比如“王”的时间相加,得到两场的总和:11000?看程序中,我用SUM不能得出两者相加的值,还是分开的笑归笑,能不能告诉在下这个问题怎么解决?
select .... into temp from yourtable where ...
select name,sum(time)as time from temp group by name
drop table temp
你现在能上么?