SELECT t1020.depart_id , t1020.depart_name, t1020.person_id
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))UNIONSELECT t1024.depart_id, t1024.depart_name, t1024.person_id
FROM ((attend_record t1024 INNER JOIN person t1021 ON (t1024.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1024.depart_id = t1022.depart_id))
WHERE ((t1024.person_id = t1021.person_id) AND ((t1024.in_well_time <= E'2012-05-02 17:06:47') AND (t1024.out_well_time >= E'2012-05-02 17:06:47'))) GROUP BY depart_id GROUP BY depart_id
ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to(t1022.depart_name, E'GBK') ASC, depart_id ASC, person_id ASC);ERROR: missing FROM-clause entry for table "t1022"
LINE 14: ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to...
^********** 错误 **********ERROR: missing FROM-clause entry for table "t1022"
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))UNIONSELECT t1024.depart_id, t1024.depart_name, t1024.person_id
FROM ((attend_record t1024 INNER JOIN person t1021 ON (t1024.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1024.depart_id = t1022.depart_id))
WHERE ((t1024.person_id = t1021.person_id) AND ((t1024.in_well_time <= E'2012-05-02 17:06:47') AND (t1024.out_well_time >= E'2012-05-02 17:06:47'))) GROUP BY depart_id GROUP BY depart_id
ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to(t1022.depart_name, E'GBK') ASC, depart_id ASC, person_id ASC);ERROR: missing FROM-clause entry for table "t1022"
LINE 14: ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to...
^********** 错误 **********ERROR: missing FROM-clause entry for table "t1022"
解决方案 »
- 问一个关于mysql 表级锁 页级锁 行级锁 的问题
- 请教怎么优化这个SQL
- 计算20分钟以内登陆过的用户
- 主库Position和从库Read_Master_Log_Pos值不一致
- 商户名称用varchar字段的问题
- 求一个SQL.
- 高分求救:有没有人提供一个多机集群的例子,网上的都是双机的。
- 如何在Windows上运行多个MySql服务器?
- mysql误导入了数据,把原来的数据覆盖掉了,这个有办法还原吗?
- 使用mysql C客户端时遇到这样的问题:Character set 'latin1' is not a compiled character
- 请问大侠们这个表用mysql查询能实现我要的这个结果吗,要是能含泪跪求代码???????
- create_mysql脚本文件是在哪个文件夹下面的
SELECT t1020.depart_id , t1020.depart_name, t1020.person_id
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))
ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to(t1022.depart_name, E'GBK') ASC, depart_id ASC, person_id ASC;其结果输入正确,这是什么原因?
两个group by?
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))UNIONSELECT t1024.depart_id, t1024.depart_name, t1024.person_id
FROM ((attend_record t1024 INNER JOIN person t1021 ON (t1024.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1024.depart_id = t1022.depart_id))
WHERE ((t1024.person_id = t1021.person_id) AND ((t1024.in_well_time <= E'2012-05-02 17:06:47')
AND (t1024.out_well_time >= E'2012-05-02 17:06:47'))) ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to(t1022.depart_name, E'GBK') ASC, depart_id ASC, person_id ASC;这样有错误:
ERROR: missing FROM-clause entry for table "t1022"
LINE 15: ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to...
然而去掉union的
SELECT t1020.depart_id , t1020.depart_name, t1020.person_id
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))
ORDER BY convert_to(t1022.depart_sn, E'GBK') ASC, convert_to(t1022.depart_name, E'GBK') ASC, depart_id ASC, person_id ASC;其结果是输出正确的 我想要知道 用联合仍采用depart_sn排序怎么弄?
SELECT t1020.depart_id , t1020.depart_name, t1020.person_id, convert_to(t1022.depart_sn, E'GBK') ordered_depart_sn, convert_to(t1022.depart_name, E'GBK') ordered_depart_name
FROM ((in_out_well t1020 INNER JOIN person t1021 ON (t1020.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1020.depart_id = t1022.depart_id))
WHERE ((t1020.in_time <= E'2012-05-02 17:06:47') AND ((t1020.out_time IS NULL)
OR (t1020.out_time >= E'2012-05-02 17:06:47')))UNIONSELECT t1024.depart_id, t1024.depart_name, t1024.person_id, convert_to(t1022.depart_sn, E'GBK'), convert_to(t1022.depart_name, E'GBK')
FROM ((attend_record t1024 INNER JOIN person t1021 ON (t1024.person_id = t1021.person_id))
LEFT OUTER JOIN depart t1022 ON (t1024.depart_id = t1022.depart_id))
WHERE ((t1024.person_id = t1021.person_id) AND ((t1024.in_well_time <= E'2012-05-02 17:06:47')
AND (t1024.out_well_time >= E'2012-05-02 17:06:47'))) ORDER BY ordered_depart_sn, ordered_depart_name呵呵 谢谢2楼的