Mysql（常用函数）

最新推荐文章于 2024-09-13 08:27:38 发布

十里八方

最新推荐文章于 2024-09-13 08:27:38 发布

阅读量1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_40384470/article/details/95880643

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SELECT
	a.id,
	a.tenant_code,
	a.machine_code,
	a.shift_class,
	a.job_sheet_no,
	a.stop_cause,
	a.start_time,
	ifnull( a.end_time, now( ) ) AS end_time,
	a.location,
	a.curr_date,
	a.start_time AS start_time_tostr,
	ifnull( a.end_time, now( ) ) AS end_time_tostr,
	UNIX_TIMESTAMP( ifnull( a.end_time, now( ) ) ) - UNIX_TIMESTAMP( a.start_time ) AS time_dif
	b.NAME AS user_name 
FROM
	tfKnit.m_stop_record a
	LEFT JOIN bladex.blade_user b ON a.emp_code = b.emp_code 
	AND a.tenant_code = b.tenant_code 
WHERE
	(
		(
			a.start_time < '2019-10-23 06:00:00' AND a.end_time > '2019-10-24 06:00:00' 
			OR (
				a.start_time >= '2019-10-23 06:00:00' 
				AND a.end_time <= '2019-10-24 06:00:00' )
				OR (
					a.start_time < '2019-10-23 06:00:00' AND a.end_time > '2019-10-24 06:00:00' ))
					AND a.tenant_code = 'JB0079' 
					AND machine_code IN ( '001','002', '003', '004', '005', '006', '007', '008', '009', '010', '010') )
			ORDER BY a.start_time

修改数据库表引擎
ALTER TABLE pe_permission ENGINE=INNODB

CRT测试连接类->https请求

        <!-- https://mvnrepository.com/artifact/commons-httpclient/commons-httpclient -->
        <dependency>
            <groupId>commons-httpclient</groupId>
            <artifactId>commons-httpclient</artifactId>
            <version>3.1</version>
        </dependency>

    public static void main(String[] args) throws Exception {

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("ext_type", "get_order_status");
        map.put("ext_order_list", "['1908304643','1908304642','1908304641']");
        map.put("ext_plan_machine", "123abc");
        String json = JSON.toJSONString(map);

        System.out.println("Body = " + json);


        HttpClient client = new HttpClient();
       // CloseableHttpClient client=createSSLClientDefult();
        client.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET, "UTF-8");

        String url = "https://2q10838f56.51mypc.cn:33029/extend.api.php";
        PostMethod method = new PostMethod(url);
        String restult = "";
        JSONObject jsonObject = new JSONObject();
        try {
            StringRequestEntity entity = new StringRequestEntity(json, "application/json", "UTF-8");
            method.setRequestEntity(entity);
            int resp = client.executeMethod(method);
            System.out.println("Resp = " + resp);

            InputStream inputStream = method.getResponseBodyAsStream();
            restult = inputStream.toString();
            //restult=String.valueOf(inputStream.read());
            jsonObject = JSONObject.parseObject(restult);
            String result = jsonObject.toJSONString();
            System.out.println("Result = " + result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            method.releaseConnection();
        }
    }

Administrator


kfWXKQjQfu9kTeM

https://192.168.0.7:8080/api/tianfu-interface/mes/getDeviceStatisticsInfo

/**
 * mongodb返回实体
 */
@Data
@Document(collection = "TF_000000")
public class TFlotEnergy {
	@Id
	@Field("_id")
	private String id;
	@Field("_time")
	private String time;
	@Field("machine_code")
	private String machineCode;
	@Field("machine_name")
	private String machineName;
	@Field("scheme_code")
	private String schemeCode;
	@Field("scheme_name")
	private String schemeName;
	@Field("Cum_flow")
	//private List<MongoParamDto> mongoParams;
	private CumFlow CumFlow;
	@Field("Ins_flow")
	private Insflow InsFlow;
	@Field("Topic")
	private String topic;
}

/**
 * mongodb参数累计/瞬时流量实体
 */
@Data
@Document(collection = "TF_000000")
public class CumFlow {
	private String param_name;
	private String param_value;
	private String param_type;
	private String value_type;
}
/**
*
/
@Data
@Document(collection = "TF_000000")
public class Insflow {
	private String param_name;
	private String param_value;
	private String param_type;
	private String value_type;
}

GroupOperation groupScheme = Aggregation.group("$schemeName").max("$time").as("time");
		//list.add(groupScheme);
		//list.add(projectionOperation);
		//生成执行条件
		TypedAggregation<TFlotEnergy> typedAggregation = Aggregation.newAggregation(TFlotEnergy.class, groupScheme);
		//执行条件 返回数据
		AggregationResults<TFlotEnergy> aggregate = mongoTemplate.aggregate(typedAggregation,"TF_000000", TFlotEnergy.class);
		//AggregationResults<TFlotEnergy> aggregate = mongoTemplate.getCollection("TF_000000").aggregate();
		List<TFlotEnergy> mappedResults = aggregate.getMappedResults();
		//mongoTemplate.getCollection("TF_000000").aggregate()
		System.out.println(mappedResults.toString());

SELECT
	#CONCAT_WS( "_", gs.scheme_name, mp.param_name ) AS meterId,
	gs.scheme_name,mp.param_name,
	gs.group_no 
FROM
	g_gateway g
	INNER JOIN g_server s ON g.server_code = s.server_code 
	AND g.tenant_code = s.tenant_code
	INNER JOIN g_gateway_scheme gs ON g.gate_no = gs.gate_no 
	AND g.tenant_code = gs.tenant_code
	INNER JOIN g_model_point mp ON gs.model_code = mp.model_code 
	AND g.tenant_code = mp.tenant_code 
WHERE
	s.app = '90'  AND g.tenant_code ='000000'
	AND mp.is_deleted = '0' 
	AND gs.is_deleted = '0' 
	AND s.is_deleted = '0' 
	AND g.is_deleted = '0'

SELECT
	meterid,meter_name,machine_code,energy_kind,emp_code,begin_readqty,begin_readtime,end_readqty,endread_time,real_qty,group_no,moneys 
FROM
	tfenergy.e_shiftenergy AS ty 
WHERE
	ty.create_time BETWEEN '2019-07-28 11:02:41' 
	AND '2019-07-31 11:02:41' 
	AND energy_kind = '127'
	AND shift_class = '甲班'
	AND shift_time = '20-08'
	AND group_no = '一组'

SELECT
	`id`,
	`curr_date`,
	`meterid`,
	`meter_name`,
	`energy_kind`,
	`endread_time`,
	`real_qty`,
	`price`,
	`create_user`,
	`create_time`,
	`update_user`,
	`update_time`,
	`remark`,
	`is_deleted`,
	`begin_readqty`,
	`begin_readtime`,
	`end_readqty`,
	`group_no` 
FROM
	e_dayenergy AS ty 
WHERE
	ty.curr_date BETWEEN '2019-07-28 13:28:46' 
	AND '2019-07-31 13:29:46' 
	AND group_no = '一班' 
	AND energy_kind = '127'

#查看员工所负责机台and
 ;SELECT DISTINCT 
			tp.emp_code,
			GROUP_CONCAT( tp.machine_code_s SEPARATOR ',' ) AS 'machcodes' 
			FROM
			tfsystem.m_shift_time AS te,
			tfknit.m_shift_emp AS tp 
			WHERE
			CURRENT_DATE BETWEEN te.begin_time 
			AND te.end_time 
			AND te.is_deleted = 0 
			AND te.tenant_code = 'JB0079'
			AND te.tenant_code = tp.tenant_code 
			AND te.dept_code = tp.dept_code 
			AND te.curr_date = tp.curr_date 
			AND tp.is_deleted = 0
			AND te.shift_class = tp.shift_class GROUP BY
			tp.emp_code,
			tp.shift_class

 select machine_code from tfsystem.p_machine where
        tenant_code='JB0079'
	
            and dept_code='D0002'

            and  machine_code not  in ('001','002','003','004','005','006','007','008','009','010')
"SELECT DISTINCT\n" +
			"\ttt.machine_code,\n" +
			"\tROUND(tt.roll_length,2) as 'roll_length',\n" +
			"\ttt.faric_code,\n" +
			"\ttt.faric_name,\n" +
			"\ttt.sheet_no,\n" +
			"\ttt.spec,\n" +
			"\ttt.speed,\n" +
			"GROUP_CONCAT(ifnull(ts.real_value,0) SEPARATOR ',') as 'realValues',\n" +
			"GROUP_CONCAT(ts.param_type SEPARATOR ',') as 'paramTypes'\n" +
			"\n" +
			"FROM\n" +
			"\ttfknit.job_knit AS tt,tfknit.job_knit_params as ts\n" +
			"WHERE\n" +
			"\ttt.tenant_code = '" + bladeUser.getTenantCode() + "' \n" +
			//"\t tt.tenant_code = 'JB0079'\n" +
			//"\t#AND tt.work_shop_code = 'D0002' \n" +
			"\tAND tt.real_start_date IS NOT NULL \n" +
			"\tAND tt.real_end_date IS NULL\n" +
			"\tAND tt.is_deleted = 0\n" +
			"\tAND tt.sheet_no =ts.sheet_no AND tt.tenant_code =ts.tenant_code \n" +
			"\tAND ts.param_type in \n" +
			"\t('length','stop','density','speed','rtime') GROUP BY tt.machine_code,tt.sheet_no  ";

查询当天数据

select    now() ->yyyy-MM-dd hh:mm:ss   当下年月日时分秒
select    CURRENT_DATE                  当下年月日

如果查询出数据字段为null的话,我们为它赋值一个数字,这样的话在程序中也不用再去做判断了

	SELECT IFNULL('字段',0)   
    如果查询出该字段在数据库中为null,则我们为它赋值为0

纵转横

注释：例如,我在查询时对某个(班级)字段进行分组了,那么这个班级是不是有多个学生？那么我怎么将这几个学生统统归在一行呢？

例如我要展示的是这样的

班级(class) 学生(students)

1年级李四,张三,李广

2年级李明,思思

SQL写法如下：

//GROUP_CONCAT -函数作用就是将学生字段纵转横  以逗号往后叠加
//如果是计算总成绩 就是  sum（student）~~~~
select class,GROUP_CONCAT(ts.student SEPARATOR ',') as 'students' from table_name group by class

mysql字符串转double

convert(字段,decimal(10,2))

SELECT DISTINCT
        jt.tenant_code,jt.
        sheet_no,js.real_value,js.param_type,js.param_name,jt.machine_code,jt.id,jl.yarn_code,jl.yarn_name,jl.wrap_count,jl.composition,jt.real_start_date
        FROM
        job_knit AS jt,job_knit_params as js,job_knit_detail as jl WHERE
        jt.tenant_code = js.tenant_code AND js.tenant_code=jl.tenant_code AND jt.sheet_no=js.sheet_no AND
        js.sheet_no=jl.sheet_no and jt.real_start_date is not null
            and jt.tenant_code='JB0079'
           # and  convert(js.real_value,decimal(10,2))  <![CDATA[ < ]]> convert(#{realValue},decimal(10,2))
       and  jt.is_deleted=0 and js.is_deleted=0 
        and js.param_type ='gbrtime' and js.real_value  <>  '-1'

SELECT DISTINCT
	jt.machine_code,
	jt.tenant_code,
	js.param_name,
	js.param_type,
	js.sheet_no,
	js.real_value
FROM
	(SELECT machine_code,tenant_code,MAX(real_start_date) as 'startTime' FROM job_knit WHERE is_deleted=0 AND real_start_date is not NULL AND tenant_code ='JB0079' GROUP BY tenant_code,machine_code) as t INNER JOIN
	job_knit AS jt
ON
	 jt.is_deleted = 0 
	AND jt.real_start_date =t.startTime
	AND jt.machine_code=t.machine_code
	AND jt.tenant_code=t.tenant_code AND jt.tenant_code = 'JB0079'  
	LEFT JOIN job_knit_params AS js ON   js.tenant_code=jt.tenant_code AND jt.sheet_no=js.sheet_no AND js.is_deleted=0 AND js.param_type='gbrtime' 
	GROUP BY jt.machine_code,js.param_name
	#WHERE convert(js.real_value,decimal(10,2))   <   decimal(10,2))CONVERT(101.2,DECIMAL(10,2))#convert(#{realValue},decimal(10,2))CONVERT(101.2,DECIMAL(10,2))
	#经编卡号 查询所属束节组织
	SELECT gb_no,sheet_no,yarn_code,yarn_name,wrap_count,composition  FROM job_knit_detail WHERE tenant_code ='JB0079' AND sheet_no in ('201907180004','201907190001','201907180005')

#查看已经分配的机台
 ;SELECT DISTINCT 
tp.emp_code,
GROUP_CONCAT( tp.machine_code_s SEPARATOR ',' ) AS 'machcodes' ,tp.shift_class
FROM
tfsystem.m_shift_time AS te,
tfknit.m_shift_emp AS tp 
WHERE
CURRENT_DATE BETWEEN te.begin_time 
AND te.end_time 
AND te.is_deleted = 0 
AND te.tenant_code = 'JB0079'
AND te.tenant_code = tp.tenant_code 
AND te.dept_code = tp.dept_code 
AND te.curr_date = tp.curr_date 
AND tp.is_deleted = 0
AND te.shift_class = tp.shift_class GROUP BY
tp.emp_code,
tp.shift_class
 #001-010都是所负责的机台   001,004,007运行
 #部门机台 011停机
 select machine_code from tfsystem.p_machine where
        tenant_code='JB0079'

            and dept_code='D0002'
 
            and  machine_code not  in ('001','002','003','004','005','006','007','008','009','010','001','002') #已经派出去的机台
SELECT machine_code FROM tfknit.job_knit as tt WHERE tt.tenant_code ='JB0079' AND tt.real_start_date is not null and tt.real_end_date is null and tt.is_deleted = 0 

SELECT DISTINCT
tt.machine_code,
ROUND(tt.roll_length,2) as 'roll_length',
tt.faric_code,
tt.faric_name,
tt.sheet_no,
tt.spec,
tt.speed,
GROUP_CONCAT(ifnull(ts.real_value,0) SEPARATOR ',') as 'realValues',
GROUP_CONCAT(ts.param_type SEPARATOR ',') as 'paramTypes'
FROM
tfknit.job_knit AS tt,tfknit.job_knit_params as ts
WHERE
tt.tenant_code = 'JB0079'
#AND tt.work_shop_code = 'D0002' \n" +
AND tt.real_start_date IS NOT NULL 
AND tt.real_end_date IS NULL
AND tt.is_deleted = 0 AND ts.is_deleted = 0
AND tt.sheet_no =ts.sheet_no AND tt.tenant_code =ts.tenant_code 
AND ts.param_type in 
('length','density','speed','rtime') GROUP BY tt.machine_code,tt.sheet_no;
#WHERE md.machine_code in ('001','003','004','007') AND md.job_sheet_no in ('201907180005','201907130009','201907180004','201907190001')

SELECT
mdon.machine_code,mdon.stop_cause 
FROM
( SELECT md.tenant_code,md.machine_code, MAX( end_time ) as 'endTime', md.stop_cause FROM m_stop_record md  WHERE md.tenant_code= 'JB0079' GROUP BY md.machine_code ) md
INNER JOIN m_stop_record mdon ON mdon.machine_code = md.machine_code AND md.endTime=mdon.end_time  and mdon.tenant_code=md.tenant_code

SELECT DISTINCT
        jt.machine_code,
        jt.tenant_code,
        js.param_name,
        js.param_type,
        js.sheet_no,
        js.real_value
        FROM
        (SELECT machine_code,tenant_code,MAX(real_start_date) as 'startTime' FROM job_knit WHERE is_deleted=0 AND real_start_date is not NULL AND tenant_code ='JB0079'
        GROUP BY tenant_code,machine_code) as t INNER JOIN
        job_knit AS jt
        ON
        jt.is_deleted = 0
        AND jt.real_start_date =t.startTime
        AND jt.machine_code=t.machine_code
        AND jt.tenant_code=t.tenant_code
            and jt.tenant_code='JB0079'
        LEFT JOIN job_knit_params AS js ON   js.tenant_code=jt.tenant_code AND jt.sheet_no=js.sheet_no AND js.is_deleted=0 AND js.param_type='gbrtime'
        and js.real_value  <>  -1
        GROUP BY jt.machine_code,js.param_name order by convert(js.real_value,decimal(10,2))  ;
        
        
        SELECT gb_no,sheet_no,yarn_code,yarn_name,wrap_count,composition  FROM job_knit_detail
            	WHERE 1=1
 
           and  tenant_code='JB0079'

 ;SELECT DISTINCT
tt.machine_code,
GROUP_CONCAT(ifnull(ts.real_value,0) SEPARATOR ',') as 'realValues'
FROM
tfknit.job_knit AS tt,tfknit.job_knit_params as ts
WHERE
tt.tenant_code = 'JB0079'
#AND tt.work_shop_code = 'D0002' \n" +
AND tt.real_start_date IS NOT NULL 
AND tt.real_end_date IS NULL
AND tt.is_deleted = 0 AND ts.is_deleted = 0
AND tt.sheet_no =ts.sheet_no AND tt.tenant_code =ts.tenant_code 
AND ts.param_type in 
('length') GROUP BY tt.machine_code,tt.sheet_no;