select * from comm.input_type where input_code<>'00' order by input_code
时间: 2024-04-23 16:27:23 浏览: 33
您好!根据您的要求,我将查询 comm.input_type 表中 input_code 不等于 '00' 的所有行,并按 input_code 进行排序。以下是查询语句:
```
SELECT * FROM comm.input_type WHERE input_code <> '00' ORDER BY input_code;
```
请注意,这只是一个示例查询语句,具体的表名和字段名可能需要根据您的实际数据库结构进行调整。
相关问题
SELECT a.comm_id, c.Title AS house_keeper_name, ( SELECT build.id FROM erp_base.tb_base_masterdata_resource AS resource INNER JOIN erp_base.tb_base_masterdata_resource AS unit ON unit.id = resource.parent_id INNER JOIN erp_base.tb_base_masterdata_resource AS build ON build.id = unit.parent_id WHERE resource.id = a.resource_id ) AS resource_group, b.resource_type FROM tb_charge_receipts_detail a LEFT JOIN erp_base.tb_base_masterdata_resource b ON a.resource_id = b.id LEFT JOIN erp_base.rf_dictionary c ON b.house_keeper = c.id GROUP BY a.comm_id, c.Title, resource_group, b.resource_type 在mysql中报SELECT list is not in GROUP BY clause and contains nonaggregated column 'erp_charge.a.resource_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 如何做sql的修改
根据错误提示,可以尝试修改sql_mode,去掉only_full_group_by,或者添加a.resource_id到GROUP BY子句中,但是这样可能会导致查询结果不准确。为了避免这种情况,可以使用聚合函数来替代a.resource_id,比如使用MAX(a.resource_id)。
修改后的SQL语句如下:
SELECT a.comm_id, c.Title AS house_keeper_name,
(SELECT build.id FROM erp_base.tb_base_masterdata_resource AS resource
INNER JOIN erp_base.tb_base_masterdata_resource AS unit ON unit.id = resource.parent_id
INNER JOIN erp_base.tb_base_masterdata_resource AS build ON build.id = unit.parent_id
WHERE resource.id = a.resource_id) AS resource_group,
b.resource_type
FROM tb_charge_receipts_detail a
LEFT JOIN erp_base.tb_base_masterdata_resource b ON a.resource_id = b.id
LEFT JOIN erp_base.rf_dictionary c ON b.house_keeper = c.id
GROUP BY a.comm_id, c.Title, resource_group, b.resource_type
将代码改写成hive语法 UPDATE rpt_comm_cm_serv_cdma t1INNER JOIN ( SELECT cust_id, MAX(create_date) AS max_create_date FROM rpt_comm_cm_serv_cdma GROUP BY cust_id HAVING COUNT(cust_id) > 1) t2 ON t1.cust_id = t2.cust_id AND t1.create_date < t2.max_create_dateSET t1.status = 'D';
把这段SQL代码改写成HiveQL语法应该如下:
```
UPDATE rpt_comm_cm_serv_cdma t1
SET t1.status = 'D'
WHERE t1.cust_id IN (
SELECT t2.cust_id
FROM (
SELECT cust_id, MAX(create_date) AS max_create_date
FROM rpt_comm_cm_serv_cdma
GROUP BY cust_id
HAVING COUNT(cust_id) > 1
) t2
WHERE t1.cust_id = t2.cust_id AND t1.create_date < t2.max_create_date
);
```
需要注意的是,HiveQL中不支持直接在`UPDATE`语句中使用`JOIN`操作,而是需要使用子查询来实现类似的功能。同时,HiveQL的语法和SQL略有不同,需要注意一些细节。