select m.user_date,m.project_position,m.strength_degree,m.pour_way,t.produced_amount,
MAX(IF(CEILING(m.id MOD 6)=1,m.material_amount,'')) as name1,
MAX(IF(CEILING(m.id MOD 6)=2,m.material_amount,'')) as name2,
MAX(IF(CEILING(m.id MOD 6)=3,m.material_amount,'')) as name3,
MAX(IF(CEILING(m.id MOD 6)=4,m.material_amount,'')) as name4,
MAX(IF(CEILING(m.id MOD 6)=5,m.material_amount,'')) as name5,
MAX(IF(CEILING(m.id MOD 6)=0,m.material_amount,'')) as name6,
count(distinct concat(m.user_date,m.project_position)) //不起作用,唉我没积分了
from manufacture_material_out m,manufacture_task t
where m.manufacturetask_id=t.manufacturetask_id and m.pour_way=t.pour_way group by m.project_position";
单独写的select count(distinct concat(m.user_date,m.project_position)) from manufacture_material_out;
起作用,查出是四条记录合起来写只有三条记录,各位大侠看看哪里错了???
MAX(IF(CEILING(m.id MOD 6)=1,m.material_amount,'')) as name1,
MAX(IF(CEILING(m.id MOD 6)=2,m.material_amount,'')) as name2,
MAX(IF(CEILING(m.id MOD 6)=3,m.material_amount,'')) as name3,
MAX(IF(CEILING(m.id MOD 6)=4,m.material_amount,'')) as name4,
MAX(IF(CEILING(m.id MOD 6)=5,m.material_amount,'')) as name5,
MAX(IF(CEILING(m.id MOD 6)=0,m.material_amount,'')) as name6,
count(distinct concat(m.user_date,m.project_position)) //不起作用,唉我没积分了
from manufacture_material_out m,manufacture_task t
where m.manufacturetask_id=t.manufacturetask_id and m.pour_way=t.pour_way group by m.project_position";
单独写的select count(distinct concat(m.user_date,m.project_position)) from manufacture_material_out;
起作用,查出是四条记录合起来写只有三条记录,各位大侠看看哪里错了???
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
竹园小区 2 35.00 2009-11-22
竹园小区 2 36.00 2009-11-22
珠信花园 1 44.00 2009-11-22
珠信花园 1 38.00 2009-11-22
樱朱小区 2 66.00 2009-11-22
樱朱小区 2 77.00 2009-11-22
珠信花园 1 33.00 2009-11-23
珠信花园 1 35.00 2009-11-23以上就是主要的表结构
能否给出你的期望结果是什么?否则很核对SQL的结果是否满足你的要求。
过滤掉了
过滤掉??这里需要两个表的联合查询,用join连接会不会过滤掉??
MAX(IF(CEILING(m.id MOD 6)=1,m.material_amount,'')) as name1,
MAX(IF(CEILING(m.id MOD 6)=2,m.material_amount,'')) as name2,
MAX(IF(CEILING(m.id MOD 6)=3,m.material_amount,'')) as name3,
MAX(IF(CEILING(m.id MOD 6)=4,m.material_amount,'')) as name4,
MAX(IF(CEILING(m.id MOD 6)=5,m.material_amount,'')) as name5,
MAX(IF(CEILING(m.id MOD 6)=0,m.material_amount,'')) as name6,
count(distinct concat(m.user_date,m.project_position)) //不起作用,唉我没积分了
from manufacture_material_out m,manufacture_task t
where m.manufacturetask_id=t.manufacturetask_id
group by m.project_position,m.user_date
看看结果
因为你是按照4个字段分组,而count(distinct concat(m.user_date,m.project_position))
是两个字段,有可能出现这种情况
MAX(IF(CEILING(m.id MOD 6)=1,m.material_amount,'')) as name1,
MAX(IF(CEILING(m.id MOD 6)=2,m.material_amount,'')) as name2,
MAX(IF(CEILING(m.id MOD 6)=3,m.material_amount,'')) as name3,
MAX(IF(CEILING(m.id MOD 6)=4,m.material_amount,'')) as name4,
MAX(IF(CEILING(m.id MOD 6)=5,m.material_amount,'')) as name5,
MAX(IF(CEILING(m.id MOD 6)=0,m.material_amount,'')) as name6,
count(distinct concat(m.user_date,m.project_position))
from manufacture_material_out m,manufacture_task t
where m.manufacturetask_id=t.manufacturetask_id
group by m.project_position,m.user_date
问题解决,thanks